Excel VBA Open xlsx File From Folder Without writing Path

Question

I want to open Excel xlsx file without writing path by using variables. I din't know why but it is not working. I have a folder with the main workbook and another one that I want to open that is xlsx. I want to name it UnionWB.

  Private Sub cmdStartMonth_Click()
    'Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    'Analyze month by selecting
    Dim myPath As String
    Dim myFile As String
    Dim UnionWB As Workbook
    Dim MonthName As String
    MonthName = ListMonth.Value
    myExtension = "*.xlsx*"
    Set UnionWB = Workbooks.Open(ThisWorkbook.Path & myExtension)

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub

Set UnionWB = Workbooks.Open(ThisWorkbook.Path & myExtension)

Where is the file on your computer? What is wrong with declaring the folderpath, should it be dynamic? — Luuklag
– Luuklag, Commented Nov 8, 2017 at 9:19
I want it to be dynamic. and the folder names are not in english, I cant use in the code. @Luuklag — Rafael Osipov
– Rafael Osipov, Commented Nov 8, 2017 at 9:21
Your code is saying the file name is *.xlsx and not a specific file name. myExtension isn't declared and ThisWorkbook.Path won't have a file separator on the the end - it should be ThisWorkbook.Path & "\" & myExtension. You'll still need to sort out which file it's trying to open though - *.xlsx doesn't refer to a single file. MonthName isn't used anywhere and ScreenUpdating & EnableEvents isn't needed. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Nov 8, 2017 at 10:14
What do you mean by "without writing path". ThisWorkbook.Path is a pretty static path - it's wherever the workbook containing the code is saved, so the workbook you're trying to open must be in the same folder as the original file. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Nov 8, 2017 at 10:20

Darren Bartrup-Cook · Accepted Answer · 2017-11-08 13:41:31Z

2

Here's a couple of examples that may help.

The first will ask you to select the correct file and then open it:

Public Sub Test()

    Dim sWrkbkPath As String
    Dim UnionWB As Workbook

    sWrkbkPath = GetFile(ThisWorkbook.Path)
    If sWrkbkPath <> "" Then
        Set UnionWB = Workbooks.Open(sWrkbkPath)
        MsgBox UnionWB.Name
    End If

End Sub

Function GetFile(Optional startFolder As Variant = -1) As Variant
    Dim fle As FileDialog
    Dim vItem As Variant
    Set fle = Application.FileDialog(msoFileDialogFilePicker)
    With fle
        .Title = "Select your Union Workbook"
        .AllowMultiSelect = False
        .Filters.Add "My Union Workbook", "*.xlsx", 1
        If startFolder = -1 Then
            .InitialFileName = Application.DefaultFilePath
        Else
            If Right(startFolder, 1) <> "\" Then
                .InitialFileName = startFolder & "\"
            Else
                .InitialFileName = startFolder
            End If
        End If
        If .Show <> -1 Then GoTo NextCode
        vItem = .SelectedItems(1)
    End With
NextCode:
    GetFile = vItem
    Set fle = Nothing
End Function

The second method assumes you only have a single xlsx file in the same folder as ThisWorkbook and opens the first file it finds:

Public Sub OpenOnlyXLSXInFolder()
    Dim sWrkbkPath As String
    Dim UnionWB As Workbook
    sWrkbkPath = Dir$(ThisWorkbook.Path & "\*.xlsx")

    'Only expecting a single file so no need to loop.
    If sWrkbkPath <> "" Then
        Set UnionWB = Workbooks.Open(ThisWorkbook.Path & "\" & sWrkbkPath)
        MsgBox UnionWB.Name
    End If

End Sub

edited Nov 8, 2017 at 13:41

answered Nov 8, 2017 at 11:31

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rafael Osipov Over a year ago

the second sub is what I need, but somehow it says that it could not find the file with its right name and it is the only xlsx file in the folder

Rafael Osipov Over a year ago

"sorry we couldn't find "bla bla". is it possible it was removed, renamed or deleted?"

Rafael Osipov Over a year ago

these 2 files are the only one in the folder and still it shows me a massage box that it cant find the xlsx file in the folder.

Darren Bartrup-Cook Over a year ago

Sorry, that was my fault. For some reason it was working for me even though Dir only returns the workbook name and not the path as well. When I tried it in a different folder I had the same error as you - I've updated the code to include the path when opening the file.

Collectives™ on Stack Overflow

Excel VBA Open xlsx File From Folder Without writing Path

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related