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.

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?

TomElZ · 23 Mar 2011 at 15:25

Hmmm..weird one this!!

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?

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. :/

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 :/

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.

TomElZ · 24 Mar 2011 at 15:42

Yeah eventually got it sorted.

Cheers works perfect now