Transfer a spreadsheet with a .xlsm extension

dbfromnewjersey
dbfromnewjersey used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
Top Expert 2016

Commented:
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


Sara

Author

Commented:
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
        .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
Success in ‘20 With a Profitable Pricing Strategy

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!

Distinguished Expert 2017
Commented:
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.

Author

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

Author

Commented:
I see the problem now.  The filedialog code specifically references .xlsm files.  I changed it to .xlsx and it's ok now.  Thank you.
Distinguished Expert 2017

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial