DoCmd.TransferSpreadsheet acImport function to import directory of files, same format

[cbs81]

Hi everyone,

I have a question regarding my code below to import into an access table named "anuj". I gave it my best shot but cant get it to work. I am a beginner programmer (accountant by profession) and need to automate a task.

I have a directory declared in "mypath" below. This directory contains a number of workbooks that I need to import into excel.

My objective is to read all workbooks in the directory and import each workbook into the table named "anuj". Once its imported, I would like the code to automatically place the workbook into a new subfolder named "processed" is there a way we can do this?

I need to import sheets 1 to 5 in each workbook starting from row 8 in each workbook. I havent figured out the code to do this as well.

I would like this all done automatically with a click of a button in a form in Access.

The code I have so far is as follows.. Please help me.. im stuck.. thankyou


Sub looper()
Dim myfile
Dim mypath

mypath = "H:\Cash reconciliation progressive backup\TEST MIGRATION LAB\" & _
"New Folder\ADOUpdate\ADOUpdate\test.xls"

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, "anuj", mypath & myfile, False
myfile = Dir
Loop Until myfile = ""
End Sub



Edited 22-May-07 by geekgirlau. Reason: insert line breaks

[geekgirlau]

So close!

Sub looper()
Dim myfile
Dim mypath

mypath = "H:\Cash reconciliation progressive backup\TEST MIGRATION LAB\" & _
"New Folder\ADOUpdate\ADOUpdate\test.xls"

myfile = Dir(mypath & "*.xls")

Do Until myfile = ""
'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, acSpreadsheetTypeExcel8, "anuj", _
mypath & myfile, False
myfile = Dir
Loop
End Sub