Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
Can you share ur sample applicatin with ur requirments?
Formating can be exported in xls shhet but not in csv .
Thanks
Manesh
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
Hi,
Thanks, This was really helpful. I have one more quick question, can we put the cell borders in bold.
Regards,
Kingshuk
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
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.
Thanks that worked great!
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