Hi all! Great forum! First time poster, long time viewer.
I'm fiendishly pouring through VBA books and codes - can't get enough of this stuff.
I have a few problems that I can't figure out...
I'm a commercial real estate appraiser, and I've created an Excel template with one main "inputs" sheet with all of the data inputs to the appraisal problem, and then there are several subsequent sheets that run all of the calculations. The output from the calculation sheets (named cells, ranges, charts, etc.) are all linked to a document template with lots of boilerplate and the aforementioned drop-in data/charts from Excel.
Right now, the thing is only set up for apartments [that is, my master Excel and Word templates are all apartment-oriented], but I'd like to add in functionality for all property types. I can easily re-write the Excel sheets to hide/unhide and report the outputs based on "property type" selection (industrial, apartment, office, etc.), but my problem is how to get Word to open the appropriate template.
In summary:
1) I'd like to see any ideas about a macro that will associate a pull-down list entry (property type) with a corresponding word template, open the template file, "build" the report and auto-update the merge fields and save out the built Word file as a unique name. From there, I'd make minimal edits to the Word document, but the majority of the heavy lifting would be done in Excel and Word would just be the reporting end of the thing.
I'd have local template files in my template directory, all named Industrial_Proposed, Industrial_Existing, Industrial_Condemnation, Apartment_Proposed, Apartment_Existing, Apartment_Rehab, etc. so the template names would be "static" and in a pre-defined template area. Ideally, I'd have some kind of a "dashboard" type page where a user would select a property type (pull down or radio box?) and the subsequent data inputs would all be tailored based on the selection. I'm able to do Part II of that equation, but not Part I, yet...
2) I'd like to hear any suggestions about automatically changing the "path" to the Excel file from Word. As it is, I open the Excel template, then the Word template, save both of them out to a unique job folder, then I go in and Find/Replace the old path [////pbw_dc1/vol1/data/report_resources/Exceltemplate.xls] with my built/output job file's path [////pbw_dc1/jobs/2007/XYZ_Apartment.xls], which is a pain. Any subsequent additions (fields) will have the same source path - another issue I'm having. Sometimes it posts an upper-case P in the pbw_dc1 [server name] and Word sees the two links as going to two different files, although it's just a case issue.
What I'm envisioning is basically a stored variable, being the full path to the xls file. Any additional fields pull from that pre-defined path/file.
Note: I briefly saw a similar report a national firm used, and I swear that their field codes used {docvariable "Acres" \mergeformat] to pull in the "Acres" named cell from Excel, but I don't yet understand (assuming I'm right on what I saw...) how docvariable is associated with his job's xls file. I'd assume that if the file and its path were stored as a variable that it *might* be able to pull the field using docvariable, but that seems counterintuitive to being able to use more than one data xls file... Again, I may have just seen the field code incorrectly. My field code usually looks like {Link Excel.Sheet.8 "[path]" "Field" \a \t} but his didn't use the Link Excel.Sheet.8 part.
3) I'd like to see if there's a way to list out the merge fields that are available, and/or those that are posting to the Word document. I absolutely saw this on the other guy's document, so I know it is possible. Essentially, it worked *like* Edit->Links wherein you could highlight a field and hit Edit->Links and it would highlight that path/field in the Links dialog box, but his list didn't have any of the path information - JUST the fields. Whenever he updated a field in Excel, he'd go into Word, highlight an instance of the field he'd just changed, go to his fields list and hit "apply to all field instances", instead of hunting and pecking for instances of that field throughout a 150+- page document.
Well... that wasn't very summarized. Eh, such is life. And Excel.
The other solution I saw was pretty sleek, but I think I can do better than what they had done - but there are "necessary" parts that I'm stuck on. I'm down to the last half-dozen steps to make it fully functional. Any help would be greatly appreciated!
-Hoop
I'm fiendishly pouring through VBA books and codes - can't get enough of this stuff.
I have a few problems that I can't figure out...
I'm a commercial real estate appraiser, and I've created an Excel template with one main "inputs" sheet with all of the data inputs to the appraisal problem, and then there are several subsequent sheets that run all of the calculations. The output from the calculation sheets (named cells, ranges, charts, etc.) are all linked to a document template with lots of boilerplate and the aforementioned drop-in data/charts from Excel.
Right now, the thing is only set up for apartments [that is, my master Excel and Word templates are all apartment-oriented], but I'd like to add in functionality for all property types. I can easily re-write the Excel sheets to hide/unhide and report the outputs based on "property type" selection (industrial, apartment, office, etc.), but my problem is how to get Word to open the appropriate template.
In summary:
1) I'd like to see any ideas about a macro that will associate a pull-down list entry (property type) with a corresponding word template, open the template file, "build" the report and auto-update the merge fields and save out the built Word file as a unique name. From there, I'd make minimal edits to the Word document, but the majority of the heavy lifting would be done in Excel and Word would just be the reporting end of the thing.
I'd have local template files in my template directory, all named Industrial_Proposed, Industrial_Existing, Industrial_Condemnation, Apartment_Proposed, Apartment_Existing, Apartment_Rehab, etc. so the template names would be "static" and in a pre-defined template area. Ideally, I'd have some kind of a "dashboard" type page where a user would select a property type (pull down or radio box?) and the subsequent data inputs would all be tailored based on the selection. I'm able to do Part II of that equation, but not Part I, yet...
2) I'd like to hear any suggestions about automatically changing the "path" to the Excel file from Word. As it is, I open the Excel template, then the Word template, save both of them out to a unique job folder, then I go in and Find/Replace the old path [////pbw_dc1/vol1/data/report_resources/Exceltemplate.xls] with my built/output job file's path [////pbw_dc1/jobs/2007/XYZ_Apartment.xls], which is a pain. Any subsequent additions (fields) will have the same source path - another issue I'm having. Sometimes it posts an upper-case P in the pbw_dc1 [server name] and Word sees the two links as going to two different files, although it's just a case issue.
What I'm envisioning is basically a stored variable, being the full path to the xls file. Any additional fields pull from that pre-defined path/file.
Note: I briefly saw a similar report a national firm used, and I swear that their field codes used {docvariable "Acres" \mergeformat] to pull in the "Acres" named cell from Excel, but I don't yet understand (assuming I'm right on what I saw...) how docvariable is associated with his job's xls file. I'd assume that if the file and its path were stored as a variable that it *might* be able to pull the field using docvariable, but that seems counterintuitive to being able to use more than one data xls file... Again, I may have just seen the field code incorrectly. My field code usually looks like {Link Excel.Sheet.8 "[path]" "Field" \a \t} but his didn't use the Link Excel.Sheet.8 part.
3) I'd like to see if there's a way to list out the merge fields that are available, and/or those that are posting to the Word document. I absolutely saw this on the other guy's document, so I know it is possible. Essentially, it worked *like* Edit->Links wherein you could highlight a field and hit Edit->Links and it would highlight that path/field in the Links dialog box, but his list didn't have any of the path information - JUST the fields. Whenever he updated a field in Excel, he'd go into Word, highlight an instance of the field he'd just changed, go to his fields list and hit "apply to all field instances", instead of hunting and pecking for instances of that field throughout a 150+- page document.
Well... that wasn't very summarized. Eh, such is life. And Excel.
The other solution I saw was pretty sleek, but I think I can do better than what they had done - but there are "necessary" parts that I'm stuck on. I'm down to the last half-dozen steps to make it fully functional. Any help would be greatly appreciated!
-Hoop
