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
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.
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.
--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