PDA

View Full Version : Solved: VBA issue with named ranges across workbooks



jrwilmot
05-28-2009, 05:01 PM
Hi, I'm a self-taught VBA junkie and have run in to a problem I don't understand. Someone may be able to give me a general explanation of a solution instead of a code example while I put together a specific example of my problem. I am writing a macro at work that is too long to use as an example here and cannot post anything at a forum while at work for security reasons.

I use XP at work but and we save all files down to 2003 versions.

I have 4 files that need to work together, one of them I cannot change much except an input screen, one of them holds all the macros, one is an output file created by my macro file based on the the first file, and the fourth is a recreation of the third in a different format.

I want a user to be able to type into the macro file the path and file name and then my macros use those fields where needed to run my macros.

So I named the ranges and set the values from those fields as the variables and copied the Dim MyInputFileName as Str, MyInputFileName = range("RangeForInputFileName").Value into the beginning of each macro. I did this for all four file names and the path. So far so good....but my macros have to switch between files so much I'm not sure how to get them to work together without naming the specific files in VBA. This doesn't work if I want to share this macro witout manually setting everything up for each group and also limits the users to only running this macro one way but they might want 2 different versions.

When I have a macro that is running on either of the output files it doesn't recognize the range from the macro file. I spent half my day looking at websites like ozgrid and could not find an example of this problem.

My original macro had the line:
Windows("SpecificClientInputFileName.xls").Activate

now I have:
Windows(MyInputFileName & ".xls").Activate

This new syntax only works if the macro file, which is where the ranges to find the values for the variable are at, is the current active file, I cannot switch from the input file to the output or even back to the macro file.

Can anyone help me with a way to refer to a range that is in a different workbook?

I think I have been working on this project too long since I can no longer tell if the solution is one of those "well, duh!" moments or if it's impossible. Since I don't have any formal computer training there are times the language baffles me.

mikerickson
05-28-2009, 07:21 PM
You could use a veryHidden worksheet to store the Range names and their full address (e.g. [Workbook1.xls]Sheet1!A1:J14). You could then write VBA function(s) to read those names/addresses. Your macros would use functions that return Ranges rather than range arguments.

jrwilmot
06-07-2009, 04:28 PM
Thanks for the suggestion. I think I have to play with a veryHidden sheet some more to get this to work right. But all coding takes a backseat til month end is over.