7 Replies Latest reply: May 15, 2014 10:08 AM by warlok RSS

    Headings in Excel

    warlok

      Hi All,

       

      I'm back again with hopefully a very simplistic question. I suspect this can not be done, but I could be wrong (and I hope I'm wrong.)

       

      I have a project that is run by the end user by executing a batch file and produces an excel spreadsheet as output. In the project, I have it auto creating a heading (which could really be anything) The end user has requested if it's possible to rather than have the heading go into the page setup area of excel (where headings normally go) to have it show as the first line of the excel spreadsheet before the filed names.

       

      I've looked and can't find anything over this but I have strongly suspected that it's not possible.

       

      If anyone has any ideas or suggestions, I'd greatly appreciate it.

        • Headings in Excel
          Data Kruncher

          Hi Warlok.

           

          First off, not to sound preachy, but practically everything is possible. Somehow.

           

          Here's how you can get new text into your page heading dynamically with Monarch v10.5.

           

          In the Table window, create a new runtime parameter calculated field. Make it Character type, and name it RT_Heading.

          Now go to the page setup dialog and change the Page Header.

          Click the 1 icon (First Value of Field), and select your new runtime parameter.

          OK the dialog.

          Hide the field from view in the Table.

          Save and close your model.

          /LIST

          Now in the batch file add a /set:RT_Heading="Sample heading text."

           

          To make the batch really interactive, so that the user is prompted for heading text for each run, consider using a Windows VBScript file instead which offers the InputBox function. Of course there are other possible solutions for that too.

           

          HTH,

          Kruncher

            • Headings in Excel
              warlok

              Thanks. I'll certainly give it a try. You've given me a couple of things to consider and possibly make some other projects work better too! And I appreciate all the help.

               

              I failed to mention I'm using Monarch Pro version 8. I don't have the 1 button in the page header area to be able to add it. But it's a good suggestion anyay. OSmething to help try and sell the company on the newest version.

                • Headings in Excel
                  Data Kruncher

                  Well that's a shame. The "First Value of Field" button was introduced with Monarch v9.

                   

                  I'll have to consider another way to handle this. The only other way that jumps to mind at the moment would be via an Excel VBA macro.

                   

                  Your batch file could have Monarch output FileA.xls (which would always be named FileA.xls). Then the batch file would launch FileB.xls. FileB.xls would have a macro that ran automatically when the file was opened. That macro would load FileA.xls and apply a custom header to the page setup and then save and close FileA.xls, as well as FileB.xls itself.

                   

                  If you're not familiar with Excel VBA, you could probably get most of the code you need for this via the macro recorder feature of Excel.

                    • Headings in Excel
                      Joe Berry

                      That's how I do it and have for some time now; however, I just use a global macro.  In my exports, I name the table something descriptive and us it in the page heading.  So for example, if I named the table Aging_By_Financial_Class, the page heading I would get from my macro would be:

                      [FONT="Courier New"]Date Printed:  mm/dd/yyyy                         Sample Client Name                                            Page # of ##

                                                                     Aging_By_Financial_Class     /B

                      /FONT 

                      /code

                      Using a macro enables me to change the fonts, size, and characteristics.  I set a hotkey for it and use it on all reports that I am presenting to the client.  My footers include my business name and website.

                        • Headings in Excel
                          warlok

                          That's cool. Something for me to play around with. I've not used VBA at all. I am a trained programmer and have worked in Pick BASIC and Visual Basic (a long time ago) From what I've heard That would work more against me than for me with VBA. I've actually been looking for a good VBA guide that would help me using Excel 2003 for other reasons. But I've been holding off since I'm pretty sure support on Office 2003 won't be on for much longer forcing upgrades. (July 12, 2011)