Links to another worksheet , automate?

[phstol]

Hello,
I have developed an application (all written in Excel) that can do comparisons between up to 5 scenarios (A.... E, with A being the Master that the others are compared to. Any one can be designated “A”.
All scenarios are the same format, and all have an identical Transfer Worksheet, to enable the transfer of data from the others to A for comparison to A. So, for A the transfer worksheet becomes an Importer, wherein the user pastes-special – link into array, always B21:E35, one column at a time, for each of up to 4 scenarios B…E.
For the others, Transfer worksheet becomes an Exporter, available for the user to copy the data from each of scenarios B….E, always cells B41:B54.
Currently the application automatically prefixes the scenarios “A_scenarioname”…… “E_scenarioname”.

The application works very well, but will be confusing to a non Excel user and there is the possibility of copying one two few cells etc.

Help needed……. To automate the Transfer using Excel macros or VBA. (I do not have any VBA skills).
If the scenarios were saved in eg Documents>Scenarios, or, if the user opened A + B….E, I could imagine something like this:

In Scenario A: “Do you want to run Comparisons?” Y/N
If Y
“Enter scenarios to be compared to A”
B_Scenario #1 Rev2
D_Scenario #6
“Go”

Help would be much appreciated!!

Thanks, PHSTOL

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

[broro183]

(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

[phstol]

Hello Rob,
Thanks for your response. Ref in the same file, if I’m following you, it would be possible but unwieldy, since each scenario has 8 worksheets and to multiply that by 5……. And, ideally, the user could pick any five from a larger group.

Anyway, attached:
1) The Import array, only active in scenario A, so the user can post the cells for B…E into this array.
2) The Export array (column), only active if in scenarios B….E, so the user can copy from each scenario and paste –link one at a time into the Import array in A.
Hope this clarifies, and thanks for your help!
PHSTOL

[broro183]

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

[phstol]

Rob,
OK, thanks I will do as you request this weekend, traveling today.
Thanks for your interest
Peter

[broro183]

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

[phstol]

Hi Rob,
Thanks for the heads up on 2008 macros. I had already found that out and should have mentioned it…
Fortunately I also have access to 2003 and 2007, with 2003 being the preference because of easier access to that system.

Now, I need help with the [ALT TMR] you requested, understanding that this starts and stops the recording. But, 1) how to check the macro to see it function, and 2) how to send it to you along with the sample spreadsheet? Excel Help is bafflingly dense on this…..

Thanks for your patience!
Peter

BTW, thanks for the link explaining the 2008 macro issue.

[broro183]

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

[phstol]

Hi Rob,
Thanks again for your input and for the macro help link, they have got me going in the right direction I think (unless you have another idea).
Attached are 5 workbooks, A being the “Master”. I wrote 2 macros to illustrate what I need:
1) “Compare” copies the export columns one at a time from scenarios B…E and paste-special-Links them to the corresponding columns in A.
2) “Reset” allows the user to clear the pasted data and start again if necessary for any reason.
(Using Windows>Arrange>Vertical will allow my users to Open and display A…E Transfer Worksheets simultaneously so that the macro can function.)

To make this useable I would need 2 additional features in the Macro:
1) To be able to function if less than B…E are present, eg if only C and E are to be compared to A.
2) To be sure that each scenario is pasted into its correct slot in the Import array in A, e.g. in the above example, columns B and D must be left empty. (since I could only upload 2 files I uploaded A and B only).
Bonus Features:
3) Now the macro makes it unnecessary for the user to see either the Export or the Import cells. How can I hide these cells (or the entire worksheet) while still having the macros functional.
4) A bonus would be for the macros to work even if a workbook is opened to some other than the Transfer worksheet.
5) Also, any suggestions you might have.

Thanks!
Peter

[broro183]

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

[phstol]

Hello Rob,
OK, when you can get to it ..... I know about busy.... I'll be around this weekend.
Peter

[broro183]

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

[broro183]

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

[phstol]

Hi Rob,
Just got back to it, thanks so much for your work on this ☺.

I added some responses to your comments, hopefully will clarify. Looking forward to installing it (will need instructions how ☺).
Ref the status of the project….. it will be ready for Beta use once this Macro is working. ….
Then the issue is secure distribution. Now that I have been told an Excel version could not be locked to prevent reverse engineering while still allowing some cells for User data entry… I will use it as a tool in my consulting business until I can fund conversion to SQL or whatever to be web based. Wd appreciate your comments on this!
I attached my commented file.

[broro183]

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

[phstol]

Hi Rob,
Sorry for the delay in responding, been wrestling with some Excel files “locked for editing”. Fixed it, see my post today “Excel 2003 files locked for editing? Suspect Google Desktop”.
Thanks for getting me going on the VBA macro, and for graciously providing a tutorial. Same as probably many other visitors to the Forum that use Excel as a tool in their jobs, VBA is tempting but it’s not really feasible to become expert for a onetime use. So, I doubly appreciate the extra steps you took. Hopefully if I do need to come back to you it will be with better educated questions. ☺
Also, thanks for the link ref worksheet protection.
Best,
Peter

[broro183]

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

[phstol]

Hi Rob,
Thanks for taking another look!
I ws careless in not defining “naming cell”…. This is the cell where the operator designates if this scenario is A, or B, or….E by typing in A ….E. The letter is then concatenated to the given name, and is used to identify the scenario throughout the workbook. So, if the given name is “Scenario 0910”, and A is typed in to the “naming cell”, “Scenario 0910” becomes “A_Scenario 0910”. The A…E differentiation is necessary to avoid confusion when Comparing scenarios.
Hope this clarifies…….

Ref - does the file do what it is "meant to do"…. It is meant to link data from a range in up to 4 exporting workbooks and link the data to an array in the importing workbook, for comparison.
I think you are very close: clicking Compare>Do you want to run a comparison>Y, allows selection from a list. (these wd already be named as above). Good.
Just 2 issues, (and if this is beyond your time investment I totally understand and will set this aside until I get better skilled… )
1. A_v7.xls seems to cycle through enough get to the Import Data ws, but does not pick up the cell range B2:B16 (the first range I gave, in my bad example) or the correct range B41:B54 if I populate that), in any selected Scenario B…E.
2. The Import Data ws (which would be in Scenario A), correctly shows “Comparison of different files” A---E, with the file name, good, and this is where the imported data must land. But the in the Export Data ws, the column is headed Scenario A_, and this can’t be because A only imports and the others only export. So, it would be the selected Scenario, B…E.
So, no immediate urgency to get this done, much other work on the project. I did look at the code via VBE and I can see some threads in what you are doing, will pick at it to continue what you have done.
Have a great weekend and again, much appreciate your help.
Peter

[broro183]

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

[phstol]

Hi Rob,
Thanks so much for the additional detail and the beyond-the-call work you have done. In all honesty I had no idea that there would be so much going on to drive a VBA macro. Deep "fishing" water for me! ...... It will take some time for me to go through it and begin to figure it out .... so, I'll be doing that in the next couple of weeks.
Then hopefully I will come back and let you know it's working .
With much respect for your patience and willingness to teach!

Best, Peter