Avatar of Jenkins
Jenkins
Flag 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
Microsoft AccessWindows OS

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
Dale Fye

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

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
Jenkins

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jenkins

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

ASKER
I see the problem now.  The filedialog code specifically references .xlsm files.  I changed it to .xlsx and it's ok now.  Thank you.
PatHartman

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.