FileDialog does not work in some excel versions VBA

Question

I have a code that works in the excel version 2010 normally, but in the 2013 version does not. Take a look.

Sub select_strategy()
'Subroutine that lets the user select a strategy table
'
    'Declare local variables
    Dim strategyFileDialog As Office.FileDialog
    'Declare local variables
    Dim intStrategySelection As Integer

    'Initialize global variables
    Call initialize_global_variables

    ' Allows the user to select the strategy table to use
    Set strategyFileDialog = Application.FileDialog(msoFileDialogFilePicker)

    With strategyFileDialog
        .Title = "Select Strategy Table"
        .InitialFileName = ActiveWorkbook.Path
        .AllowMultiSelect = False
    End With

    intStrategySelection = strategyFileDialog.Show  'updates cell only if a file is selected
     If intStrategySelection <> 0 Then
        wsMain.Cells(2, 3) = strategyFileDialog.SelectedItems(1)
     Else
        wsMain.Cells(2, 3) = ""
     End If

End Sub

The error for the 2013 version is Compile error: Cant find project or library. Any ideas how to solve it?

Either switch to late binding or change the reference to Microsoft Office 15.0 Object Library. — Comintern, Nov 30 '16 at 13:53
http://stackoverflow.com/questions/9476268/filedialog-doesnt-work — Preston, Nov 30 '16 at 13:54

score 0 · Answer 1 · answered Jan 16 '19 at 19:22

To use late binding use the FileDialog() function from the Excel.Application Object. Excel.Application.FileDialog() Is a function that returns a FileDialog Object for an open Workbook. If you declare a variable as a specific object such as Office.FileDialog it will create the object of the version that is used in the VBA reference the code was written with and at runtime on a different pc will ignore other version references and look for the specific version that the variable was declared with.

You can use the reference to the active workbook but is not explicitly needed in Excel

Dim strategyFileDialog As Object 
Set strategyFileDialog = ActiveWorkbook.Application.FileDialog(3)

'Filedialog Enumerations: 
'msoFileDialogFilePicker = 3 File picker dialog box. 
'msoFileDialogFolderPicker = 4 Folder picker dialog box. 
'msoFileDialogOpen = 1 Open dialog box. 
'msoFileDialogSaveAs = 2 Save As dialog box

FileDialog does not work in some excel versions VBA

1 Answers1