How do I get chartname into save as filepath on export VB macro

[stevelord]

Hi I have this macro that works fine, I just need help with syntax to make it use the chart caption as the file name.

Sub Test

set obj = ActiveDocument.GetSheetObject("CH01")

File = "C:\users\steve\desktop\"&"Email List of Active Users "&DatePart("YYYY", Now())&DatePart("MM", Now())& DatePart("DD", Now()) & ".xlsx"

obj.Export File, ","

End sub

Basically I'd like File="C:\users\steve\desktop\"&CH01Caption&".xlsx" - I just need to know what I should actually put in the place of CH01Caption

Thanks!

-Steve

[Gysbert_Wassenaar]

See this discussion.

[stevelord · Accepted solution]

Hi Gysbert, this is close but instead of the actual Caption value it is giving me CH01 in the file name. I do believe the captiontext line you gave got me halfway there, but it was grabbing the object id instead of the object caption or title for some reason. (GetCaption looks like it should GetCaption, so I’d fault QV or VB or something and not your logic/post. ☺ I’m not sure why it’s going after the object id.)

This is what I get with the solution you provided.

CH01_20130703_122717

I’d like this:

=ClientAccountName&' Email List of Active Users '

(This is what I have as the Title and Caption on the chart object and I see it present on both of those tabs in the chart properties.)

I came to this:

Email List of Active Users_20130703_122717

I had a spontaneous thought that the “CH01” bit in the set obj = thing is what was hanging us up, and I found that if I go into the related object and change its object id to match the caption, I’m good to go so…

Step 1: go to chart properties general tab and change Object ID to match caption or give whatever text you want it to give. (Doesn’t accept dynamic =fieldname&’text’ formula stuff though. Everything is treated like text.)

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")

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

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

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

-Steve