I do a lot of this type of work and have built processes to automate
importing a lot of files:
Always require the user to build uniformly named files.. that's like trying
to herd cats.
I have a vb script that reads a specific folder and builds a text file
containing all files in that folder of a specific type:
========================
Dim objFSO
Dim ofolder
Dim objStream
Set objFSO = CreateObject("scripting.filesystemobject")
'create the output file
Set objStream = objFSO.createtextfile("c:\FileList\tblMyFileList.txt", True)
CheckFolder (objFSO.getfolder("J:\MyShare\MyFolder\")), objStream
'MsgBox "File Search Completed." + vbCr + "Please check c:\FileList\
tblMillFIleList.txt for details."
Sub CheckFolder(objCurrentFolder, objLogFile)
Dim strTemp
Dim strSearch
Dim strOutput
Dim objNewFolder
Dim objFile
Dim objStream
strSearch = ".xls"
For Each objFile In objCurrentFolder.Files
strTemp = Right(objFile.Name, 4)
If UCase(strTemp) = UCase(strSearch) Then
'Got one
strOutput = CStr(objFile.Path)
objLogFile.writeline strOutput
End If
Next
'Recurse through all of the folders
For Each objNewFolder In objCurrentFolder.subFolders
CheckFolder objNewFolder, objLogFile
Next
End Sub
===============================================
Then I import the text file in c:\FileList\tblMyFileList.txt into my database
and add a column for tablename and parse the filename part from the
path/filename string and insert into tablename
A code module contains :
=================================
Set rs = db.OpenRecordset("SELECT * FROM tblMilARFileList ;", dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
MyARFile = rs!MilARFiles
MyTable = rs!tablename
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
mytablename, MyARFile, True
rs.MoveNext
Loop
====================================================================
and finally I consolidate the tables:
Dim db As Database
Dim tbl As TableDef
Dim strNewTablename As String
DoCmd.SetWarnings False
Set db = CurrentDb
For Each tbl In db.TableDefs
If tbl.Name Like "FY" & "*" Then
strNewTablename = tbl.Name
CurrentDb.Execute "Insert into tblMillARConsolidated select *
from " & strNewTablename
DoCmd.DeleteObject acTable, strNewTablename
End If
Next tbl
db.Close
======================================================
Hope this helps,
Jim
Chris said:
Import the first file into a table
Link to all the other spreadsheets
Use an append query to append to newly created table.
Hi,
I have 34 excel files of identical layout that I need to convert into one
[quoted text clipped - 4 lines]
access file.
Any suggestions?
