Updating target workbook - extracting data from source workbook

Question

My question is as follows: I have given a workbook to multiple people. They have this workbook in a folder of their choice. The workbook name is the same for all people, but folder locations vary. Let's assume the common file name is MyData-1.xls. Now I have updated the workbook and want to give it to these people. However when they receive the new one (let's call it MyData-2.xls) I want specific parts of their data pulled from their file (MyData-1) and automatically put into the new one provided (MyData-2). The columns and cells to be copied/imported are identical for both workbooks. Let's assume I want to import cell data (values only) from MyData-1.xls, Sheet 1, cells B8 through C25 ... to ... the same location in the MyData-2.xls workbook. How can I specify in code (possibly attached to a macro driven import data now button) that I want this data brought into this new workbook. I have tried it at my own location by opening the two workbooks and using the copy/paste-special with links process. It works really well, but It seems to create a hard link between the two physical workbooks. I changed the name of the source workbook and it still worked. This makes me believe that there is a "hard link" between the tow and that this will not allow me to give the target (MyData-2.xls) workbook to others and have it find their source workbook.

Answer 1

You should use the copy/paste-special for values only:

Private Sub ImportData_Click()

  On Error GoTo OpenTheSheet

    Workbooks("MyData-1.xls").Activate
    GoTo SheetOpen

  OpenTheSheet:
    Workbooks.Open "MyData-1.xls"
    Workbooks("MyData-1.xls").Activate

  SheetOpen:
    On Error GoTo 0
    Workbooks("MyData-1.xls").Worksheets("sheetwhatever").firstRange.Copy
    Workbooks("MyData-2.xls").Worksheets("anothersheet").yourRange.PasteSpecial(xlPasteValues)

End Sub

This could be cleaned up a bit, but it's always messy to do file stuff in VBA, I'd probably put the opening code in a function. Make sure they put the new file in the same directory as the old file.

Answer 2

To clarify my understanding, each user has a spreadsheet called MyData-1.xls but with varying locations. You would like to send each person a new spreadsheet MyData-2 which will automatically pull in data from range B8:C25 in MyData-1.xls?

There are various options on doing this and below I have provided one way of doing this. In short, the user will open MyData-2, click a button, and the code will search for MyData-1 on their directory, open the workbook, grab the data, paste it into MyData-2, and then close MyData-1.

Sub UpdateWorkbook()

'Identify workbook you would like to pull data from (same for all users)
    Dim TargetWorkbook As String
    TargetWorkbook = "MyData-1"

'Get the full path of that workbook by searching in a specified directory
    Dim TargetPathName As String
    TargetPathName = GetFilePath(TargetWorkbook)

'Retrieve data in range B8:C25, copy and paste, then close workbook

    Dim TargetRng As Range
    Application.ScreenUpdating = False
    Workbooks.Open Filename:=TargetPathName
    Set TargetRng = Sheets("Sheet1").Range("B8:C25")
    TargetRng.Copy Destination:=ThisWorkbook.Worksheets(1).Range("B8:C25")
    ActiveWorkbook.Close
    Application.ScreenUpdating = True

End Sub

Function GetFilePath(TargetWkbook As String) As String

    Dim FullFilePath As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    On Error Resume Next
        With Application.FileSearch
            .NewSearch
            .LookIn = "C:\"
            .FileType = msoFileTypeExcelWorkbooks
            .SearchSubFolders = True
            .Filename = TargetWkbook
                If .Execute > 0 Then
                    FullFilePath = .FoundFiles(1)
                End If
        End With

    GetFilePath = FullFilePath

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

End Function

By way of explanation:

1. In the sub you first need to specify the name of the workbook MyData-1
2. The Function GetFilePath will then get the full path name of the workbbok. Note that I have set it to look in the "C:\" drive and you may want to amend that
3. Once we have the full file path we can easily open the workbook and copy the required range.

Note that the screenupdating is turned off to create the 'illusion' that the workbook has not been opened when the data is copied. Also, I have added a button on the worksheet of MyData-2 to trigger the code i.e. user opens workbook, presses button, and data is imported.

Finally, this code could be augmented significantly and you may want to tweak it. For example, error checking if file not found, searching in multiple directories (e.g C:\, D:)...

Hope this gets you started on the right track