My code below works greate when I'm importing a file that has the same date as the date when I run the import. For example, if I run the import on 06/01/18 and the filename is TestDate_060118. xlsx, Access will import and append file to existing table. If the filename is TestDate_053118.xlsx, Access will not import the file and append to table.
All files have the prefix (TestData_), but the date changes.
How can I amend my VBA code below to import my file regardless if the date changes i.e. TestDate_060118, TestDate_053118, TestDate_053018 or any date associate with TestDate_.
_______________________________________________________________________________________
Private Sub btnimportexcel_Click()
Dim PATH As String
Dim Date1 As String
Date1 = Format(Date, "mmddyy")
PATH = "C:\Users\mosco\Desktop\Test Access\ImportNewTable\Testdata_" & Date1 & ".xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Master", PATH, True, "Sheet1!"
MsgBox "Worksheet imported", vbInformation + vbOKOnly, "success"
End Sub
All files have the prefix (TestData_), but the date changes.
How can I amend my VBA code below to import my file regardless if the date changes i.e. TestDate_060118, TestDate_053118, TestDate_053018 or any date associate with TestDate_.
_______________________________________________________________________________________
Private Sub btnimportexcel_Click()
Dim PATH As String
Dim Date1 As String
Date1 = Format(Date, "mmddyy")
PATH = "C:\Users\mosco\Desktop\Test Access\ImportNewTable\Testdata_" & Date1 & ".xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Master", PATH, True, "Sheet1!"
MsgBox "Worksheet imported", vbInformation + vbOKOnly, "success"
End Sub