Hi all,
I'm new to the form and very new (yesterday!) to VBA.
I have a number of spreadsheets, all formatted the same which I need to import into a single table in Access. Not quite sure how I managed it but I have a module that's now part of a query that does the job I want.
However I forgot that I needed to import the filename as the first column of data into the table (identifies a supplier)
I've found several threads but with NO VBA knowledge I'm really struggling to understand how to incorporate it into my code, can anyone help?
My code so far is:
Function Import_Excel()
Dim myfile
Dim mypath
mypath = "C:\Users\timog\Dropbox\Brand Page Details\1.Reports\Detailed Reports\"
myfile = Dir(mypath & "*.xlsx")
Do While myfile <> ""
If myfile Like "zz*.xlsx" Then
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "AMS_Import all Results", mypath & myfile, True
End If
myfile = Dir()
Loop
MsgBox "Load Finished"
End Function
Sub Import()
End Sub
Many thanks in advance.
Tim
I'm new to the form and very new (yesterday!) to VBA.
I have a number of spreadsheets, all formatted the same which I need to import into a single table in Access. Not quite sure how I managed it but I have a module that's now part of a query that does the job I want.
However I forgot that I needed to import the filename as the first column of data into the table (identifies a supplier)
I've found several threads but with NO VBA knowledge I'm really struggling to understand how to incorporate it into my code, can anyone help?
My code so far is:
Function Import_Excel()
Dim myfile
Dim mypath
mypath = "C:\Users\timog\Dropbox\Brand Page Details\1.Reports\Detailed Reports\"
myfile = Dir(mypath & "*.xlsx")
Do While myfile <> ""
If myfile Like "zz*.xlsx" Then
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "AMS_Import all Results", mypath & myfile, True
End If
myfile = Dir()
Loop
MsgBox "Load Finished"
End Function
Sub Import()
End Sub
Many thanks in advance.
Tim