Link to home
Start Free TrialLog in
Avatar of Jenkins
JenkinsFlag for United States of America

asked on

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
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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.
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

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.

Open in new window

Avatar of Jenkins


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(msoFileDialogFilePicker)

    '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
        '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

            '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
Avatar of PatHartman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jenkins


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.
Avatar of Jenkins


I see the problem now.  The filedialog code specifically references .xlsm files.  I changed it to .xlsx and it's ok now.  Thank you.
The Microsoft folks chose less than obvious names for the Excel12 variables so my suggestion of the variable to use changes now that the file type will be .xlsx rather than .xlsm
acSpreadsheetTypeExcel12 = .xlsm
acSpreadsheetTypeExcel12XML = .xlsx