Export to Excel in formated way

[#1]

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

[maneshkhottcpl]

Hi,

Can you share ur sample applicatin with ur requirments?

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

Thanks

Manesh

[#3 · Accepted solution]

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

[#4]

Hi,

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

Regards,

Kingshuk

[charlotte_qvw]

Hi

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

[#6]

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.

[charlotte_qvw]

Thanks that worked great!

[#8]

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