Pull Data from Another Workbook

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
547
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,677
Office Version
365
Platform
Windows
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

Code:
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
 
Last edited:

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
547
thanks! how do i hard code part of the path and then fill it with A5. so \\Server\Folder1\Folder2\Folder3\Range("A5")
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,677
Office Version
365
Platform
Windows
instead of ...
Code:
fPath = Sheets("SheetXXX").Range("A2")
build the required string like this ...
Code:
fPath = [COLOR=#ff0000]"K:\Folder\Subfolder\SubSubFolder\" & [/COLOR]Sheets("SheetXXX").Range("A2")
 
Last edited:

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
547
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,677
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 21, 2009
Messages
547
I'm getting a run time error 5 - Invalid call or procedure on this line: ChDrive Left(fPath, 1)
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,677
Office Version
365
Platform
Windows
what is the value of fPath?
the code is looking for a drive letter
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,096,141
Messages
5,448,579
Members
405,520
Latest member
youzukk

This Week's Hot Topics

Top