+ Reply to Thread
Results 1 to 3 of 3

Update links in Another Workbook (without openi) by running a macro in the Active Workbook

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Update links in Another Workbook (without openi) by running a macro in the Active Workbook

    Hello,

    I am trying to update links in another workbook (without opening the workbook) on the network by running the macro in the Active Workbook (This workbook is open and has the button to run the macro).

    I found below mentioned codes, but getting a compile error:

    Sub RefreshData()
    Workbook("ANOTHER WORKBOOK.xlsx").UpdateLink Name:="\\Server\Drive\User\Folder\ANOTHER WORKBOOK.xlsx", Type:=xlExcelLinks
    mdteScheduledTime = Now + TimeSerial(0, 3, 0)
    Application.OnTime mdteScheduledTime, "RefreshData"
    End Sub

    The links which I am trying to update are Cell to Cell links (these links are not related to Active Workbook)

    Kindly help me correct it.

    Thanks loads.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Update links in Another Workbook (without openi) by running a macro in the Active Work

    You can't use the updatelink method on a closed workbook, you need to open it first.

    Why do you need to update the links without opening the workbook? It maybe easier to find a workaround to this than to update the links without opening the workbook (I'm not even sure it is possible).

  3. #3
    Registered User
    Join Date
    06-11-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Update links in Another Workbook (without openi) by running a macro in the Active Work

    Hi,

    Thanks for your reply.

    Yes, I concluded the same after 4 hours of crawling on Google.

    This is what I have structured:

    I have 10 remote sites inputting the data in 10 different excels. This excels are imported / updated to my local sever using Google Drive! I know its a cheap / unreliable option as I cant afford a proper server right now with a VPN. Having this option I decided to have another layer of backup on my local drive which imports the data of these 10 excel files into one using the links, but know as 10 different databases are imported into one, the file becomes big and slow and also increases the chances of data corruption. So I wanted to have another file which will be used to query the backup database without opening the backup file.

    Now I believe I am left with only 2 options one I use the same backup file (over the network) for querying the database or I put VB code to open the backup file, update links and close it.

    Any suggestions?

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1