TransferSpreadsheet not importing all the rows

[Euler271]

I'm having some trouble using TransferSpreadsheet to import Excel data rows. Here's my line:



DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "T", str, True, "Template!"

I'm using 2010 and the users put their data in an Excel xlm template. The data goes into new table called "T".

I have one of them that I'm looking at now and it has 52 rows of data but only 9 are getting put into the table.

My code opens the File Dialog box for the user to select the template and the program takes it from there.

Can anyone help with this? Thanks very much.

[Peter M]

Is Template! the Excel sheet name or a "Table" in Excel? (I'm not very familiar with formal "Tables" and have frequently had problems referencing their contents.)
I'm guessing the problem is Template! is a defined range (and not the sheet name) and only the first 9 rows are part of that definition. Alternatively, the Print Area might be set and limiting the scope of Template!

I have gotten around this in the past by using Named Ranges with a strictly worded "Do Not Enter Data Below This Row." The named range goes up to and includes that row, which is removed after the data load. It isn't the prettiest solution but the rest of the Excel worksheet had headers and side notes and I couldn't simply reference the entire sheet. (Ideally the data sheet would only have the header row and then you could reference the sheet "Sheet1!".)
When rows are inserted above the final row it automatically expands the Named Range.

p.s. you can go into VBA in excel and in the immediate window enter
? Range("Template").address

and it should display the address of the Table "template" (but not the sheet "template")

[Micron]

Quite possibly your target table has fields with properties that don't allow these records to be added; e.g. not allowing null or empty strings, indexing (being required), numbers vs text, etc.
You could try setting all to text, removing indexes, allowing null or empty strings. Or you could simply link to the sheet?

[Micron]

I have gotten around this in the past by using Named Ranges with a strictly worded "Do Not Enter Data Below This Row."

In your case, what about dynamic named range instead?

[Peter M]

In your case, what about dynamic named range instead?

I had not heard of those! I found this description: https://www.excel-easy.com/examples/...med-range.html
That could work very well - thank you!

[Euler271]

It's trying to import a table in Excel.

I think it's an issue with the datatypes. I've set the template column datatypes the way they should be but during the import process Access changes some of them. In previous Access versions you could create an import specification.

[Micron]

You're welcome. If I recall, it is possible to create a named range at the workbook level AND/OR the sheet level. I never got it to work when using automation from Access when the range was at the book level. Notice that in step 6 of your link, it's being created at the workbook level.

[Micron]

It's trying to import a table in Excel.

I think it's an issue with the datatypes. I've set the template column datatypes the way they should be but during the import process Access changes some of them. In previous Access versions you could create an import specification.

The first n rows of a sheet determine the data type. Seems that number is 8 or 9 and that's why I suggested linking. Some do a work around by linking, then using a query on the linked table to copy to a properly structured table. It's reported that this usually fixes it.

[Euler271]

Yes, I agree. I'm going to try the linking workaround to see how it works for my situation.

I'm not sure the dynamic range solution is right for me but I'll look at that also.

Thanks to all for your time and suggestions.

[Peter M]

If you are having problems with the Excel datatypes, define Table "T" first and then append the data.
fyi - You can save the Import Steps and then view the XML specifications to create your own ImportSpecs, but in my experience the data types may still go sideways when making a New Table. The worst part is when it works 90% of the time. (It's gotten so bad that in the past I've resorted to dumping the excel file as a CSV and importing the text.)

[Micron]

The oft used approach that's like your suggestion is to link to the sheet, then (one time) make a table via make table query, after which you only ever append/update to the target table from the linked. You could always make target table from scratch. Either way, data transposed from linked sheet to target table should be OK as long as it doesn't violate any table field restrictions.

[Euler271]

If you are having problems with the Excel datatypes, define Table "T" first and then append the data.
fyi - You can save the Import Steps and then view the XML specifications to create your own ImportSpecs, but in my experience the data types may still go sideways when making a New Table. The worst part is when it works 90% of the time. (It's gotten so bad that in the past I've resorted to dumping the excel file as a CSV and importing the text.)

That's exactly what I was doing. Creating "T" then importing the worksheet's table into it. I was creating "T" with certain datatypes for each of the fields, obviously, but Access was deciding different datatypes for the Excel table's columns.

I will try linking to the Excel table but so far I've found that Access decides on certain datatypes for the linked table's columns regardless of how I try to format the columns ahead of time. I haven't tried copying the data from the linked Excel table to "T" yet.

[Micron]

Access decides on certain datatypes for the linked table's columns regardless of how I try to format the columns ahead of time.

This makes me think you missed my point. In post 9 I said you'd have a properly structured table to copy the data from the link. You don't try to control the fields in the link or source.