Declare variable by choosing file

[sh1ne]

Hi Guys,

I have a little problem, everytime when I need to run macro the name of files are changed. I want to automatize it maximally as I can and it need to be friendly for newbies.
How can I assign name of chosen files to macros using box like below?

Need to run twice this box, choose file and assign it to variable. Simply? Not for me

Would be thankful if someone helps me.

 

[Fluff]

Rather than creating your own input box you can use the inbuilt filepicker like

Sub GetFname()
'sh1ne

    Dim Fname1 As String
    Dim Fname2 As String
    Dim Wb1 As Workbook
    Dim Wb2 As Workbook
    
    Fname1 = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", _
                Title:="Select Source File")
    Fname2 = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", _
                Title:="Select Source File")

    If Fname1 <> "False" Then Set Wb1 = Workbooks.Open(Fname1)
    If Fname2 <> "False" Then Set Wb2 = Workbooks.Open(Fname1)

End Sub

 

[sh1ne]

Thanks Fluff,

there arrives problem "subscript out of range" there:

I've realized these things:

1. Variables are strings, and I always have its opened, one or both files(Im running macro from "destinyfile") so all what I need is just get name of this Workbook. (my proposition)
2. We can do the same to "sourcefile", get filename or just open = no matters
3. Before it, Ive just changing names in first lines of code, but as I said - need to automatize it and being friendly UX. (thats why Ive wanted combox(?))
4. Operations are made in both workbooks in "Data" worksheets. So name of worksheets are constant, but name of workbooks are variables.

 

[Fluff]

If you are using the code snippet I supplied the problem line should be

Wb1.Worksheets("Data")