Jump to content


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.

Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.


Macros linking Excel spreadsheets

  • Please log in to reply
1 reply to this topic

#1 garnetwr


  • Members
  • 44 posts
  • Local time:10:58 AM

Posted 25 July 2005 - 10:32 PM

I have created two separate spreadsheets that serve two different purposes; however, I sometimes need to view information on one of them after looking at the other. Both files are worksheet protected.

I recorded the following macro to access the one from the other:

Workbooks.Open Filename:="DRIVE:\Path To File\Filename.xls"

The problem I have with this is it assumes the file is not already open.

What do I add to the macro to counter the screen-pop I receive when the called file is already open?

BC AdBot (Login to Remove)


#2 tos226



  • Members
  • 1,577 posts
  • Gender:Female
  • Location:LocalHost
  • Local time:11:58 AM

Posted 25 July 2005 - 11:36 PM

One way to check whether a workbook is already open is to check if it is in the list of workbooks :thumbsup: . You can't record such check. Find your macro and just add the code to do it for you.
For instance, try to set workbk=workbooks("filename"), and then if you can't create the workbk object, the object will be NOTHING, otherwise the file is already open. Read Help on the VBA side about the workbook object and the command SET.

Why do you need a macro? It seems to be complicating your life.
Perhaps you could setup a link, and then excel would open the "other" file for you. It normally works quite well, unless of course, you change the location or delete linked files or otherwise confuse yourself or excel. Read up in Excel Help about links to external workbooks.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users