VBScript Exporting string as number

chris_johnson — Tue, 25 Mar 2014 11:54:49 GMT

Hi,

I am trying to export a table from a QlikView document but one of the columns has a field value "DEC08" within it. When pasting the table to Excel from within VBScript it is converting this value into an integer which Excel is then formatting as a date.

I do not want this to be converted into a number as it is a Document Number and not a date. Does anyone know if there is a way to stop this happening?

This is the part of the VBScript I'm trying to fix:

'Create the files

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = false 'you can make it false, if you want to make it in the background

set XLDoc = XLApp.Workbooks.Add

XLDoc.Sheets(1).name = "SB_Export"

set XLSheet = XLDoc.Worksheets(1)

set MyTable = ActiveDocument.GetSheetObject("CH147")

SET p = MyTable.GetProperties

set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted

'XLSheet.Name="Export"

Mytable.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range("A1")

XLSheet.Range("1:1").Delete

'you can remove another line if you have subtotal that you want to remove

'format required columns

XLDoc.WorkSheets(1).Columns("A:A").EntireColumn.NumberFormat = "000000"

XLDoc.WorkSheets(1).Columns("E:E").EntireColumn.NumberFormat = "@"

XLDoc.WorkSheets(1).Columns("I:I").EntireColumn.NumberFormat = "00"

XLDoc.SaveAs vFilePath

'XLApp.Visible = False 'you can hide excel again if you make it visible in the beggining

XLApp.Quit 'finish

Regards,

Chris

Re: VBScript Exporting string as number

thomastc — Tue, 25 Mar 2014 14:16:55 GMT

Hello,

We need to apply the formatting to the column in question first.

So after the following code:

XLDoc.Sheets(1).name = "SB_Export"

set XLSheet = XLDoc.Worksheets(1)

Add this line:

XLDoc.WorkSheets(1).Columns("E:E").EntireColumn.NumberFormat = "@"

Then change the following line:

XLSheet.Paste XLSheet.Range("A1")

To this:

XLSheet.Range("A1").PasteSpecial Paste = xlPasteValues

Thanks!

topic Re: VBScript Exporting string as number in QlikView

VBScript Exporting string as number

Re: VBScript Exporting string as number