opening excel workbooks in vba

Question

below is a file path i need the workbook to open however this file path changes so i have an input box that gives the file path and dimmed as location

Workbooks.Open ("C:\Users\AylingB\OneDrive - TGE\Desktop\coding test for calcs\C5663-TD37-CAL-1900-0005_A TANK DOME ROOF STRUCTURE.xlsm")

so i instead have this

A = Range("p1")
B = Range("p2")
C = Range("p3")
Dim location As String
location = "(" & """" & A & B & C & """" & ")"
Debug.Print location
Workbooks.Open location

p1-3 is the file path split in certain cells(this cannot change unfortunately)

this does not work however even tho it equals the exact same pathway (brackets and quotaton marks included) is there any way of doing this without having to go to the vba code every time and changing it

i have tried concatenation to bring everything together. ive also tried it with and without brackets with and without speech marks im just abit lost as when i do debug .print the code looks exactly the same but only works when its typed out fully

So you can check out building a path & name here, one example there are many others on here: https://stackoverflow.com/q/30575923/4961700 — Solar Mike, Oct 26 '22 at 09:01
Can you show a little bit more of your code, especially how you fill `filepath`. — FunThomas, Oct 26 '22 at 09:03
`this does not work` - what does not work? You need to be more specific in order to get help. Are you absolutely sure the paths are equal? Test it in code to make sure. — Olle Sjögren, Oct 26 '22 at 09:23

score 0 · Accepted Answer · answered Oct 26 '22 at 09:32

The GetFileName function will ask open the FileDialog and ask you to select the file - starting in the same folder as the file containing the code (ThisWorkbook).

Test shows how to use it - and store a reference to it in a variable.
GetFileName is the function you should copy to your project.

Sub Test()

    Dim MyFilePath As String
    MyFilePath = GetFileName 'Ask for the filename & path
    
    Dim MyFile As Workbook
    
    'Check that a file was selected, and it wasn't this file.
    If MyFilePath <> "" And MyFilePath <> ThisWorkbook.FullName Then
        Set MyFile = Workbooks.Open(MyFilePath) 'Open the file and set a reference to it.
        
        'Rest of your code.
        'Use "MyFile" whenever referencing the workbook.
        
        Dim MySheet As Worksheet
        Set MySheet = MyFile.Worksheets(1)
        
        Dim LastRow As Long
        LastRow = MySheet.Cells(Rows.Count, 1).End(xlUp).Row
        
        MsgBox MyFile.Name & " contains " & MyFile.Worksheets.Count & " worksheets." & vbCr & _
            "The last row in " & MySheet.Name & " column A is " & LastRow
        
    End If
    
End Sub

Private Function GetFileName() As String

    Dim FD As FileDialog
    Set FD = Application.FileDialog(msoFileDialogFilePicker)
    
    With FD
        .InitialFileName = ThisWorkbook.Path & Application.PathSeparator
        .AllowMultiSelect = False
        
        'Not needed unless want to limit to specific file types.
        .Filters.Clear 'On it's own will set filter to "All Files", "*.*"
        .Filters.Add "Excel Files", "*.xlsx, *.xlsm, *.xls"
        .Filters.Add "Other Files", "*.csv, *.someotherextension"
        .FilterIndex = 2 'Display "Other Files" as default.
        
        If .Show = -1 Then
            GetFileName = .SelectedItems(1)
        End If
    End With
    
    Set FD = Nothing

End Function

opening excel workbooks in vba

1 Answers1