Hello.
I have a bunch of files that I need to import sequentially. The files are generated by an order management system, and the format is like this: store_YYYYMMDDHH. I can't use this date because the number of characters are not static:
store_20209111- this can be the file of 01/09/2020 at 11h or 11/09/2020 at 1h.
So, my idea is to change manually the files with a _number like:
store_date_1
store_date_2
So, I want to import these files sequentially: first the store_date_1 and then the store_date_2 and so on.
My code is like these:
For Each ofile In ofolder.Files
DoCmd.SetWarnings False
If VBA.InStr(1, ofile.Name, "~") = 0 Then
If ofile.Type Like "*" & oRecon_otype & " *" Then
'
Debug.Print ofile.Path
ofilepath = ofile.Path
oExFileName = ofile.Name
MsgBox "File to open : " & ofile.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, oTableName, ofilepath, True
i = i + 1
End If
End If
Next ofile
Exit Function
ExitDoor:
MsgBox Err.Number & " - " & Err.Description
End Function
For each file in the specific folder, import all. But I want a piece of code to force access to import the store_1 first, next the store_2 and so on.
Any ideas?
Thank you.
I have a bunch of files that I need to import sequentially. The files are generated by an order management system, and the format is like this: store_YYYYMMDDHH. I can't use this date because the number of characters are not static:
store_20209111- this can be the file of 01/09/2020 at 11h or 11/09/2020 at 1h.
So, my idea is to change manually the files with a _number like:
store_date_1
store_date_2
So, I want to import these files sequentially: first the store_date_1 and then the store_date_2 and so on.
My code is like these:
For Each ofile In ofolder.Files
DoCmd.SetWarnings False
If VBA.InStr(1, ofile.Name, "~") = 0 Then
If ofile.Type Like "*" & oRecon_otype & " *" Then
'
Debug.Print ofile.Path
ofilepath = ofile.Path
oExFileName = ofile.Name
MsgBox "File to open : " & ofile.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, oTableName, ofilepath, True
i = i + 1
End If
End If
Next ofile
Exit Function
ExitDoor:
MsgBox Err.Number & " - " & Err.Description
End Function
For each file in the specific folder, import all. But I want a piece of code to force access to import the store_1 first, next the store_2 and so on.
Any ideas?
Thank you.