runtime error 13 type mismatch Transferspreadsheet

[Me.txtfolder [& "\*Arlington - MATS Lifecycle*.xlsx"]

Select allOpen in new window

if you want to concatenate strings use the concatenation operator [&] i.e.
string1 & string2 [& string3 & string_n]

   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBLLIFECYCLE", [Me.txtfolder & "\*Arlington - MATS Lifecycle*.xlsx"]

I'm trying to find the file name that contains Arlington - MATS Lifecycle.xlsx

   

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

Select allOpen in new window

Error.JPG
Error-287.PNG

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.

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

Select allOpen in new window

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

Select allOpen in new window

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.

I do not want to us FSO

I do not want the user to select the file ....

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?

with you code I would also have to use late binding since you have a reference to a library

Me.txtfolder  is the file location

That is no what I want to do
This is what I'm trying to do
 strFileName = Forms![frmImport]![txtfolder] & "\Arlington - MATS Lifecycle" * ".xlsx"

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

Select allOpen in new window

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.

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.

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

Select allOpen in new window

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TBLLIFECYCLE", strFolder & "\" & strFileName, True

That's the folder. You miss the filename.
Seems like you have no file named:

*Arlington - MATS Lifecycle.xlsx

So adjust that.

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

That worked - let me do a little testing. Good job

Thanks Gustav.... Pat I gave you 100 point even though your suggested solution was not what I was looking for.

You are welcome!

There was no need to give me any points.

Cudos to Gus for figuring out what you were saying.

Pat - she's very good....