shieldsco
asked on
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"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBLLIFECYCLE", Me.txtfolder & "\" * "Arlington - MATS Lifecycle" * ".xlsx"
ASKER
ASKER
I'm trying to find the file name that contains Arlington - MATS Lifecycle.xlsx
ASKER
Dim strFileName As String
strFileName = Me.txtfolder Like "\" * "Arlington - MATS Lifecycle" * ".xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBLLIFECYCLE", strFileName, True
Error.JPGError-287.PNG
1. you need to change acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel12XM L 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.
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.
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
ASKER
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
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.
ASKER
I do not want to us FSO
ASKER
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?
ASKER
with you code I would also have to use late binding since you have a reference to a library
ASKER
Me.txtfolder is the file location
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is no what I want to do
This is what I'm trying to do
strFileName = Forms![frmImport]![txtfold er] & "\Arlington - MATS Lifecycle" * ".xlsx"
This is what I'm trying to do
strFileName = Forms![frmImport]![txtfold
ASKER
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
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.
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.
ASKER
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
ASKER
That's the folder. You miss the filename.
Seems like you have no file named:
*Arlington - MATS Lifecycle.xlsx
So adjust that.
Seems like you have no file named:
*Arlington - MATS Lifecycle.xlsx
So adjust that.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked - let me do a little testing. Good job
ASKER
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.
Cudos to Gus for figuring out what you were saying.
ASKER
Pat - she's very good....
Open in new window
if you want to concatenate strings use the concatenation operator [&] i.e.string1 & string2 [& string3 & string_n]