Importing entire sheet from excel via VBA

[harryklein]

Hi,

I use vba code to import the content of a sheet into a table.
The sql looks something like this

INSERT INTO Table1(Field1, myDate) SELECT(Field1, DateField) FROM  [Excel 12.0 Xml;HDR=Yes;Database=C:\Users\MYFILE.xlsb].[DATA$]

The format of mydate-field in Table1 is date. It worked fine till the last times, when the data in the DateField in excel has changed, the has been extended with some character that is not the part of the date. It is like "20210339ABC". The lenght is fixed. The import didn't stop, but myDate field in the table remains empty.

I would like to use some formatting-function or serialdate to get the right date in my table, but I couldn't figure out, what would be right way.
Any help would be appreciated.


Thank you

[Welshgasman]

I cannot see how the numeric portion is a date.?
What did it look like before?

20210339 ?

[davegri]

In Excel, try saving the file as xlsx, then changing your sql accordingly.

[harryklein]

It isn't, it is a typo.
The correct value is 20210329, and it was put into excel like this.

[Gicu]

I'd say you should use a temporary (staging) table with the second field set as Short Text instead of date then use a query to move the imported data to the final table using a Left([tempTable1].[MyDateString],6).

Cheers,

[Welshgasman]

It isn't, it is a typo.
The correct value is 20210329, and it was put into excel like this.

Well you could do it using DateSerial()

tt="20210329ABC"
? dateserial(left(tt,4),mid(tt,5,2),mid(tt,7,2))
29/03/2021

but that would expect correct data?, so Vlad's suggestion of a staging table would be better I believe.

What are you meant to do when the date is rubbish?

[harryklein]

you mean, when the data in this field cannot be interpreted as a date? it didn't happen so far and no instructions are there for this scenario.
Thanks a lot for your answer.

[harryklein]

Thanks for the answer, Vlad.