Ajax - thank you... still not exactly clear on the syntax that includes Excel file name, e.g., "Test.xlsx". Below is the latest code:
Code:
Private Sub cmdButton_ImportSurvey_Click()
'Import Excel files to a specified Access Table
Const strPath As String = "C:\Users\dellc\Desktop\TestingImport\"
'Filename | File Array | File Number
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim MySql As String
Dim strFile2 As String
'Drop records from table "Tabl1"
MySql = "DELETE Table1.* FROM Table1;"
CurrentDb.Execute MySql
'Loop through the import folder
strFile = Dir(strPath & "*.xlsx")
While strFile <> ""
'Add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'Check whether or not any files were found
If intFile = 0 Then
MsgBox "No files were found!", vbInformation, "Information"
Exit Sub
End If
strFile2 = "INSERT INTO Table1" & _
" SELECT F1 AS Field1, F2 As Field2 FROM [Sheet1$A:B] IN 'C:\Users\dellc\Desktop\TestingImport\Test.xlsx'[Excel 12.0;HDR=No;IMEX=1;ACCDB=Yes] WHERE F1<>'Field1'"
'strFile2 = "INSERT INTO table1" & _
" SELECT F1 AS Field1, F2 As Field2 FROM [Sheet1$A:B] IN '" & strPath & strFileList(intFile) & "'[Excel 12.0;HDR=No;IMEX=1;ACCDB=Yes] WHERE F1<>'Field1'"
CurrentDb.Execute strFile2
'Throw message box upon file input
MsgBox "Records have been successfully imported!", vbInformation, "Information"
End Sub
Please advise how to modify the VBA for importing filename "Test.xlsx". Thank you!
//
Final question... if my actual spreadsheet includes, e.g., 50 columns, is there a way to identify the cell range? Further, do I then have to list every single column and specify the target field?