Export to Excel

arpita — Fri, 12 Mar 2021 17:01:38 GMT

Hi,

I need to export to Pivot tables and a Line chart to an excel file. I have used the below code. Everything is fine apart from the AGEING section.

Sub Export
Dim objShell
Dim objFolder
Dim excelFile
Dim curWorkBook

   Set objShell = CreateObject("Shell.Application")
   Set objFolder = objShell.BrowseForFolder(0, "Select the path to save the excel:", 1, "Computer")
   On Error Resume Next
      If objFolder Is Nothing Then
        Set objShell = nothing
        Set objFolder = nothing
        Exit sub
      End If
'File name
FileName = "ABC") & ".xlsx"

     'Create the Excel spreadsheet
     Set excelFile = CreateObject("Excel.Application")
     excelFile.Visible = False
     'Create the WorkBook
     Set curWorkBook = excelFile.WorkBooks.Add
     'Create the Sheet
     Set curSheet = curWorkBook.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
     curSheet.Range("A1") = chartCaption
     'Paste the rest of the chart
     curSheet.Paste curSheet.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
     curSheet.Range("J1") = chartCaption
     'Paste the rest of the chart
     curSheet.Paste curSheet.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
     curSheet.Range("J10") = chartCaption
     'Paste the rest of the chart
     curSheet.Paste curSheet.Range("J11")

     'Save the file and quit excel
     curWorkBook.SaveAs objFolder.self.path & "\" & FileName
     curWorkBook.Close
     excelFile.Quit

    Msgbox "File exported successfully!!"
     'Cleanup
     Set objShell = nothing
     Set objFolder = nothing
     Set curWorkBook = nothing
     Set excelFile = nothing
End Sub

I have attached one of the pivots and the exported excel. The AGEING formatting gets changed to DD-MM format.

Please help

Re: Export to Excel

marcus_sommer — Mon, 15 Mar 2021 14:43:46 GMT

It's an Excel feature. Excel will always interpret the content - depending on the region settings from Excel respectively the OS. AFAIK it couldn't be disabled or customized in any way.

This means you will any workaround. This could be to format the Excel area as text before copying the data or not using copy & paste as method else a writing approach (looping cell for cell through the QV chart and writing it into the Excel).

Easier than this may be to adjust your QV values, for example by prefixing the value with a single-quote which Excel treats as a local (related to the cell) string-formation. Of course this char will be visible in Qlik - therefore I use in similar cases mostly chr(8203) which is a non-length white-space.

- Marcus

Re: Export to Excel

arpita — Thu, 18 Mar 2021 13:30:28 GMT

Thanks Marcus, it worked

topic Export to Excel in QlikView

Export to Excel

Re: Export to Excel

Re: Export to Excel