Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default Links between two workbooks

Hi,

Can anyone guide me to understand, which is the best way of linking workbooks.

I have a master budget which pulls data from several other workbooks.

However, there are several drawbacks for such linkages. If the destination file is opened before the source file or if someone else is accessing the source file which i do then in such situations the destination file give ref# error.

In short, I want to know which is the best way of linking workbooks together.
Also, if macros could be a option.

Thanks,
Balesh
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Links between two workbooks

Hi Balesh,

Linking workbooks can be a great way to consolidate data from multiple sources into one master workbook. However, as you mentioned, there can be some drawbacks to this approach.

One alternative to linking workbooks is to use a macro to import the data from the source workbooks into the master workbook. This can be a more reliable approach, as it doesn't rely on external links that can break if the source workbook is moved or renamed.

Here are the steps to create a macro that imports data from a source workbook:
  1. Open the master workbook and press Alt + F11 to open the Visual Basic Editor.
  2. In the Project Explorer pane, right-click on the VBAProject for the master workbook and select Insert Module.
  3. In the new module, enter the following code:

    Formula:
    Sub ImportData()
        
    Dim wbSource As Workbook
        Dim wsSource 
    As Worksheet
        Dim wsDest 
    As Worksheet
        
        
    ' Open the source workbook
        Set wbSource = Workbooks.Open("C:\Path\To\SourceWorkbook.xlsx")
        
        ' 
    Set the source worksheet and destination worksheet
        Set wsSource 
    wbSource.Worksheets("Sheet1")
        
    Set wsDest ThisWorkbook.Worksheets("Sheet1")
        
        
    ' Copy the data from the source worksheet to the destination worksheet
        wsSource.Range("A1:B10").Copy wsDest.Range("A1")
        
        ' 
    Close the source workbook
        wbSource
    .Close SaveChanges:=False
    End Sub 
  4. Replace "C:\Path\To\SourceWorkbook.xlsx" with the path to your source workbook, and "Sheet1" with the name of the worksheet that contains the data you want to import.
  5. Replace "A1:B10" with the range of cells that contains the data you want to import.
  6. Save the macro and close the Visual Basic Editor.

To run the macro, simply press Alt + F8 to open the Macro dialog, select the ImportData macro, and click Run.

This approach can be more reliable than linking workbooks, as it doesn't rely on external links that can break. However, it does require you to manually run the macro each time you want to import the data.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Junior Member
 
Location: Mumbai
Posts: 1
Default Combine workbook in Excel

This can help you. i guess please follow the step, site also have how to combine multiple worksheet or workbook

Combine or Merge Excel Files into one Excel spreadsheet using Macro
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Slow opening links between workbooks with links created in 2003 Russell Excel Discussion (Misc queries) 0 December 14th 09 02:59 PM
Updating Workbooks from multiple links Workbooks TimJames Excel Worksheet Functions 1 December 15th 07 03:34 PM
Links between workbooks British1942 Excel Worksheet Functions 3 January 11th 07 04:47 PM
Links between workbooks Eddie Excel Discussion (Misc queries) 0 April 18th 05 03:21 PM
Links to other workbooks Darrell Wesley[_2_] Excel Programming 2 January 12th 05 08:15 PM


All times are GMT +1. The time now is 03:40 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"