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

[#1]

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

[#2]

Question,

Example:

if you have an object in Qlikview which has only one column and u set it to "Text" in qlikview... let say one of its value is: "08E5"

A) if you exporting "08E5" to Excel, after that you use the Excel macro to format the column

B) if you format the Excel's column A to Text first, then you call the .CopyTableToClipboard 

A and B, will their result same ?

Have a nice day

[#3]

Good Afternoon Michael

I made a change in the code in macro in Excel and it worked.
Thank you for your help.

Seizing the opportunity, how to i write in the Qliqview macro a command to verify that the template.xlsm is open? If it is open, go to the next line of code VB and if not is open, open the template.xlsm and then continue to execute the code in VB editor Qliqwiew.

ISRAEL J. M. CARVALHO

[#4]

interesting...

hope this link help

http://support.microsoft.com/kb/209189

[#5]

Isreal,

You mentioned that you changed something in your macro that fixed the problem. Was this change in the QV module or an Excel macro? If it was in the QV module what did you change? Can you post your solution?

[#6]

U have to assign the range to work on, then format all coloumns u want exported as text (in my case all) and make paste special keeping the format, my code is taken from same sample as yours i'm sure u can easily modify your code

sheetRange="A5"

objExcelDoc.Sheets(sheetName).Columns("A:Z").EntireColumn.NumberFormat = "@"

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

objExcelApp.CutCopyMode = False

if (pasteMode = "image") then

objExcelDoc.Sheets(sheetName).PasteSpecial "Bitmap"

else

objExcelDoc.Sheets(sheetName).PasteSpecial -4163

end if