Select file to pull data from

[Mikeymike_W]

Hi all,

I was given a great piece of code the other day to help pull a range of data from several worksheets in a fixed location. This works great but now my boss has asked me to do something similar for another spreadsheet but this time the the source where I want to pull data from is changeable.

Is there a way to alter the code below so that it opens a file browser for the user to select the file to pull the data from?
If possible I don't actually want to open this file just for excel to know the path where the data needs to come from.

Sub ConsolidateWbks()
Application.ScreenUpdating = False
Application.EnableEvents = False
    Dim Pth As String
    Dim MstSht As Worksheet
    Dim fname As String
    Dim Rng As Range
    Dim vFile As Variant
    
    

  Pth = "C:\Users\MWa\Desktop\VBA Test\"
    Set MstSht = ThisWorkbook.Sheets("Test1")
    fname = Dir(Pth & "*xls*")
    Do While Len(fname) > 0
        Workbooks.Open (Pth & fname)
        With Workbooks(fname)
        
        .Sheets("Global FACT").Unprotect "watters"
            Set Rng = MstSht.Range("D" & Rows.Count).End(xlUp).Offset(1)
            Rng.Resize(, 35).Value = Application.Transpose(.Sheets("Global FACT").Range("B2:B36").Value)
            Rng.Offset(, 35).Value = .Sheets("Global FACT").Range("C36").Value
            Rng.Offset(, 36).Value = .Sheets("Global FACT").Range("B37").Value
            Rng.Offset(, 37).Value = .Sheets("Global FACT").Range("B38").Value
            Rng.Offset(, 38).Value = .Sheets("Global FACT").Range("B39").Value
            Rng.Offset(, 39).Value = .Sheets("Global FACT").Range("B40").Value
            Rng.Offset(, -2).Value = .Sheets("CRF").Range("C12").Value
            Rng.Offset(, -3).Value = .Sheets("CRF").Range("C16").Value
            Rng.Offset(, -1).Value = .Sheets("CRF").Range("G4").Value
            .Sheets("Global FACT").Protect "watters"
            Application.DisplayAlerts = False
            .Close , False
            Application.DisplayAlerts = True
        End With
        fname = Dir
    Loop
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Many thanks,

Mike

 

[Roderick_E]

fname = [COLOR=#747474][FONT=monospace]Application.GetOpenFilename _[/FONT][/COLOR]
[COLOR=#747474][FONT=monospace](Title:="Please choose a file to open", _[/FONT][/COLOR]
[COLOR=#747474][FONT=monospace]FileFilter:="Excel Files *.xls* (*.xls*),")[/FONT][/COLOR]

 

[Mikeymike_W]

Hi Roderick,

Thanks for your help

I have tried slotting this code into mine, it allows me to select a file but once I do this it throws up a debug error at:

with workbooks (fname)

Any ideas how the code should be slotted in?

Many thanks,

Mike

 

[Roderick_E]

do a test to see what fname is, before the 'with' line maybe add msgbox fname It should help you figure out the problem.

 

[Mikeymike_W]

Hi Again,

Popped in the message box, it showed me which file was being selected (the correct file) so not sure what else to do.

Hopefully there's an easy solution but I have no idea.

Thanks for any help,

Mike