Trouble with TransferSpreadsheet method

[Sketchin]

I am running this code to import an Excel sheet into a table in my database. This works fine if I set [HasFieldNames] to false. When [HasFieldNames] is set to true, I get the correct field names in the table, but don't get any data from the sheet. Has anyone heard of anything like this?

DoCmd.TransferSpreadsheet acImport, , "txlsRevenuePayback", _
"s:\Programs\Reporting\Payback Revenue Table - Master Copy.xlsx", True, "Actual Revenue By Province!A12000"

 

[Sketchin]

Ok, I lied. The data is in the newly created table....its just that the data in the table starts at row 1954 and ends at row 1999, previous to that, it is all blank. This is partially correct because the spreadsheet does only have 46 records currently in it.

Any reason why the database is importing it this way??

 

[GinaWhipp]

What row does the data start in the spreadsheet? Any hidden rows?

 

[vbaInet]

By the way, I don't think I've come across a column name like this one-->

..."Actual Revenue By Province!A12000"

 

[Sketchin]

The actual data starts at A2. No Hidden rows. Its just a really basic spreadsheet with a list of information. No formulas.

Thought you might like that column name...lol

 

[vbaInet]

I did thanks!

Instead of giving it a range (which may not fit your data sometimes), just give it the name of the sheet:

"Actual Revenue By Province[COLOR="Red"]![/COLOR]"

Note the exclamation still stays.

 

[GinaWhipp]

Then the only time I have ever seen this is when there is something, even a period, somewhere below in the spreadsheet in one of the columns. Any chance of that?

 

[Sketchin]

No text whatsoever in the spreadsheet besides the entries I want.

Removing the range caused it to import nothing but the field names.

 

[GinaWhipp]

Well, that has me stumped... Hmm, what are the field names?

 

[vbaInet]

I'm thinking the field and column names don't match entirely. Even a trailing space in one of the field names in Excel could cause it to fail. Also look out for the underscore character as well.

 

[Sketchin]

Field names are ID, Company, Province, Actual Revenue. No special characters.

 

[vbaInet]

Do it through the wizard and see if it picks up your field names.

 

[Sketchin]

I think the problem is that the data is actually stored in a table in Excel. I didnt realize that was the case because there is no easy way to tell.

Still quite strange that it would import the data this way.

 

[GinaWhipp]

@vbaInet

Great idea! Eagerly awaiting to hear the results...

 

[GinaWhipp]

Ahh, yes that just might be the *problem*. Can you save as a .CSV file and see if you get the same results when importing?

 

[Sketchin]

No Joy with the CSV.

 

[GinaWhipp]

What happened when you tried what vbaInet seuggested?