Really weird problem

TomElZ

TomElZ

TomElZ

Associate
Joined
7 Aug 2008
Posts
302
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.
 
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
 
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
Click to expand...

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?
 
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;
 
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;
Click to expand...

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?
 
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?
 
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.
 
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.
 
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.
 
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
 
You must log in or register to reply here.
Back
Top Bottom