Transfer a spreadsheet with a .xlsm extension

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.

Select allOpen in new window

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

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.

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