Results 1 to 9 of 9

Thread: Pull Data from Another Workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2009
    Posts
    443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Pull Data from Another Workbook

    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?

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,256
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Pull Data from Another Workbook

    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("SheetXXX").Range("A2")
    'specify default drive and folder for GetOpenFilename
        ChDrive Left(fPath, 1)
        ChDir fPath
    'get workbook
        fOpen = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
        If fOpen <> False Then Set wb = Workbooks.Open(fOpen) Else GoTo handling
    'get ranges
        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
    'copy\paste values & formats
        rng1.Copy
        rng2.PasteSpecial (xlPasteFormulasAndNumberFormats)
        rng2.PasteSpecial (xlPasteValues)
        wb.Close False                                                              'close without saving
    Exit Sub
    
    handling:
        MsgBox "nothing selected"
    End Sub
    Last edited by Yongle; May 17th, 2019 at 07:15 AM.

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Posts
    443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Data from Another Workbook

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

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,256
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Pull Data from Another Workbook

    instead of ...
    Code:
    fPath = Sheets("SheetXXX").Range("A2")
    build the required string like this ...
    Code:
    fPath = "K:\Folder\Subfolder\SubSubFolder\" & Sheets("SheetXXX").Range("A2")
    Last edited by Yongle; May 17th, 2019 at 09:24 AM.

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Posts
    443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Data from Another Workbook

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,256
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Pull Data from Another Workbook

    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

  7. #7
    Board Regular
    Join Date
    Oct 2009
    Posts
    443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Data from Another Workbook

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

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,256
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Pull Data from Another Workbook

    what is the value of fPath?
    the code is looking for a drive letter
    Last edited by Yongle; May 17th, 2019 at 11:07 AM.

  9. #9
    Board Regular
    Join Date
    Oct 2009
    Posts
    443
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Data from Another Workbook

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •