Using inputbox to open specific files from a folder

[tcox090]

As a warning, I am somewhat new to using VBA, butI have a feeling I am pretty close to figuring this out.
I have a folder with a bunch of different files and I need to open them one at a time to compare them with a master file.

My input box is where I will have the user add the specific file name. A
fter doing this, my string is left with quotations around it so when I put data_wbk1 into the file path, I am getting a run-time error because 'C:Users\......\"CapFcst2014".xlsm' could not be found. If i get rid of the quotes around the file name, it would open right up.

'Open Outdated Workbook
data_wbk1 = InputBox("Enter File Name:", Default:="Paste File Name Here")
Workbooks.Open ("C:\Users\036494\Desktop\CapComp_14&15\ """ & data_wbk1 & """.xlsm")

Thanks

 

[CalcSux78]

I think you're looking for:

'Open Outdated Workbook
data_wbk1 = InputBox("Enter File Name:", Default:="Paste File Name Here")
Workbooks.Open ("C:\Users\036494\Desktop\CapComp_14&15\" & data_wbk1 & ".xlsm")

 

[CalcSux78]

Another way to do the same thing but give the user the ability to click the file from a File Explorer window...


Add this to a Module:

Function GetFileName() As Variant
Dim FInfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
 
'Set up list of file filters
FInfo = "All Files (*.*) ,*.*," & _
        "Old Excel (*.xls) , *.xls," & _
        "New Excel (*.xlsx) , *.xlsx," & _
"Macro Excel (*.xlsm) , *.xlsm"
FilterIndex = 3            'Change to select a default filter
Title = "Select a File containing the comment data"
'Get the filename
FileName = Application.GetOpenFilename(FInfo, FilterIndex, Title)
'Handle return info from dialog box
If FileName = False Then
    GetFileName = ""
Else
    GetFileName = FileName
End If
End Function

The above function allows the user to select the file to be used in your code. To use the function, you would add the following to your code.

Dim FileName As String
 
FileName = GetFileName()
If Len(FileName) > 1 Then
    Workbooks.Open FileName:=FileName, ReadOnly:=True
    Set wb = Workbooks(Dir(FileName))
    'do something with newly opened workbook here
Else
    Exit Sub
End If

 

[danb96]

@CalcSux78 I know this is a very old thread but your code just made my life a lot easier! Thanks!