I have some customer MS Excel VBA code (MS Excel 2007) that takes a range as input and then does some work on it (it is quite a large range). I want to use a separate excel document as the source of the range data. If I have both the source and target document open then the function works just fine. If I have only the target document open I get #Value! returned and stepping through in the debugger I see "Error 2023" in the data value passed in. Any ideas how I make this work without having to open both spreadsheets simultaneously?
views:
12answers:
1
A:
Hi.. I am not sure if I have understood the problem correctly; as you have mentioned that there are 2 excel spreadsheets viz source and target and since you would be referring both you should have both the spreadsheets open.
Are you looking for a solution to open the reference spreadsheet automatically?
Cheers... Nilesh
Nilesh Deshmukh
2010-03-02 09:45:08
Yes it seems that I am looking for a solution to open the source sheet automatically. I was assuming that Excel would handle that behind the scenes!
Dave Potts
2010-03-18 10:59:09
In that case the option would be to link the data in your excel sheet and then everytime when you open the excel sheet it will ask you if you want to update the links. On selecting Yes excel application will open the other excel sheet and then update the data in your target sheet automatically. Hope this helps.Cheers...
Nilesh Deshmukh
2010-03-29 08:10:17