topic Export to Excel in formated way in QlikView

Export to Excel in formated way

Thu, 24 Feb 2011 09:49:09 GMT

Hi All,

I am trying to export a chart and table to excel from QV, I am not able to store the formating in the exported sheets, Does anyone has any solution for this. Like in VB script code or any other code.

Regards,

Kingshuk

Export to Excel in formated way

maneshkhottcpl — Thu, 24 Feb 2011 09:52:11 GMT

Hi,

Can you share ur sample applicatin with ur requirments?

Formating can be exported in xls shhet but not in csv .

Thanks

Manesh

Export to Excel in formated way

Thu, 24 Feb 2011 18:14:56 GMT

Hi, you could try something like this:


'FIRST WILL DO A ERROR CONTROL
Sub ErrorControlBeforeExport
 on error resume next
 'CALL THE MACRO TO EXPORT
 call ExportWithFormat
 'IF ERRORS…
 if Err.Number <> 0 then
 MsgBox "Error #" & CStr(Err.Number) & " " & Err.Description, vbCritical, "Error"
 exit sub
 end if
end Sub

Sub ExportWithFormat
Set XLApp = CreateObject("Excel.Application")

Set Objeto = XLApp.Workbooks.Open("C:\PATH\FILE.xls")
XLApp.Visible = True 'Visible set as true
set XLDoc = XLApp
set table = ActiveDocument.GetSheetObject("YourObjectName")
' Copy table and paste into Excel

set XLSheet = XLDoc.Worksheets(1) 'your Excel sheet number
with XLSheet
.Range("A1:G60000").ClearContents
.Range("A1:G60000").ClearFormats
end with

'copy object
table.CopyTableToClipboard true
'paste into Excel document
 XLSheet.Paste XLSheet.Range("A1")
 'define columns format
 Set xlRange1 = XLSheet.Columns("C:C")
 xlRange1.NumberFormat = "h:mm:ss"
 Set xlRange2 = XLSheet.Columns("D:D")
 xlRange2.NumberFormat = "#,##0"
 Set xlRange3 = XLSheet.Columns("E:E")
 xlRange3.NumberFormat = "#,##0"
 Set xlRange4 = XLSheet.Columns("F:F")
 xlRange4.NumberFormat = "000000000"
 Set xlRange5 = XLSheet.Columns("G:G")
 xlRange5.NumberFormat = "dd/mm/yyyy"
 XLSheet.Cells.EntireRow.RowHeight = 15

 'assing columns width
 XLSheet.Range("A1:A60000").ColumnWidth = 11
 XLSheet.Range("B1:B60000").ColumnWidth = 49
 XLSheet.Range("C1:C60000").ColumnWidth = 7.57
 XLSheet.Range("D1:D60000").ColumnWidth = 40.57
 XLSheet.Range("E1:E60000").ColumnWidth = 11.14
 XLSheet.Range("F1:F60000").ColumnWidth = 12.14
 XLSheet.Range("G1:G60000").ColumnWidth = 9.57
 XLSheet.Range("H1:H60000").ColumnWidth = 0

 'XLSheet.Range("H1:H60000").EntireColumn.Hidden = true
 XLSheet.Range("A1:G60000").Interior.ColorIndex = xlNone

 'rename Sheet
 XLSheet.name = "SheetName"

 'call a macro in excel document (with optional parameter)
 Objeto.Application.run "MacroExcel" ,"myparam1"

 'save and close Excel
 XLDoc.SaveAs "G:\PATH\FILE.xls"
 XLDoc.ActiveWorkbook.Save
 XLApp.quit

 'message box
 MSGBOX "EXPORT COMPLETED",VbInformation
End Sub

Hope this help

Regards

Re:Export to Excel in formated way

Thu, 24 Feb 2011 23:30:20 GMT

Hi,

Thanks, This was really helpful. I have one more quick question, can we put the cell borders in bold.

Regards,

Kingshuk

Export to Excel in formated way

charlotte_qvw — Fri, 25 Feb 2011 12:18:48 GMT

I was looking to do this too and tried the macro script. however it's failing on this part -

Set XLApp = CreateObject("Excel.Application")

Any suggestions as to why that would happen/what I should change?

Thanks, Charlotte

Export to Excel in formated way

Fri, 25 Feb 2011 20:38:07 GMT

Go to Edit Module.

In 'Request Module Security' select 'System Access'.

In 'Current Local Security' select 'Allow System Access'

It need system access to create open excel.

Export to Excel in formated way

charlotte_qvw — Mon, 28 Feb 2011 23:45:48 GMT

Thanks that worked great!

Re: Export to Excel in formated way

Tue, 16 Aug 2011 16:41:49 GMT

Hello,

interessing thread!

i managed to get an excel export too.

but i am still trying.

is the attached picture the right place to fill in the "excel export macro"?

greetings

marti