Creating a Macro to import data from other Excel document

[MancmMonkee]

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.

Thanks

 

[mrshl9898]

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