topic macro with title/caption in QlikView

macro with title/caption

Siva_Sankar — Fri, 01 Feb 2013 12:25:24 GMT

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 — Fri, 01 Feb 2013 12:35:46 GMT

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

Siva_Sankar — Fri, 01 Feb 2013 12:50:32 GMT

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

Siva_Sankar — Fri, 01 Feb 2013 14:12:56 GMT

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

Re: macro with title/caption

stevelord — Wed, 03 Jul 2013 17:03:46 GMT

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