Looping through file system

  • Thread starter Thread starter Rockn
  • Start date Start date
R

Rockn

I am attempting to loop through a set of Excel files in a specific
directory, extract the data from two cells and write the content of each
cell into a table field. The cell references are the same between all of the
spreadsheets.

I had done something similar where I would loop through a set of records in
a table to see if the same file name exsisted in a directory on a server,
but I can't for the life of me find the database now.

Any help would be greatly appreciated.

Thanks
 
Rockn,
here is a way to proceed and some code (from previous discussion group
answers)

See the code at http://www.mvps.org/access/api/api0001.htm for a way to let
the user actually browse to and select the file.

See Dir function in VBA Help for how to get a list of all .xls files within
a folder (using the wildcard * to the left of the .xls in the filename
argument).

See TransferSpreadsheet method (DoCmd.TransferSpreadsheet) in VBA Help for
information on how to import an EXCEL spreadsheet via this method.

You can do the following with the above items:

(1) Get the folder from user using the
http://www.mvps.org/access/api/api0001.htm code.

(2) Get one of the .xls file's filename in the chosen folder using the Dir
function.

(3) Set up a loop based on stopping the loop when the result from the Dir
function is an empty string.

(4) Do the TransferSpreadsheet action within the loop; then recursively call
the Dir function (without any arguments) to get the next filename in the
folder.

(5) Continue the loop until no more files can be imported.


Dim strDirectory As String, strFilter As String
Dim strFileName As String, strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
strDirectory = Left(strInputFileName, _
InStrRev(strInputFileName, "\"))
strFileName = Dir(strDirectory & "*.xls")

Do While strFileName <> vbNullString
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"SomeTable", _
strDirectory & strFileName, True
strFileName = Dir()
Loop


Jeanette Cunningham -- Melbourne Victoria Australia
 
Back
Top