Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
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