Pull Data from Another Workbook

[helpexcel]

I'd like to pull data from another workbook. I'd like to open the folder that's named in A2. The user would then find the workbook and open it. I can populate the code for copying, but then i would want the workbook to close. Is opening and closing the workbook the quickest way to pull data?

 

[Yongle]

Without knowing exactly what you are trying to do this is a method to adapt to your needs
VBA below assumes the path is specified in cell A2 ( C:\folder\subfolder\subsubfolder )
Amend sheet name SheetXXX

Sub Test_GetValues()
    Dim fPath As String, fOpen As Variant, wb As Workbook, rng1 As Range, rng2 As Range
    fPath = Sheets("[COLOR=#ff0000]SheetXXX[/COLOR]").Range("A2")
[I][COLOR=#006400]'specify default drive and folder for GetOpenFilename[/COLOR][/I]
    ChDrive Left(fPath, 1)
    ChDir fPath
[I][COLOR=#006400]'get workbook[/COLOR][/I]
    fOpen = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If fOpen <> False Then Set wb = Workbooks.Open(fOpen) Else GoTo handling
[COLOR=#006400][I]'get ranges[/I][/COLOR]
    With Application
        Set rng1 = .InputBox("Select range to pull", "Get data", , , , , , 8)
        ThisWorkbook.Activate
        Set rng2 = .InputBox("Select paste cell", "Paste where", , , , , , 8)
    End With
[COLOR=#006400][I]'copy\paste values & formats[/I][/COLOR]
    rng1.Copy
    rng2.PasteSpecial (xlPasteFormulasAndNumberFormats)
    rng2.PasteSpecial (xlPasteValues)
    wb.Close False                                                              [I][COLOR=#006400]'close without saving[/COLOR][/I]
Exit Sub

handling:
    MsgBox "nothing selected"
End Sub

 

[helpexcel]

thanks! how do i hard code part of the path and then fill it with A5. so \\Server\Folder1\Folder2\Folder3\Range("A5")

 

[Yongle]

instead of ...

fPath = Sheets("SheetXXX").Range("A2")

build the required string like this ...

fPath = [COLOR=#ff0000]"K:\Folder\Subfolder\SubSubFolder\" & [/COLOR]Sheets("SheetXXX").Range("A2")

 

[helpexcel]

Is this trying to open a specific file? I'm looking to just open a specific folder and then let the user choose the file. Sorry for any confusion.

 

[Yongle]

It does exactly what you requested

Your filepath is the problem and I cannot help you with that
My code is looking for this type of path C:\folder\subfolder\subsubfolder
And you are trying to use \\Server\Folder1\Folder2\Folder3

You could begin a new thread to resolve that issue and then my code will work

 

[helpexcel]

I'm getting a run time error 5 - Invalid call or procedure on this line: ChDrive Left(fPath, 1)

 

[Yongle]

what is the value of fPath?
the code is looking for a drive letter

 

[helpexcel]

Ah, there is no drive letter. There's an address.