Changing the directory using ChDir

[azizrasul]

Why does ChDir not change the directory in the following code?

    ChDrive "C"
    ChDir Nz("C:\Users\Aziz\Desktop", "")

    strFilePathandName = objExcelApp.Application.GetOpenFilename(Title:="SELECT MS EXCEL FILE", FileFilter:="Excel Files *.xls* (*.xls*),")

 

[smozgur]

ChDir and ChDrive wouldn 't affect where Excel instance of GetOpenFilename method looks for. It would work only if you run the code in Excel.

You need to use Access equivalent to get the file name via file open dialog:

Note: If you don't like using Late Binding then you need to add Office Object Library and declare fileDialog As Office.FileDialog.

Sub selectFile()
Dim fileDialog As Object
Dim strFilePathandName As String
    Set fileDialog = Application.fileDialog(3)
    With fileDialog
        .Title = "SELECT MS EXCEL FILE"
        .InitialFileName = "C:\Users\Aziz\Desktop"
        With .Filters
            .Clear
            .Add "Excel Files", "*.xls*"
        End With
        If .Show = True Then
            ' File is selected
            strFilePathandName = .SelectedItems(1)
        Else
            ' No file selected - cancel clicked
            Exit Sub
        End If
    End With
    MsgBox strFilePathandName
End Sub

 

[azizrasul]

I am trying to open a password protected Excel file, but I want to pass the filename and password from another function. However I need a file dialog type function which starts from a predefined folder.

The line

If .Show = True Then

makes the password dialog appear, which I want to avoid.

Can't find an equivalent of GetOpenFilename in Access.
I want late binding code.

 

[smozgur]

The code I provided as sample is in FilePicker mode since I used 3 as the function parameter:
msoFileDialogFilePicker = 3

So, all it is supposed to do is returning the file name as string that you selected in the file dialog.
It works as it is supposed to in my computer.

 

[azizrasul]

Will have a look and get back to you.

BTW, will the .Show line not bring up the Password dialog box, which I want to avoid?

 

[azizrasul]

Unfortunately the .Show line makes the password dialog appear.

I want a string variable to accept the filepath and filename of a password protected MS Excel from an initial directory without the MS Excel file opening up.

 

[smozgur]

As I said, it works as you need in my computer. It doesn’t ask password or something (it shouldn’t). It simply returns the selected file name as string. I have no idea why it doesn’t work at your side.

Someone else might come up with better solution.

 

[azizrasul]

smozgur, I know I may be asking a lot, but would you be able to send me a sample MS Access db with the code so I can see what is different to your outcome compared to mine?

 

[smozgur]

We shouldn't share any files for security purposes in public forums. However, it is easy to test the code as I explain below.

Just create a new database, and go to VBE and create a new module. Copy and paste the code I put above and run it by pressing F5 button or Run command on the menu. Do not add or use any of your code, only the code I sent above. That's it. This way, we'll see how it is working in your computer.

 

[azizrasul]

Tried the code on a new Access db, and I get the same problem. The MS Excel window opens up along with the password dialog box. When I press cancel, I re-select the Excel file and this time the message box gives me the filename. Hence the initial issue still hasn't gone away.