Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

VBScript Exporting string as number

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

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

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!

View solution in original post

1 Reply
Highlighted
Contributor III
Contributor III

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!

View solution in original post