Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
can we view the code of your macro?
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
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.
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
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
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.
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?