VBScript Exporting string as number

[chris_johnson]

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

    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

[thomastc · Accepted solution]

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!