Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arpita
Contributor III
Contributor III

Export to Excel

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

 

1 Solution

Accepted Solutions
marcus_sommer

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 

View solution in original post

2 Replies
marcus_sommer

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 

arpita
Contributor III
Contributor III
Author

Thanks Marcus, it worked