Import Select Columns

juneau730

Board Regular
Joined
Jun 7, 2018
Messages
72
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
302
Office Version
365, 2016
Platform
Windows
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

Board Regular
Joined
Jun 7, 2018
Messages
72
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.

VBA Code:
 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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,601
Office Version
2013
Platform
Windows
One simple way is import the table to a temp (staging) table, then move only the necessary columns:

VBA Code:
    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.
 

Forum statistics

Threads
1,089,217
Messages
5,406,916
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top