Really weird problem

TomElZ · 23 Mar 2011 at 14:54

Hey guys.

I have a really weird problem and don't have a clue where it is coming from. Basically what I have is a text file that is opened in Visual Basic. Visual basic then performs some formatting and outputs it to Excel. The Excel workbook is then saved. I then go into Access and open the Excel file as a Linked Table - which is a requirement. However, from what I gather this creates some wierd problem in my Time column. I don't have a clue where the 30/12/1899 comes into it. It is absolutely nowhere in my code/excel spreadsheet. The end result is then displaying this data in a DataGrid in Visual Basic and so on so forth.

http://www.360driveflashing.co.uk/problem.jpg

Cheers.

GravyMonster · 23 Mar 2011 at 15:07

Because you are only giving the field a time value Access is adding it's default date to it.

If you only want to display the time, change the format parameter of the date/time field to:

Code:

hh\:nn\:ss

TomElZ · 23 Mar 2011 at 15:10

Spunkey said:
Because you are only giving the field a time value Access is adding it's default date to it.

If you only want to display the time, change the format parameter of the date/time field to:

Code:

hh\:nn\:ss

Ahh yeah I see what you mean.

So would need to change this in Excel as it is a linked table so Access won't let me change it right?

GravyMonster · 23 Mar 2011 at 15:20

Ummmm... not sure off the top of my head. Suck it and see would be the best coure of action here

TomElZ · 23 Mar 2011 at 15:25

Hmmm..weird one this!!

SimonCHere · 23 Mar 2011 at 15:25

Yes, but even if you change the column in Excel to Time or hh:mm:ss (hh:nn:ss I don't think will be recognised) it will still try to show the default date bit in access. If you change the column format in Excel to Text it will import correctly but it will only be a text column in Access.

You can of course just leave it as it is and when you select the data just pull out the bit you need.

Code:

SELECT Format(Sheet1.[Time],'hh:nn:ss') AS time, Sheet1.Date
FROM Sheet1;

TomElZ · 23 Mar 2011 at 15:30

SimonCHere said:
Yes, but even if you change the column in Excel to Time or hh:mm:ss (hh:nn:ss I don't think will be recognised) it will still try to show the default date bit in access. If you change the column format in Excel to Text it will import correctly but it will only be a text column in Access.

You can of course just leave it as it is and when you select the data just pull out the bit you need.

Code:

SELECT Format(Sheet1.[Time],'hh:nn:ss') AS time, Sheet1.Date FROM Sheet1;

Yeah you are right, just changed it in Excel and still same problem.

Ok, well is there anyway that when I'm writing out to the spreadsheet I can set the Data type as Text/General instead of Date/Time or by default?

TomElZ · 23 Mar 2011 at 15:31

In Excel the time column appears as Custom so if I can get it to write to Excel as a General rather than a Custom the problem will be solved?

SimonCHere · 23 Mar 2011 at 15:31

.Net or classic VB?

and no, probably not. Excel will see it as a date/time and set the column as such.

TomElZ · 23 Mar 2011 at 15:33

.Net mate

TomElZ · 23 Mar 2011 at 15:54

When changed to General in Excel, the whole format is messed up. :/

SimonCHere · 23 Mar 2011 at 16:16

You want the numberformat property of the Range you want to change.

i.e.

Code:

Worksheets("Sheet1").Columns("A").NumberFormat = "@"

Will set column A on Sheet1 to text format.

And yes, changing the format on anything Excel thinks is a date tends to produce some strange values.
remember to change the column format before importing the data to it.

TomElZ · 23 Mar 2011 at 17:10

Hmmm.

Just trying to get that to work now. How could you set the whole worksheet to be Text format?

TomElZ · 23 Mar 2011 at 17:19

Can't get that piece of code to work either :/

SimonCHere · 23 Mar 2011 at 19:04

As NumberFormat is a property of the range object, anything that inherits range can use it.
so Worksheets("Sheet1").NumberFormat = "@"
should work.

well that code would only work if your variable matched. That was just generic VBA.
so replace Worksheets("Sheet1") with the variable that references your worksheet.

TomElZ · 24 Mar 2011 at 13:56

oWB = oApp.Workbooks.Add

oWB.Sheets("Sheet1").Columns("A").NumberFormat = "@"

Using that bit of code still doesn't work.

oWB.Sheets("Sheet1").Cells(lRow, 1).Value = sLineArray(0)
oWB.Sheets("Sheet1").Cells(lRow, 2).Value = sLineArray(4)

Outputs to the spreadsheet fine however the format code isn't working.

SimonCHere · 24 Mar 2011 at 15:37

It should do:

Code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim excelApp As ApplicationClass = New ApplicationClass
        Dim excelBook As Workbook
        excelBook = excelApp.Workbooks.Add

        Dim excelSheets As Sheets = excelBook.Sheets
        Dim wSheet As Worksheet = CType(excelSheets(1), Worksheet)
        Dim cell1 As Range = wSheet.Range("A1", Type.Missing)
        Dim cell2 As Range = wSheet.Range("B1", Type.Missing)
        CType(wSheet.Columns("A"), Range).NumberFormat = "@"

        cell1.Value2 = "01:01:01"
        cell2.Value2 = "02/02/2011"


        excelBook.SaveAs("c:\temp\formats.xls")
        excelApp.Quit()
    End Sub

TomElZ · 24 Mar 2011 at 15:42

Yeah eventually got it sorted.

Cheers works perfect now