Merge Excel spreadsheets to table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Every month I shall receive Excel spreadsheets in a dedicated folder which I
shall then need to merge into various Access tables. There are a lot of
spreadsheets so it's not practicable to cut and paste the data and there are
new spreadsheets every month e.g. Fuel Sales - Jan '06, Fuel Sales - Feb '06,
Fuel Sales - Mar '06. There are many other categories as well e.g. Shop
Sales, H&S etc.

The file names can be in an agreed format so if Sales - Jan '06 is not
appropriate then it could be changed.

I need some code to search for the latest files and append the data they
contain, in the specified worksheet, to myTable_FuelSales, myTableShopSales
etc. in MS Access. The code needs to contain some error checking to warn if
an expected file for the current month has not been found.

Grateful for any help.
 
Sorry - there's more...

For each month there will be multiple files per category, one per territory,
so there'll be Territory 1 Sales - Jan'06, Territory 2 Sales - Jan '06,
Territory 3 Sales - Jan '06 etc.
 
First, you will need to set up a table that has the file name of all the
spreads you expect to import. It needs to include the extension, but not the
path. You will also need a field to know what the last month the file was
imported is. Also include the name of the table the spreadsheet will be
imported into.
Then, you will need code to import each file and update the table to show
the file was imported.

Private Sub cmdImport_Click()
Dim rst As Recordset
Dim strPath As String
Dim strFile As String
Dim strTableName As String
Set rst = CurrentDb.OpenRecordset("tblSpreadsheets", dbOpenDynaset)

'Use UNC path so it can be run from any station
strPath = "\\SomeServer\XlImportDocs\"

strFile = Dir(strPath & "*.xls")

Do While Len(strFile) <> 0
rst.FindFirst "[FileName] = '" & strFile & "'"
If Not rst.NoMatch Then
strTableName = rst![TableName]
Docmd.TransferSpreadsheet acImport, , strTableName, strPath &
strFile, _
True
rst.Edit
rst![LastImport] = Me.txtReportMonth
rst.Update
End If
Loop

rst.Close
Set rst = Nothing

'Check for missing

If DCount("*", "tblSpreadsheets", _
[LastImort] < " & Me.txtReportMonth) > 0 Then
MsgBox "Not All Files were Imported"
End If
End Sub
 
Back
Top