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: 
Not applicable

Exported to excel, Leading zeroes (in text format) was dropped.

Hi,

When loading data, i already forced the field to be in text data type. The field is used in dimension.

My problem is when i tried to export the table to excel through the module, the zero was dropped. I even tried to change the field dimension's Text Format to =Text(field), but it cant works.

I checked the (Settings -> Document Properties -> Tables -> Fields).the field is $ascii, $text.

Please advise.

'// ****************************************************************

'// copyObjectsToExcel

'// ~~

'// Parameters:

'//                    qvDoc - Reference to the QlikView document (normally just use

'//                                        "ActiveDocument", but you can also use copyObjectsToExcel

'//                                        outside of QlikView ...

'//                    aryExportDefinition - array of settings

'// ~~

'// Version 1.02

'// ~~

'// The aryExportDefinition is used to pass the following properties to

'// copyObjectsToExcelSheet:

'//

'//   Index                    Description

'// ------------------------

'//           0          -           Id of the QlikView object to copy from

'//           1          -           Name of the sheet (in Excel) where the object should be copied to

'//

'//                                        (If a sheet with the same name already exists no new

'//                               sheet will be created, instead the existing sheet will

'//                                        be used for pasting the object)

'//

'//                                        Note: the sheetName can be max 31 characters long

'//

'//                    2          -           Range in Excel where the object should be pasted to

'//                    3          -           PasteMode ["data", "image"]

'//                                        Defines if the objects underlaying data should be

'//                                        pasted ("data") or the the image representing the object

'//                                        should be used

'// ****************************************************************

Private Function copyObjectsToExcelSheet(qvDoc, aryExportDefinition) 'as Excel.Workbook

Dim i 'as Integer

Dim objExcelApp 'as Excel.Application

Dim objExcelDoc 'as Excel.Workbook

Set objExcelApp = CreateObject("Excel.Application")

objExcelApp.Visible = false 'false if you want to hide Excel

objExcelApp.DisplayAlerts = false

  

'Set objExcelDoc = objExcelApp.Workbooks.Add

 

Dim strSourceObject

Dim qvObjectId 'as String

Dim sheetName

Dim sheetRange

Dim pasteMode

Dim objSource

Dim objCurrentSheet

Dim objExcelSheet

for i = 0 to UBOUND(aryExportDefinition)

          '// Get the properties of the exportDefinition array

          qvObjectId = aryExportDefinition(i,0)

          sheetName = aryExportDefinition(i,1)

          sheetRange = aryExportDefinition(i,2)

          pasteMode = aryExportDefinition(i,3)

               

          Set objExcelSheet = Excel_GetSheetByName(objExcelDoc, sheetName)

          if (objExcelSheet is nothing) then

                    Set objExcelSheet = Excel_AddSheet(objExcelApp, sheetName)

                    if (objExcelSheet is nothing) then

                              msgbox("No sheet could be created, this should not occur!!!")

                    end if

          end if

               

    objExcelSheet.Select           

          set objSource = qvDoc.GetSheetObject(qvObjectId)

          Call objSource.GetSheet().Activate()

          objSource.Maximize

          'qvDoc.GetApplication.WaitForIdle

 

 

          if (not objSource is nothing) then

 

                    if (pasteMode = "image") then

                              Call objSource.CopyBitmapToClipboard()

                    else

                              Call objSource.CopyTableToClipboard(true) '// default & fallback

                    end if

 

                    Set objCurrentSheet = objExcelDoc.Sheets(sheetName)

                    objExcelDoc.Sheets(sheetName).Range(sheetRange).Select

                    objExcelDoc.Sheets(sheetName).PasteSpecial 'XLSheet.Range(aryExportDefinition(i,2))

                    'DataType=wdPasteBitmap

                    'Paste:=xlPasteValuesAndNumberFormats

 

                    if (pasteMode <> "image") then

                    With objExcelApp.Selection

            .WrapText = False

            .ShrinkToFit = False

                    End With                    

                    end if       

 

                    objCurrentSheet.Range("A1").Select   

          end if

              

              

next   

Call Excel_DeleteBlankSheets(objExcelDoc)

'// Finally select the first sheet

objExcelDoc.Sheets(1).Select

'// Return value

Set copyObjectsToExcelSheet = objExcelDoc

end function

14 Replies
Not applicable
Author

it has to do with CopyToClipboard which caused the leading zeros dropped.

Anyone has idea how to prevent it happened.

i dont wan to have extra step in excel @ VBA

Not applicable
Author

I live in Brazil and do not have English language fluently. Forgive me for spelling errors.
     I'm having the same problem. When exporting a graph (simple table) to excel using macro, all the dimensions that were parameterized as text are exported as general format. Now if I use the icon "export to excel" in the menu bar graph, the data dimension are exported as text correctly. The problem is when I use a macro to export.

     Can anyone help me?

thank you very much

ISRAEL J. M. CARVALHO

Not applicable
Author

can we view the code of your macro?

Not applicable
Author

Sub Excel_Table_Export


set Obj1  = ActiveDocument.GetSheetObject("CH03") 

set XLApp = CreateObject("Excel.Application")

set XLDOC = XLApp.Workbooks.Open ("W:\1 - Áreas\Controladoria\Custos\israel\QlikView\Macro Análise Despesas.xlsm")

XLApp.Visible = True

XLApp.WorkSheets("SUP0710").Activate

Obj1.CopyTableToClipboard true

XLApp.Sheets("SUP0710_II").select

XLApp.Sheets("SUP0710_II").cells(1,1).pastespecial xlpastevalues

end sub

Not applicable
Author

Using the CopyTableToClipboard method will make you lose the format.

I think now, you have only 2 ways to retain your format...

1. Continue to use the .CopyTableToClipboard method  BUT with pre-formatting your excel file. In this case, you should know the format in every column.

2. use the  .SendToExcel ; the drawback is you can't modify the worksheet name, and you can only export one qlikview object to the excel.


Not applicable
Author

I Already formatted cells from of excel to text (Cells.NumberFormat = "@") before running the macro Qlikview. But then when I run the Qlikview macro, the cells format is lost returning as general format, losing 0 to left

Not applicable
Author

format the EXCEL worksheet... not the column in qlikview; means u need to use a template.

example... u create a template.xlsx then format the file ( depends your data on each columns)

Then in Qlikview Macro , you try to open the template.xlsx  and then call method to export the object to the template.xlsx

Not applicable
Author

This is exactly what I'm doing.
Already have an excel file that is used to several months.
This file already has all columns in text format.

When you open this excel file, automatically runs a macro in excel to make formatting text as the command below (Workbook_Open Event)

Private Sub Workbook_Open ()
    Sheets ("SUP0710_II"). Select
    Cells.NumberFormat = "@"
end Sub

Besides excel already be formatted as text, columns Qlikview are also formatted as text.

Not applicable
Author

Hello Michael

By analyzing the file, I realized that the problem is in the format of dimensions.
The format of the expressions is exported correctly obeying the format of Qlikview.

In the properties of the object, it is possible to set only the format of Expressions? There is this possibility for the dimensions?