Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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!