export file in dynamic date

[#1]

Hi Guys.

I want a macro by which i can export the data from my qlikview application with custom name and dynamic date.

For example i have straight table and start date and end date selection criteria in my dashboard.when i export it should export like customerdata_startdate_enddate.xls.

Can anyone help me in this?

[marcus_sommer]

export excel with dynamic date

[#3]

Thanks marcus for your help. I am struggling with one small error. I am trying below macro .

Sub ExcelExpwCaption

     'Set the path where the excel will be saved

     SET v = ActiveDocument.Variables("vStartDate")

varDate = v.GetContent.STRING

SET v1 = ActiveDocument.Variables("vEndDate")

varDate1 = v1.GetContent.STRING

     filePath = "H:\ExcelReport_"&varDate&"_"&varDate1&".xls"

     'Create the Excel spreadsheet

     Set excelFile = CreateObject("Excel.Application")

     excelFile.Visible = true

     'Create the WorkBook

     Set WorkBook = excelFile.WorkBooks.Add

     'Create the Sheet

     Set Sheet = WorkBook.WorkSheets(1)

     'Get the chart we want to export

     Set tableToExport = ActiveDocument.GetSheetObject("TB01")

     Set chartProperties = tableToExport.GetProperties

     tableToExport.CopyTableToClipboard true

     'Get the caption

     chartCaption = tableToExport.GetCaption.Name.v

     'MsgBox chartCaption

     'Set the first cell with the caption

     Sheet.Range("A1") = chartCaption

     'Paste the rest of the chart

     Sheet.Paste Sheet.Range("A2")

     excelFile.Visible = true

'

'     'Save the file and quit excel

'

'     WorkBook.SaveAs filePath

'

'     WorkBook.Close

'

'     excelFile.Quit

     'Cleanup

     Set WorkBook = nothing

     Set excelFile = nothing

End Sub

it export to excel but not saving the file in location. Could you please help me in this

[marcus_sommer · Accepted solution]

Make sure that you have the proper access rights to this target-folder and put your filePath into a message-box to see if your variables for v1 return the expected value and you get a valid path - there could be also a path-syntax issue if your dates are stored like MM/DD/YYYY then windows doesn't acccept slash/backslash and some other chars within the path.

- Marcus

[#5]

Hi marcus,

thanx for your help, it works now, but i have a question, it takes much time to run, first it is exporting and then naming in that new name and saving in the path. Is it possible to rename the file while exporting itself?

[marcus_sommer]

A changed order of naming the file wouldn't make a difference and the saving needs to be the last step before closing and clean everything.

One thing which could be take some time is to get the chart-data and copy them into the clipboard if it's really a large table and could only speed up this if you make this table smaller and/or by replacing text dimensions/expressions with a numeric content (ID's instead of descriptions).

Another point could be that your drive H: is a very slow (connected) network-storage - in this case you could change it to a local storage and make afterwards a manually or batch-triggered copy/move action.

To find out which part of your routine takes how many time you could implement a timekeeping, like:

dim start

start = start = now()

....

msgbox "step x: " &  FormatDateTime(now() - start, 0)

- Marcus

[#7]

everything is wrking fine in desktop version but in ajax accesspoint button or text object run macro not working, any other way can i run the macro?

[marcus_sommer]

Macros don't work within the AJAX clients - you could only use the desktop client or the IE plugin, see also: IE Plugin Vs Ajax Client

- Marcus

[#9]

i tried in IE and it shows

Macro parse failed. Funionality was lost

ExcelExpwCaption

Error: ActiveX component cant create object: 'Excel.Application'

I have changed document setting security macro override also, but everytime though i save my macro with allow system access mode it is changing to safe mode and giving me this error.

is there anything else i need to enable?

[marcus_sommer]

Yes you need to enable the system access on document level but you you need further enabling the macro-execution on the server-side (qmc/system/setup/YourServer/security) and on the user-properties in tab security.

- Marcus