After Exporting to Excel by using VBScript Number format missing

Nagaraj6 — Wed, 22 Nov 2023 07:21:52 GMT

Hi Guys,

I need a help

I have added below code in my script but not working.Please any suggest me.PFA App.

oSH.Range("D" & num_rows).NumberFormat = "0,000"

oSH.Range("D" & num_rows).NumberFormat = "#,###"

Below my script:

sub exportToExcel

'==============================================================
' File Path & Name

Path = "E:\QlikView\New_MACRO_REPORT\"
FileName = "New"
strSaveFile = Path & FileName
'==============================================================
'Open Excel

set oXL=CreateObject("Excel.Application")
oXL.visible=True
oXL.Workbooks.Add

aSheetObj=Array("CH01","CH02","CH03") ' Chart ID's here

'==============================================================
for i=0 to UBound(aSheetObj)
Set oSH = oXL.ActiveSheet

num_rows = oSH.UsedRange.Rows.Count

If num_rows = 1 then
oSH.Range("A2").Select
Else
oSH.Range("A" & num_rows+4).Select
End If

Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing

oSH.Cells.Select

oSH.Columns("A").ColumnWidth = 12.17
oSH.Columns("B").ColumnWidth = 12.17
'oSH.Columns("D").ColumnWidth = 12.17
If num_rows = 1 then
oSH.Range("A" & num_rows).Value = sCaption
oSH.Range("A" & num_rows).Font.Bold = True
'MsgBox "Before Number format"
'oSH.Range("D" & num_rows).NumberFormat = "0,000"
'MsgBox "After Number format"
oSH.Range("A" & num_rows).Font.ColorIndex = 3

Else

oSH.Range("A" & num_rows+3).Value = sCaption
oSH.Range("A" & num_rows+3).Font.Bold = True
oSH.Range("A" & num_rows+3).Font.ColorIndex = 3
oSH.Range("A" &num_rows+3).Interior.ColorIndex = 40
oSH.Range("D" & num_rows+3).NumberFormat = "0"
End If
'oSH.Range("D" & num_rows).NumberFormat = "#,##0"
'oXL.Selection.Columns.AutoFit

next
'==============================================================
oSH.Range("A1").Select
oXL.Sheets("Sheet2").Delete
oXL.Sheets("Sheet3").Delete
oSH.Name = "Data"

oXL.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"

set oSH = Nothing
set oXL=Nothing
end sub

Re: After Exporting to Excel by using VBScript Number format missing

marcus_sommer — Wed, 22 Nov 2023 15:41:32 GMT

Just a few hints which may be helpful:

formatting the area before pasting the values
use of pastespecial instead of paste
converting the content after the paste - because if it's applied as string you couldn't format it in any way else at first it needs to become a numeric value - quite simple would be to multiply each value with 1 and format then the result

topic After Exporting to Excel by using VBScript Number format missing in Qlik Automate

After Exporting to Excel by using VBScript Number format missing

Re: After Exporting to Excel by using VBScript Number format missing