runtime error 13 type mismatch Transferspreadsheet

I using the code below using wildcards in the file name however I'm getting a runtime error 13 type mismatch.

   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBLLIFECYCLE", Me.txtfolder & "\" * "Arlington - MATS Lifecycle" * ".xlsx"
shieldscoAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Then it will be:

strFileName = Dir(strFolder & "\" & "Arlington - MATS Lifecycle*.xlsx")

Open in new window

0
 
David Johnson, CD, MVPOwnerCommented:
[Me.txtfolder [& "\*Arlington - MATS Lifecycle*.xlsx"]

Open in new window

if you want to concatenate strings use the concatenation operator [&] i.e.
string1 & string2 [& string3 & string_n]
0
 
shieldscoAuthor Commented:
Error.JPG
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBLLIFECYCLE", [Me.txtfolder & "\*Arlington - MATS Lifecycle*.xlsx"]
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
shieldscoAuthor Commented:
I'm trying to find the file name that contains Arlington - MATS Lifecycle.xlsx
0
 
shieldscoAuthor Commented:
Error.JPG

   
  Dim strFileName As String
    strFileName = Me.txtfolder Like "\" * "Arlington - MATS Lifecycle" * ".xlsx"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBLLIFECYCLE", strFileName, True

Open in new window

Error.JPG
Error-287.PNG
0
 
PatHartmanCommented:
1. you need to change acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel12XML if you want to use the .xlsx format.
2. You must supply a specific file name in the TransferSpreadsheet expression.  That means you either have to open the Windows file dialog and have the user navigate to the workbook he wants OR, if there will always only be ONE that satisfies the criteria, you can use FSO (File System Object) to search the directory and get the exact file name.  Let us know which you prefer.  I tend to give the users a file dialog since it is ultimately more flexible but it is up to you.
0
 
PatHartmanCommented:
Here is sample code to call up the Windows File Dialog.  Call it with the following line of code in the click event of a button

Me.txtMyFileName = fChooseFile

The code requires a reference to the Microsoft Office library for the version of Office you are using.  Access will automatically "promote" this reference so if you run the code using a newer version of Office, it will still work.  However, if you distribute it to a computer with an older version of Office, the code will fail.  If that is your situation, you would need to modify the code to use "Late Binding" where the library reference isn't made until run time.  This method is less efficient plus you loose intellisense so I don't do it unless I have to.
Public Function fChooseFile()
  
   ' Requires reference to Microsoft Office xx.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
 
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
   With fDialog
 
      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
             
      ' Set the title of the dialog box.
      .Title = "Please select one file"
 
      'starting location
      .InitialFileName = CurrentProject.path
      
      ' Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Excel ", "*.XLSX"
''''      .Filters.Add "Access Databases", "*.MDB"
''''      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "All Files", "*.*"
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next
        
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Function

Open in new window

0
 
shieldscoAuthor Commented:
The last few characters of the file name changes (Arlington - MATS Lifecycle a8f744879.xlsx)  so I'm trying to use wildcards to find the file that contains Arlington - MATS Lifecycle.xlsx. Something like this:

 
   Dim strFileName As String
    strFileName = Me.txtfolder Like "\" * "Arlington - MATS Lifecycle" * ".xlsx"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBLLIFECYCLE", strFileName, True

Open in new window

0
 
PatHartmanCommented:
You CANNOT do it that way.  I thought I made that clear.  You have to use FSO or a Windows File Dialog.  Nothing in your code is asking Access to scan a folder and retrieve a specific file name.  THAT is what you can do with FSO.  I don't have code to offer for that but I posted code for the file dialog.
1
 
shieldscoAuthor Commented:
I do not want to us FSO
0
 
shieldscoAuthor Commented:
I do not want the user to select the file ....
0
 
PatHartmanCommented:
I can understand why you might not want the user to have to select the file but why do you not want to use the code base that includes this functionality?
0
 
shieldscoAuthor Commented:
with you code I would also have to use late binding since you have a reference to a library
0
 
shieldscoAuthor Commented:
Me.txtfolder  is the file location
0
 
PatHartmanConnect With a Mentor Commented:
OK, so do that if you have a mixed Office  version installation.

Or, you could try to modify this to do what you want:
Public Sub LoopUsingDir()
Dim strFileName As String
'TODO: Specify path and file spec
Dim strFolder As String: strFolder = "C:\Data\UsefulDatabases\c2018TaibleMaint\"
Dim strFileSpec As String: strFileSpec = strFolder & "*.*"
Dim FileList() As String
Dim intFoundFiles As Integer
strFileName = Dir(strFileSpec)
Do While Len(strFileName) > 0
    ReDim Preserve FileList(intFoundFiles)
    FileList(intFoundFiles) = strFileName
    intFoundFiles = intFoundFiles + 1
    strFileName = Dir
Loop
End Sub

Open in new window

0
 
shieldscoAuthor Commented:
That is no what I want to do
This is what I'm trying to do
 strFileName = Forms![frmImport]![txtfolder] & "\Arlington - MATS Lifecycle" * ".xlsx"
0
 
shieldscoAuthor Commented:
Compile Error Syntax on line  strFileName = Like & "*" "Arlington - MATS Lifecycle.xlsx"

 
Dim strFileName As String
   Dim strFolder As String
   strFolder = Forms![frmImport]![txtfolder]
   strFileName = Like & "*" "Arlington - MATS Lifecycle.xlsx"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBLLIFECYCLE", strFolder, strFileName, True

Open in new window

0
 
PatHartmanCommented:
You are not listening.  This is not a syntax problem.  I can't simply change your syntax and make this statement work.  You are making the incorrect assumption that:

Like & "*" "Arlington - MATS Lifecycle.xlsx"

will somehow cause Access to loop through all the files in some unspecified folder and extract some file name which it will put into your variable.

Good luck with that.

And as I already mentioned, you have syntax errors in the TransferSpreadsheet statement.  I mentioned one earlier but now I see a second.
0
 
shieldscoAuthor Commented:
Ok thanks ... let someone else have a shot. Your are making the wrong assumption... I'm not trying to loop through all the files. I have one file that part of the name changes on a daily basis.
0
 
Gustav BrockCIOCommented:
This will work:

Dim strFileName As String
Dim strFolder   As String

strFolder = Forms![frmImport]![txtfolder]
strFileName = Dir(strFolder & "\" & "*Arlington - MATS Lifecycle.xlsx")
DoCmd.TransferSpreadsheet acImport,  acSpreadsheetTypeExcel12Xml, "TBLLIFECYCLE", strFolder & "\" & strFileName, True

Open in new window

0
 
shieldscoAuthor Commented:
Error.JPG
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TBLLIFECYCLE", strFolder & "\" & strFileName, True
0
 
Gustav BrockCIOCommented:
That's the folder. You miss the filename.
Seems like you have no file named:

*Arlington - MATS Lifecycle.xlsx

So adjust that.
0
 
shieldscoAuthor Commented:
I have file in the format Arlington - MATS Lifecycle 123 .xlsx. The 123 changes on a daily basis that's why I'm trying to use a wildcard
0
 
shieldscoAuthor Commented:
That worked - let me do a little testing. Good job
0
 
shieldscoAuthor Commented:
Thanks Gustav.... Pat I gave you 100 point even though your suggested solution was not what I was looking for.
0
 
Gustav BrockCIOCommented:
You are welcome!
0
 
PatHartmanCommented:
There was no need to give me any points.

Cudos to Gus for figuring out what you were saying.
0
 
shieldscoAuthor Commented:
Pat - she's very good....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.