Import Select Columns

[juneau730]

Good day all,

I am back seeking a little insight. I currently import an entire excel into a tbl within Access. It works great with no issues, but I don't need all that data. So, I am wondering if there is a simple way to import only the columns I do need.

I the case of the spreadsheet in question I only need columns F, G, I, L and M

 

[JonXL]

How are you doing the import now?

Also, why do you want to only import some columns? Is it because of the size of storing extra data?

 

[juneau730]

The behind the "why" is that the reports contain a LOT of data that isn't necessary for what we need, but we can't ensure that other users will delete the unnecessary columns before trying to import.

Right now, we are just using a simple DoCmd string.

 Set objShell = VBA.CreateObject("wscript.shell")
 strGandL = Application.CurrentProject.Path & "\Separated Users\GandL.xlsx"

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM [tbl GandL]"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbl GandL", strGandL, True
    DoCmd.SetWarnings True
    Forms![_Navigation Form].NavigationSubform.Form.Requery

 

[xenou]

One simple way is import the table to a temp (staging) table, then move only the necessary columns:

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM [tbl GandL_TEMP]"
    DoCmd.RunSQL "DELETE * FROM [tbl GandL]"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "[tbl GandL_TEMP]", strGandL, True
    DoCmd.RunSQL "Insert into [tbl Gandl] (ColA, ColB, ColC, ColD) select ColA, ColB, ColC, ColD from [tbl_Gandl_TEMP]"
    DoCmd.SetWarnings True
    Forms![_Navigation Form].NavigationSubform.Form.Requery

I'm sure there are many other solutions as well.