I'm facing a few difficulties with a macro to import spreadsheets into Access database tables.
Each spreadsheet file represents a different month (e.g. for August-October 2012, there will be different files named 201208.xlsx, 201209.xlsx and 201210.xlsx) and each file contains only one tab with the same name as the file (201208, 201209, 201210). They sit in one separate folder.
I want to import the spreadsheets such that each one overwrites and updates one table in the database.
The issue I face is that different permutations will be uploaded at various times. For example, one run of data may seek to update August 2012 and 2013, but no other months. Another run may seek to update every month.
In order to overwrite tables I understand that they must first be deleted. How can I tell Access which tables are to be updated, and hence deleted, and which aren't?
I've taken the below code from another example in this forum and modified it to the extent of my (slight) capability. At the moment it seems to have a count function that loads every worksheet in a file. How can I change this so it loads every file in the relevant folder, and not just one (201507, as in the code)?
Many thanks in advance!
Paul
Private Sub first_import()
Dim WrksheetName As String
Dim i As Integer
Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Visible = True
xl.Workbooks.Open "C:\Users\Toshiba\Documents\tableload\201507.xlsx"
With xl
.Visible = True
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).Name
DoCmd.TransferSpreadsheet (acImport), 10, WrksheetName, "C:\Users\Toshiba\Documents\tableload\201507.xlsx", True
Next i
End With
End With
Set xl = Nothing
End Sub
Each spreadsheet file represents a different month (e.g. for August-October 2012, there will be different files named 201208.xlsx, 201209.xlsx and 201210.xlsx) and each file contains only one tab with the same name as the file (201208, 201209, 201210). They sit in one separate folder.
I want to import the spreadsheets such that each one overwrites and updates one table in the database.
The issue I face is that different permutations will be uploaded at various times. For example, one run of data may seek to update August 2012 and 2013, but no other months. Another run may seek to update every month.
In order to overwrite tables I understand that they must first be deleted. How can I tell Access which tables are to be updated, and hence deleted, and which aren't?
I've taken the below code from another example in this forum and modified it to the extent of my (slight) capability. At the moment it seems to have a count function that loads every worksheet in a file. How can I change this so it loads every file in the relevant folder, and not just one (201507, as in the code)?
Many thanks in advance!
Paul
Private Sub first_import()
Dim WrksheetName As String
Dim i As Integer
Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Visible = True
xl.Workbooks.Open "C:\Users\Toshiba\Documents\tableload\201507.xlsx"
With xl
.Visible = True
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).Name
DoCmd.TransferSpreadsheet (acImport), 10, WrksheetName, "C:\Users\Toshiba\Documents\tableload\201507.xlsx", True
Next i
End With
End With
Set xl = Nothing
End Sub
Last edited:
