Creating a Macro to import data from other Excel document


New Member
Nov 21, 2018
Hi there,

I'm trying to create a series of Macros in a document, that will enable us to import data from a sheet on another document, into a sheet onto the active document.

Multiple people will have access to the document, at different locations, so the files the data is being imported from will not be kept on a shared drive - the Macro will need to locate the document by file name, on someone's desktop.

E.g. - We have a document called R122, which needs to be imported into a tab (also called R122) onto the master document (called GH - DAILY PICKUP). All data from the source file is to be imported to the tab in the master document. That source file will not always be saved at the same location.

Is this even possible to do, and if so, does anyone have any advice on how to do it?

We have a similar scenario with other documents, but if I can get a resolution to the above, I should be able to apply to the other documents - only difference there, is one of those documents will not be pasting the whole data over, just specific columns.



Well-known Member
Feb 6, 2012
This will search the desktop and any subfolders for as file:

Just update the file path and book name.

You'll need to add the reference library "Microsoft Scripting Runtime"

Function Recurse(sPath As String) As String

    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim mySubFolder As Folder

    Set myFolder = FSO.GetFolder(sPath)
        For Each myFile In myFolder.Files
        Debug.Print myFile.Name
            If myFile.Name = "blabla.xlsx" Then                    '''update
                'Workbooks.Open Filename:=myFile
            End If
        Next myFile
    For Each mySubFolder In myFolder.SubFolders
        For Each myFile In mySubFolder.Files
        Debug.Print myFile.Name
            If myFile.Name = "blabla.xlsx" Then                    '''update
                'Workbooks.Open Filename:=myFile
            End If
        Next myFile
    Next mySubFolder

End Function

Sub TestR()

    Call Recurse("C:\Users\andrew.marshall\Desktop\")                    '''update

End Sub
Last edited:

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...