Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
arpita
Contributor III
Contributor III

Create year folder to export data

Hi,

I need to export to Pivot tables and a Line chart to an excel file. I have used the below code. In this case, it  saves directly to \\Desktop\TLM\QlikView_TLM_Output\. But I also want it to creat the current Year and Month and then save the file

For eg: It saves in \\Desktop\TLM\QlikView_TLM_Output\2021\March wherein the year (2021) and month(March) is created dynamically so that it changes with change in year and month

sub Export

Path = "\\Desktop\TLM\QlikView_TLM_Output\"

FileName = "TLM-CCY's Daily metrics_" & ActiveDocument.Evaluate("Date(Now(), 'DD-MM-YYYY hhmmss')") & ".xlsx"

'Create the Excel spreadsheet
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False
'Create the WorkBook
Set XLDoc = XLApp.WorkBooks.Add
'Create the Sheet
Set XLSheet = XLDoc.WorkSheets(1)

'Get the chart we want to export
Set tableToExport = ActiveDocument.GetSheetObject("Break Volume")
Set chartProperties = tableToExport.GetProperties
tableToExport.CopyTableToClipboard true

'Get the caption
chartCaption = tableToExport.GetCaption.Name.v
'MsgBox chartCaption

'Set the first cell with the caption
XLSheet.Range("A1") = chartCaption
'Paste the rest of the chart
XLSheet.Paste XLSheet.Range("A2")




'Get the chart we want to export
Set tableToExport = ActiveDocument.GetSheetObject("Break Scenerio")
Set chartProperties = tableToExport.GetProperties
tableToExport.CopyTableToClipboard true

'Get the caption
chartCaption = tableToExport.GetCaption.Name.v
'MsgBox chartCaption

'Set the first cell with the caption
XLSheet.Range("J1") = chartCaption
'Paste the rest of the chart
XLSheet.Paste XLSheet.Range("J2")




'Get the chart we want to export
Set tableToExport = ActiveDocument.GetSheetObject("Trend Analysis")
Set chartProperties = tableToExport.GetProperties
tableToExport.CopyBitmapToClipboard()


'Get the caption
chartCaption = tableToExport.GetCaption.Name.v
'MsgBox chartCaption

'Set the first cell with the caption
XLSheet.Range("J15") = chartCaption
'Paste the rest of the chart
XLSheet.Paste XLSheet.Range("J16")


Set XLSheet = XLDoc.WorkSheets(2)
'WorkSheets(2).Name = "RawData"
'Get the chart we want to export
Set tableToExport = ActiveDocument.GetSheetObject("Raw Data")
Set chartProperties = tableToExport.GetProperties
tableToExport.CopyTABLEToClipboard TRUE


'Get the caption
chartCaption = tableToExport.GetCaption.Name.v
'MsgBox chartCaption

'Set the first cell with the caption
XLSheet.Range("A1") = chartCaption
'Paste the rest of the chart
XLSheet.Paste XLSheet.Range("A2")

'Save the file and quit excel
XLDoc.SaveAs Path & FileName

XLDoc.Close
XLApp.Quit

Msgbox "File exported successfully!!"
'Cleanup

Set XLDoc = nothing
Set XLApp = nothing
End Sub

 

2 Replies
Or
Master
Master

Perhaps something along the lines of this formula would work? I don't work with macros much, so the syntax may not be perfect, but assuming the one you posted works I think should too...

Path = "\\Desktop\TLM\QlikView_TLM_Output\" & ActiveDocument.Evaluate("Year(Now())") & "\" & ActiveDocument.Evaluate("Month(Now())")

arpita
Contributor III
Contributor III
Author

This doesnt work