Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Transfer a spreadsheet with a .xlsm extension
I'm trying to use the following code, which is part of some FileDialog code I got on the net to import an Excel file with a .xlsm extension into Access and it's not working. When I navigate through windows explorer as a result of running the FileDialog code, I'm not seeing the file I want to select. I'm guessing because it has a .xlsm extension.
Also, if I can get the file to import, I'd like to only include columns C, D and E as well as start on row 16 if possible (skip over the first 15 rows).
tblFileName = strFilePath
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTest", tblFileName, True
Also, if I can get the file to import, I'd like to only include columns C, D and E as well as start on row 16 if possible (skip over the first 15 rows).
tblFileName = strFilePath
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTest", tblFileName, True
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What code are you using to get the value of strFilePath?
But the concept of tblFilename being equal to strFilePath really makes no sense at all. If the code you are using to open and select the file from the FileDialog is supposed to return a filename, instead of a path, then you ought to give that result a variable name that looks like a filename.
You should also add some code to check the value returned by that function and exit out of your code if the value is an empty string.
But the concept of tblFilename being equal to strFilePath really makes no sense at all. If the code you are using to open and select the file from the FileDialog is supposed to return a filename, instead of a path, then you ought to give that result a variable name that looks like a filename.
You should also add some code to check the value returned by that function and exit out of your code if the value is an empty string.
as far as i remember file dialog gets a list of extensions to show. in c and c++ it is somewhat strange because in the char array to pass the list of extensions, e. g. ".xlsm" and ".xls" are separated by a binary zero char. the end of the list is done by (at least) two zero characters.
if you look at such a 'list' with the debugger, you see only the first extension because of the terminating zero character.
to build such a list you would do like
Sara
if you look at such a 'list' with the debugger, you see only the first extension because of the terminating zero character.
to build such a list you would do like
char strexts[64] = { 0 }; // makes all zero
int len = 0;
strcpy(strexts, ".xlsm");
len = strlen(strexts)+1;
strcpy (&strexts[len], ".xls"); // at strexts[len-1] we got a binary zero.
len += strlen(".xls") +1;
strcpy (&strexts[len], ".csv");
// at end we need at least to zero characters what is granted if the buffer is big enough.
Sara
Here's the code I got off the net.
Sub btn_GetFileName_Click()
'*************************
'Lets get the file name
Debug.Print "Getting File Name"
'Declare a variable as a FileDialog object.
Dim fd As FileDialog
'Set the starting look location
Dim strComPath As String
strComPath = "C:\"
Dim strFilePath As String
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(mso
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant
'Use a With...End With block to reference the FileDialog object.
With fd
.InitialFileName = strComPath
.AllowMultiSelect = False
.Filters.Clear
'Add filter to only show excel files.
.Filters.Add "Excel files", "*.xlsm", 1
'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
strFilePath = .SelectedItems(1)
'Step through each string in the FileDialogSelectedItems collection.
'For Each vrtSelectedItem In .SelectedItems
'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
' strFilePath: " & vrtSelectedItem
'Next vrtSelectedItem
Else
'The user pressed Cancel.
DoCmd.Hourglass (False)
MsgBox "You must select a file to import before proceeding", vbOKOnly + vbExclamation, "No file Selected, exiting"
Set fd = Nothing
Exit Sub
End If
End With
tblFileName = strFilePath
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_TEST", tblFileName, True
Set fd = Nothing
End Sub
Sub btn_GetFileName_Click()
'*************************
'Lets get the file name
Debug.Print "Getting File Name"
'Declare a variable as a FileDialog object.
Dim fd As FileDialog
'Set the starting look location
Dim strComPath As String
strComPath = "C:\"
Dim strFilePath As String
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(mso
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant
'Use a With...End With block to reference the FileDialog object.
With fd
.InitialFileName = strComPath
.AllowMultiSelect = False
.Filters.Clear
'Add filter to only show excel files.
.Filters.Add "Excel files", "*.xlsm", 1
'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
strFilePath = .SelectedItems(1)
'Step through each string in the FileDialogSelectedItems collection.
'For Each vrtSelectedItem In .SelectedItems
'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
' strFilePath: " & vrtSelectedItem
'Next vrtSelectedItem
Else
'The user pressed Cancel.
DoCmd.Hourglass (False)
MsgBox "You must select a file to import before proceeding", vbOKOnly + vbExclamation, "No file Selected, exiting"
Set fd = Nothing
Exit Sub
End If
End With
tblFileName = strFilePath
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_TEST", tblFileName, True
Set fd = Nothing
End Sub
I tried the code you posted and it worked fine for me. Make sure you are moving the dialog to the correct directory.
I created a form with a button and a text box. I assume you have set the correct reference to make the code work. The code REQUIRES a reference to Microsoft Office xx.x Object Library. On my computer xx.x = 16.0.
I'm not sure what version of Excel created the file but you might want to use
acSpreadsheetTypeExcel12
rather than
acSpreadsheetTypeExcel9
The spreadsheet I created didn't have any macros in it so there may be an issue if you have macros turned off. check that security setting also.
I also changed the reference to the text box to Me.tblFileName for neatness. It is best practice to reference the form object when working with controls on a form.
I created a form with a button and a text box. I assume you have set the correct reference to make the code work. The code REQUIRES a reference to Microsoft Office xx.x Object Library. On my computer xx.x = 16.0.
I'm not sure what version of Excel created the file but you might want to use
acSpreadsheetTypeExcel12
rather than
acSpreadsheetTypeExcel9
The spreadsheet I created didn't have any macros in it so there may be an issue if you have macros turned off. check that security setting also.
I also changed the reference to the text box to Me.tblFileName for neatness. It is best practice to reference the form object when working with controls on a form.
I made a mistake. It is files with a .xlsx extension that I'm not able to see when I open windows explorer using the above filedialog code. The file I want to transfer into Access has a .xlsx extension and only files with a .xlsm extension are displaying. The .xlsx file does have macros and I have Microsoft Office 15.0 Object Library selected in the References window of Access. Sorry.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial