4 Replies Latest reply: Jul 3, 2013 1:19 PM by Steve Lord RSS

    macro with title/caption

    sivasankar kanagasabai

      hi, i have a macro to export a straight table to excel. sub expexcel set obj = ActiveDocument.GetSheetObject("CH07")  obj.SendToExcel    end Sub This exports to excel perfectly but the opened excel is with the name 'CH02_20130201_175054'  (chart object with timestamp) i want this name should be my chart object title(caption) or with chart object title_time stamp. and is it possible to do this without saving the file to a location? how can i achive this? Thanks for the help

        • Re: macro with title/caption
          Gysbert Wassenaar

          See here for a macro that exports to excel and renames the sheet name and saves with a specified file name.

           

          set obj = ActiveDocument.GetSheetObject("CH01")

          captiontext = obj.GetCaption.Name.v

           

          strSafeDate = DatePart("yyyy",Date()) & Right("0"  & DatePart("m",Date()), 2) & Right("0"  & DatePart("d",Date()), 2)

          strSafeTime = Right("0" & Hour(Now()), 2) & Right("0" & Minute(Now()), 2) & Right("0" & Second(Now()), 2)

          strDateTime = strSafeDate & strSafeTime

           

          filename = captiontext & "_" & strDateTime

            • Re: macro with title/caption
              sivasankar kanagasabai

              But here i have to give the name for an excel file and save it. My need is it should open automatically as mentioned  my code mentioned above does. the only need is it uses a object id with date format. i want to have my title caption used in the chart object.

                • Re: macro with title/caption
                  sivasankar kanagasabai

                  Hi, any help? just need the caption and title to be arrived in excel using the macro. Thanks.

                    • Re: macro with title/caption
                      Steve Lord

                      Hey I actually had the same issue and Gysbert pointed me here.  I ultimately figured out that you need to go to the chart/table object properties and change the object id itself to what you want.  The object ID field is next to the title field on the general tab and has CH01 or TB01 etc.  You can change that CH01 to Email List Report or whatever you want, but it treats everything as pure text so you'd have to append other things like date to it in the macro.

                       

                      ---excerpt from thread I posted---

                       

                      Step 1: go to chart or table properties general tab and change Object ID to match caption or give whatever text you want it to give. e.g. Email List of Active Users


                      Then Step 2: use this script and make the part in “” on set obj match the object id you gave the object.

                      sub ExportEmailButton
                      set obj = ActiveDocument.GetSheetObject("Email List of Active Users") <-match this to your object id
                      captiontext = obj.GetCaption.Name.v
                      strSafeDate = DatePart("yyyy",Date()) & Right("0" & DatePart("m",Date()), 2) & Right("0"  & DatePart("d",Date()), 2)
                      strSafeTime = Right("0" & Hour(Now()), 2) & Right("0" & Minute(Now()), 2) & Right("0" & Second(Now()), 2)
                      strDateTime = strSafeDate & strSafeTime
                      filename = captiontext&"_"&strDateTime
                      obj.SendToExcel
                      end Sub

                       

                      Result is a file opens in Excel with file name: Email List of Active Users_20130703_122730

                       

                      Probably okay to add an &.xlsx or &.xls to the end of the filename if you have such a need.  (I saw someone else with that need.)

                       

                      Other thing that seems to be needed is to match the first line sub name to the button name and macro name.  I had difficulty making the macro work outside the test mode until I synchronized the sub, macro name, and button label.  Then they worked when I pushed the button object as well as when I pushed test.

                       

                      Other comments:

                      --I couldn’t get a dynamic caption formula to work in the object id script.  I originally had =ClientAccountName&’ Email List of Active Users’ but had to drop the =ClientAccountName& part.  I will explore other methods for that later.

                      --The one I originally posted saved to a particular folder and I suspect I could stick that filepath code in above, but I actually like opening it first since I have to rerun the script and save to a multitude of folders for the actual task I was working on.

                      --I could make a script for each client folder and one-up that by tracking down the email hyperlink vb script, but happy to get the job done for now. J

                       

                      Thanks Gysbert for the getcaption syntax that pulls the object id for whatever reason.  I was able to fudge the object id to something helpful at least. J

                       

                      -Steve