Import range from Excel to Access with field names

[fredalina]

i have an Excel spreadsheet that is manually eek updated daily from a mainframe app. Weekly, that data is joined manually eek with data from an Access database and sent out one to each supplier per week. The first manual process will have to remain manual for the time being as there's no simple way to get the data out of the mainframe, but the second manual process (joining the data together and sending out the reports once per week) can be automated to at least some degree. This is my task.

I am trying to import data from the manual spreadsheet through a Module. The field names are stored in row 2 (this is one field per working day of the month) of the spreadsheet, and the data is in row 13 for the first supplier down to row 21 for the last supplier. This is the code I'm trying to use for the first country:

DoCmd.TransferSpreadsheet acImport, 8, "tblCountry1", ImportDir, True, "Dec08!c2:x2,c13:x13"

The above code produces no column headings (just F1, F2, etc), and blank data.


I've tried:

DoCmd.TransferSpreadsheet acImport, 8, "tblCountry1", ImportDir, True, "Dec08!c13:x13"

The above code produces the same result as the first.


I've tried:

    DoCmd.TransferSpreadsheet acImport, 8, "tblCountry1", ImportDir, False, "Dec08!c13:x13"

This produces the correct data, but obviously no column headings (just F1, F2, etc).


I've tried:

    DoCmd.TransferSpreadsheet acImport, 8, "tblCountry1", ImportDir, False, "Dec08!c2:x2,c13:x13"

and i get an error message on the Range portion. I've also tried it with Dec08! before both sections of the range, with a semicolon instead of a comma, etc, and this issue happens regardless.

Is this possible to do? Even if I make sure the field titles are in the second column, it doesn't use the correct field titles.

 

[johncallaghan]

I have encountered this problem and the way I get round it is to import the spreadsheet as you have tried to in your first example into a temporary table, and then assuming the fields are in the same order each week import/append he data into another table with the fieldnames in.

this will rename any old table with the dame name so I use an inelegant approach to delete the old table. In this case the temporary table is 'Bankimport'

On Error Resume Next
db.TableDefs.Delete "BankImport"
On Error GoTo 0

DoCmd.TransferSpreadsheet acImport, , "BankImport", "filename", False

I hope that helps

Cheers
John

 

[fredalina]

Thanks.

Unfortunately, the spreadsheet has a tab for each month (i.e. Dec08). The fields are the working days for that month, so at the start of each new month, the field names would change. The data would still be in the same cell, though.

Perhaps there's a way to systematically rename the fields each month?