Import some columns from Excel to Access

[valgore]

Another simple question...
I current have a button that imports from excel to an Access table. i need to change the code to make it so when the button is pressed, the User decides which file to import. i also need to be able to hard code which columns and rows i want to be imported. All the imported files will have the same format. so for example, i get an electronic bill every month into Excel. the download gives me four rows of headers which i dont need. it also gives me a column i dont need. is there a way to say import all column A except the first 5 rows, all of column B except first 5 rows, all column C except first 5 rows, not column D, all Column E and F except first 5 rows?

 

[bubba100]

To select a specific file take a look at thread705-1339257 or search using FindFile.

To import try looking into theTtransferSpreadsheet method.

 

[valgore]

well, that thread looks like it allows you to copy it. i want to be able to select the file name and import that. also i am currently using the TransferSpreadsheet function and it works perfect. but it imports the whole file, not specific ranges. i want it to always transfer columns A,B,C,E,F. they will always be the same format, but there might be more or less data. and like i said above, it has a 5 row header that i dont want. i also dont want it to import the last row, which is a total row.

 

[bubba100]

There are several ways to create the file open popup box. You could use FileSystemObject, an AciveX control (Common Dialog) even API calls. Personally, I learned how to use the common dialog early and have stayed with it. All of these methods are discussed here with examples.


Here is my example of the Transfer Spreadsheet method, it will import a range. Notice the “last row” (intLstRow) in the range is set as a variable.

Docmd.TransferSpreadsheet acImport, 8, "tbYourTableName", "C:\My Documents\" _
& FolderName & "\" & MYFileName, True, "Sheet1!S1:W" & intLstRow

 

[JoeVegas]

bubba100 -- I need to import multiple worksheets within a workbook of each Excel file into an Access 2003 table that will be part of an archive database. TIA