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
