Importing multiple .xls files from a folder, and referencing cells.

[xpeterx]

I need to import all .xls files from a designated folder, and only certain cells within these .xls files.

Can anyone point me in the right direction??

I have something, but im really not to sure if its anything like:

Dim myfile
Dim mypath
mypath = "n:\importxls\aramiska\"
Do
myfile = Dir(mypath & "*.xls")
'this will import ALL the excel files (one at a time, but automatically) in this folder. Make sure that's what you want.
DoCmd.TransferSpreadsheet acImport, 8, "aramiskaimport2", mypath & myfile
myfile = Dir
Loop Until myfile = ""


ps _ I want to import INTO access from external .xls files.
aramsiakimport2 is my access table name.

 

[The_Doc_Man]

Search this forum for threads with keyword "Import" in title and look for my name. I have given a detailed overview of how to do this within the last month or so. I hate re-inventing wheels when you could do a search.

 

[xpeterx]

Search this forum for threads with keyword "Import" in title and look for my name. I have given a detailed overview of how to do this within the last month or so. I hate re-inventing wheels when you could do a search.

Thanks doc man, I found a post and you said:

If you can stomach VBA code, there is a way to do this.

First, put all workbooks in the same folder. You must have MODIFY access to the folder's contents.

Next, define a button on a form and tell it to Run some SQL, or build a Macro that does a RunCode. (But error handling and other factors are better under a class module on a form. Macros are STUPID. Hint, hint.)

Under the OnClick of your button (second set of hint, hint), change the SQL you run. REMOVE the DoCmd.RunSQL and instead, make it call your import routine. Leave the error handling in place under the button.

OK, now, in either the class module or a general module, build a subroutine for your importation. Here is a rough draft of how you do it. Read up EXCEL help on collections: Worksheets, rows, columns; and read up on cell structure & properties.

In your subroutine,

1. Do a FindFiles for your workbooks. (FindFiles is in Access Help)

2. Create an Excel application. (Creating an Application is in Access Help)

3. Using a For/Each loop on the .FilesFound collection of the FindFiles object, start processing your workbooks.

4. For each workbook, open it within the Excel application. This will be in the Excel Help Files under the Using VBA heading.

5. Using a For/Each loop on the Worksheets collection of the Open Workbook, start processing your worksheets.

6. For each worksheet, make that worksheet current. (It's a SELECT.) You can find the name (ActiveSheet.Name). Other properties of the rows and columns collection allow you to determine the range on that sheet. These collections are in your Excel Help files.

7. You can build a TransferSpreadSheet from the information you have at this point. That DoCmd option is in Access Help.

8. When you finish the last worksheet in the workbook, close the workbook (without saving any changes) but DO NOT CLOSE THE EXCEL APPLICATION.

9. When you finish the last file in the FilesFound collection, close the application.

That's it, you are done. You'll be surprised at just how compact this code is to run.

Optional actions: Using the Name As command (Access Help files), you can move the Excel file to a sub-folder or parallel folder on the same device after you are done with it. The FilesFound collection won't change until another FindFile.Execute action. Just enumerating .FilesFound doesn't do that, so it is (relatively) static.

but it doesnt really help me with the cell selection, Can i specify which worksheet and which cells to use for import??