Open, Filter & Import

[sando]

Hi,

It's been a while since I have tried my hand at VBA and need some help to get me going again.

in Access VBA, I'm trying to open a spreadsheet, filter it (because the file is too large) and then import into a table.

Any help would be appreciated.

Thanks

 

[theDBguy]

Hi. Just curious... When you said the file is too large, how big is it?

 

[sando]

theDBguy

42,166 rows by 352 columns

So I will need to hide a few columns before I import the file

 

[isladogs]

Instead of importing the spreadsheet, link to the Excel file. You can then import the fields you require (up to 255) from the linked table.
However if you want anything like that number of fields then you almost certainly have a poorly designed table structure

 

[Cronk]

I'd do that too (link to Excel file). Problem though if you want data in the 256th column as only the first 255 will show in the linked file in Access.


If so, for a one off import operation, I'd delete unnecessary columns in a copied Excel file. Use automation if the import is to be repetitive.

 

[sxschech]

Another option is to query the data in excel, unfortunately, if the cols you need are not contiguous, you would need separate queries, so in your case, you would need two as each can hold 255 cols. Then a third query could select the actual cols you want. You will need to replace your names for Filename, SheetName and Range. Since it is a query, you can use joins and where clauses to filter the data too.

SELECT T1.* 
FROM [Excel 12.0;HDR=YES;IMEX=1;Database=C:\Downloads\FileName.xlsx].[SheetName$A1:G1000] AS T1

 

[sando]

Thanks everyone for your help.

because the excel workbook was a report on a server from another system, I didn't want to hang it, so I've created a temp excel file that will extract only the columns I need, which brings it down to 145 columns.

Now I just need to link Access DB to the excel file