how to use saved import when file name changes

[Bobbie]

I need to import an excel file every week into an access database. the file is always saved in the same folder called "Current" however the file name changes week to week because of a date and time stamp.

For example this week the file is named: Weekly_Internet_Order_Matchup_Converted_Channel_Summary_20120721_080603
next week it will be
Weekly_Internet_Order_Matchup_Converted_Channel_Summary_20120728_074452

Is there a way for me to import the file by ignoring everything after the "y"?

 

[frispee]

Why don't you locate the file using a 'Browse to..' dialog box every time you import the excel sheet? This way you need not hard code even the folder location. The code is pretty simple.

Dim fileDump As FileDialog
Set fileDump = Application.FileDialog(msoFileDialogOpen)
fileDump.Show           'This will POP up the File choose dialog box.
Dim Yourroute As String
Yourroute = fileDump.SelectedItems(1) 'path to file

I should mention that I took this piece of code from elsewhere in this forum and used it in one of my applications some time back. This is the only thing I can think of. I am sure the experts can come up with exactly what you want.

 

[Bobbie]

I actually have 15 or 16 different excel files that need to be imported into different Access tables and wanted to set up a quick way to import them. My problem is that each excel file name ends with a date and time stamp. I had added this to a form that kicks off several macros but I keep getting a compile error "line number or label or statement or end of statment and I don't know what that means.
Sub GetMyFile()
Dim strSQL As String
strSQL = "INSERT INTO Matched Ad Cost"
" SELECT *" & _
" FROM [Text" & _
";FMT=Delimited;HDR=YES;CharacterSet=437" & _
";DATABASE=U:\Print_Catalog_Marketing_Reports\USAP_WeeklyReports\Current]." & _
"[Weekly_Internet_Order_Matchup_Converted_Channel_Summary_" & Format(Date(), "yyyymmdd") & "_" & Format (Now(), "hhmmss" & ".xlsx];"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

 

[sxschech]

Try adding the following line after your strSQL statement to see what it shows. This will show the results of strSQL in the debug window of the vbaEditor.

debug.print strSQL

Then if nothing obvious stands out, try manually running that result in the immediate window. Maybe set up the filename as a variable and then put the variable in the strSQL statement.

Here is an example from my code to create the filename with a date and has variables.

stFileName = "CountryList" & Right(Me.txtCurrentYr, 2) & stTerm & "_" & Format(Me.txtRunDate, "YYYYMMDD") & ".pdf"

" SELECT *" & _
             " FROM  [Text" & _
                    ";FMT=Delimited;HDR=YES;CharacterSet=437" & _
                    ";DATABASE=U:\Print_Catalog_Marketing_Reports\USAP  _WeeklyReports\Current]." & _
                    "[stFileName];"

 

[Bobbie]

I tried something different this moringing.
Private Sub TransferSpreadsheet()
On Error GoTo Err_Command1_Click

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Matched Ad Costs", _
Dir("U:\Print_Catalog_Marketing_Reports\USAP_WeeklyReports\Current\Weekly_Internet_Order_Matchup_Converted*", vbNormal), True
Exit_Command1_Click:
Exit Sub
Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

But for some reason instead to looking at the path I have listed it is changing U:\Print_Catalog_Marketing_Reports\USAP_WeeklyReports\Current to C:\Users\rmacy\documents.

I don't know what it is changing the path?

 

[sxschech]

I would think you would need to define the filename before including it in the Transferspreadsheet statement. If the filename is known, why do you need to use dir(). Also a wildcard means it is returning more than one file, but the transfer spreadsheet would only work on one file.