Create year folder to export data

arpita — Wed, 24 Mar 2021 10:08:10 GMT

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

Re: Create year folder to export data

Or — Wed, 24 Mar 2021 20:44:05 GMT

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

Re: Create year folder to export data

arpita — Thu, 25 Mar 2021 08:23:13 GMT

This doesnt work

topic Create year folder to export data in QlikView

Create year folder to export data

Re: Create year folder to export data

Re: Create year folder to export data