Links to another worksheet , automate?

(would an attachment , the Tranfer Worksheet , clarify?)

Yes, it would help. An attachment provides context & will help us help you much more effectively.

Are the scenarios saved in different files?
If so, would it be acceptable to have the different scenarios as different sheets in the same file?

Rob

hi,

Can you please upload a sample workbook with the layout & some dummy data?
- Pictures are nice to look at but don't really help us help you (without having to do a lot of unnecessary guessing/rework to create a file).

Also, can you please record a macro of your actions when you work through this task manually (use [alt + T + M + R] to start & stop recording) & include this recorded macro in your sample file when you upload it?

Rob

hi Peter,

There may be a slight problem...

I've just noticed that your profile states you are using Excel Mac 2008 - this version of Excel doesn't support VBA. Is it the only version of Excel you can use?

Here's just one of many articles about the lack of vba support:
http://www.schwieb.com/blog/2006/08/...-visual-basic/

Excel forum helpers may still be able to make some suggestions around workbook design that will make things easier for you, but if you are limited to Mac 2008 we may not be of much help in automating the transfer.
(I'm not a Mac user, so someone else may need to step...)


Rob

hi Peter,

I'd suggest saving it in the Excel 97-2003 xls format because there are a few helpers who don't use 2007.


When you start recording a macro in your sample file & the first popup appears, choose to Store Macro in "This Workbook" then start recording & stop once complete (see http://www.mvps.org/dmcritchie/excel/getstarted.htm). 2) By storing it in this workbook the macro will be available for helpers when you upload the file.
1)To review the recorded code, press [alt + F11] (opens the Visual Basic Editor/VBE), press [ctrl + R] (shows the Project Window which may already be visible), expand the "Project" & the Modules folder for the file & your recorded code will be a sub item (eg double click on Module1 to view it). On a copy of your file, you can then arrange the VBE window so that you can see the Excel window at the same time, put the cursor within the recorded "Sub" & press [F8] to go through each line of code at a time.
However, your recorded code is likely to need some tidying up before it can be flexible & robust enough for regular re-use - & that's where Forum Helpers play a part

hth
Rob

Hi Peter,

I'm sorry I haven't had a chance to look at this today - I'll try tomorrow. Hopefully another helper can help before then...

regarding #3 & #4 (w/o looking at your files), remove any ".select" or ".activate" lines from your code by merging multiple lines together to explicitly the entire object variable eg from having multiple lines you can merge them to get...

Please Login or Register  to view this content.

hth
Rob

hi Peter,

I think I have covered everything in the attached file... My thoughts are that this single file upload becomes the template file (ie the base file that all possible comparison files are built from) & is renamed/"saved as" when it is populated with the data of specific scenarios.

However, I've used some "bad/poor programming" because I've changed a loop counter within one of the loops - I can't easily figure out a better way around it though, so I'm going to try asking others in the linked thread: http://www.excelforum.com/excel-prog...in-a-loop.html

hth
Rob

hi Peter,

I've had some feedback from some of the best on the Forum in my linked thread & have modified the code in the new version to prevent the loop counter being changed (+ some other small changes). I've commented the code reasonably thoroughly so hopefully it is clear but feel free to ask if I have overlooked something or you have further questions.

Please let me know how your testing/development is going...

hth
Rob

hi Peter,

Please bear with me here - I do want to help you learn but...

Thanks for your honesty that this is going to be a commercial product, with that in mind + my preference of "teaching a man to fish..." + me just doing this for the sense of challenge*, I'm going to leave more of the work to you than I have so far. I've made a couple of changes to the file (now v7), you have a go & see what you can do...

If you have specific questions with problems that you can't resolve, please let us know.

*I've added more error checking than some others would when posting examples to the Forum, but I would not consider this code robust enough to be distributed as a finished product - that is paid for. You need to put your thinking cap on & identify all things that could go wrong & mitigate/eradicate them - ask for help with specific challenges. Most of the volunteers are more interested in helping people learn or do things more effectively than just doing the job for you.
Alternatively, if you want to hand the job over completely it may be worth checking out the site's link to "Consultants".

I don't know enough to suggest or comment on the best alternative to Excel but... I have to agree that Excel is not a secure platform - John Walkenbach's below link does a pretty good job at explaining the limitations: http://spreadsheetpage.com/index.php...otection_faq1/

Rob

hi Peter,

Good as gold - I think you're being over generous with your use of the "gracious" phrase - I'd had a long day at work & I let it show .

I'm not an expert either (just further along the continuum than you) and learning through the experience of trying this & that and absorbing a lot of info from this Forum & the web in general.
"better educated questions. ☺" come with advice or experience & you are gaining experience each day. Now, I'll try to be more gracious with some advice

...
Blimey!
I wrote the above & then glanced in the file to see what else I could suggest (in terms of robustness) & it took me a few minutes to get in the groove of what the file is "meant to do"!
Then I had a bit of a blank - all I can think of at the moment is:
- to add in flexibility regarding the size of the range to link to (perhaps used a named range, instead of hardcoding the cell addresses into the macro).
- on your journey to "better educated questions", try to be clearer/consistent in your explanations (I'm still a bit confused by your recent reference to the "naming cell").
- does the file do what it is "meant to do" (if not, see above)?

I'd like to help you get this sorted so you can mark the post as Solved...
(in saying that, I'm not sure how much I'll be able to help during this weekend)

hth
Rob

hi Peter,

This is more time commitment than just giving you the answer but are you ready to learn to fish...?

#1: Sorry I'm not sure where I got that cell range from. Change this:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Manually create the links to the correct range for one file ie "=...Export Data'!$B41", now change the cell reference style to R1C1 via Tools - Options - ?(I can't remember the tab) within Excel, note down the equation which will look something like "...Export Data'!R[?]C2" & change this in the EqtnStr line of the above code.
Change the cell reference style back by unticking the "R1C1" option.
Next run the macro, it will run as normal until it gets to the first "Stop" line & then it will open the VBE & highlight this line yellow.
Press [ctrl + G] to open the Immediate pane within the VBE, press [F8] to step through the code "line by line" until the next Stop line. You can check that the string in the Immediate pane & that the formula in the spreadsheet is linking to the right range.
If it's not, try again... I'd do this by copying the "EqtnStr = " line into the Immediate pane, changing it, have the cursor on the line & press [enter] to "execute" the line of code which will change the contents of the Eqtnstr variable.
Copy the ".Range(.Cells(5, ColToUpdate),...= Eqtnstr" line into the Immediate pane, have the cursor on the line & press [enter]. Check the spreadsheet & see if it's correct - repeat as needed using the Immediate pane
Press [F5] to let the macro run by itself again, it will loop through the actions until it gest back to "Insert Links:" & the Stop line. If you want you can check again with this next file, or you can delete/comment out the stop lines & let the macro run.

#2: This is related to the missing "naming cell"...

in the Export Data ws, the column is headed Scenario A_,...

To be honest, it is just a matter of changing/linking the text to the "naming cell", there is no impact on how the macro will handle it.

and this can’t be because A only imports and the others only export.

If the file is "named as A", the export sheet becomes redundant to an extent but is still there because it is part of the standard template design & links to column b of the import data sheet. There are 2 possible approaches here:
- incorporate an If statement into the formulae on the Export Data sheet so that no data is returned if it is the "A" file.
- Add the below code immediately above the "ExitHandler:" label

Please Login or Register  to view this content.

NB: you may need to make some adjustment in the Thisworkbook module to take appropriate action to unhide the Export data sheet when the "A" file is saved/closed - have a think, see if you can solve it &/or ask.

So, it would be the selected Scenario, B…E.

No worries, delete/hide the column & change the title as needed. The macro works by choosing the appropriate column by searching the title row for the right scenario prefix (within the "IdHdrColumn" function).


If you have any more questions or once you've got this sorted, can you please upload the latest version of the file (make sure it includes a naming cell!)?

... how's the fishing, have you caught anything yet?

hth
Rob