Error Importing Excel Data

[skizzly]

Hello Experts,

I am using VBA to import an Excel spreadsheet, however i am getting the "Error!" in some fields from the spreadsheet. See attached.

Does anyone know what the cause of this and how to get rid of it

:banghead:

Thanks greatly

 

[JHB]

Do you import into a new table, if not try it?
Is there some calculation in the Excel sheet?

 

[skizzly]

Hi,

thanks for the reply,

I am importing the worksheet as a new table.
looking at the sheet, there is no calculations.....

 

[JHB]

Do you have some sample data you could post, (a few line of data from the Excel sheet, zip it)?
How do you import the data, because you wrote: "I am using VBA to import an E..."?

 

[skizzly]

Thanks for reply,

Please find the offending sheet....
I am using this VBA to import the sheet:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ExcelDynamicRangeData" & "_" & .Sheets(intLocalCounter).Name, strThisPath(0), False, "Sheet1!A1:E" & intLastRow

I cant see the wood for the tree....

Thanks again

 

[JHB]

The Excel file you try to import has some errors in it, you can see it if you use a file dump program, I've made a printscreen of it.
Create a New Excel file with some other data in and it will import okay.
The only thing which is wrong in your code line is that the Excel file has fields name in first row, and the code line says it hasn't.

 

[skizzly]

Hi JHB,

Thanks for the reply, unfortunately the spreadsheet is password protected and am unable to get that password, so the data in the file has been copied and pasted into the sheet i sent as example of what i am experiencing...

Its the source spreadsheet that is the problem.... will have to download the spreadsheet and copy and paste the regions into a new spreadsheet where i can remove any formatting of the columns i need....

Thanks