Problem importing excel to access due to dynamic columns

[jobbie]

I have an excel file which is actually a dump file taken from SAP. I have written a macro in VBA (Access) which imports this excel file in a table. The problem is that sometimes there are columns in SAP which may appear or disappear. This causes a problem in the automation of my macro as Access gives an error when it can't find certain columns or finds additional columns. How do I deal with this problem of the dynamic nature of these excel columns?

[Homegrownandy]

consistency in the excel document is important, If you cant find a way to automate that. You may need to manually paste your data where you want it.

[CJ_London]

if it is erroring when finding additional columns, this implies you are using transferspreadsheet to import directly to a table

instead, import to a temporary table, then have an insert query to copy data to it's destination. This will solve the additional columns issue since they will simple be ignored. For missing columns you need an additional routine to run to check the required columns are there before trying to run the import - and aborting if they are not. What this routine is depends on your situation - could compare manually if executing manually, could try to run the import and have an orderly retreat on fail (use currentdb.execute("myquery",dbfailonerror) could have some sort of a routine to check the column names before running the query (use tabledefs)

[jobbie]

For missing columns you need an additional routine to run to check the required columns are there before trying to run the import - and aborting if they are not. What this routine is depends on your situation - could compare manually if executing manually, could try to run the import and have an orderly retreat on fail (use currentdb.execute("myquery",dbfailonerror) could have some sort of a routine to check the column names before running the query (use tabledefs)

Actually I forgot to mention an important thing. I won't be using all the columns from the excel file in my access macro. There are only a certain number of columns which I use and they will always be present in the SAP dump. The problem arises when columns other than the ones I use appear or disappear. So is there a way to just import only the relevant columns without worrying about the rest? I would like the macro to continue working and not just provide a warning if irrelevant columns are missing or added.

[CJ_London]

per my suggestion

instead, import to a temporary table, then have an insert query to copy data to it's destination.

[jobbie]

per my suggestion

I will try it and let you know

[jobbie]

per my suggestion

ok I got some time to try out your suggestion but the problem is how do I import the excel contents to a temporary table? I have the below code in vba which imports the data at the click of a button:

Sub Macro_Import_Reservation_Report()
On Error GoTo Macro_Import_Err
DoCmd.TransferSpreadsheet acImport, 10, "TheTable", "C:\Excel Report", True, ""
Macro_Import_Exit:
Exit Sub

Macro_Import_Err:
    MsgBox Error$
    Resume Macro_Import_Exit
End Sub

"TheTable" is a predefined table in access. How do I modify the code such that it imports to a new temporary table?

[CJ_London]

take a look at this link

https://msdn.microsoft.com/en-us/lib.../ff834402.aspx

which explains about transferspreadsheet - so you can change acimport to aclink if you would rather, and rename Thetable to tmpTable or impTable or whatever name is meaningful, or just remove it and access will provide a name

[jobbie]

thanks for the link but I fixed the problem by creating a linked table to my excel sheet and then used a query to append the selected columns to the original table

[CJ_London]

well that was basically what I suggested.