topic Re: Export to excel changes the format from text to date in QlikView

Export to excel changes the format from text to date

Guidok — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I've added a export button for exporting some of the tables to excel. This works fine, but we have locations that look like a date. For example 22-01-50

In Qlikview this location is 22-01-50 but when exported to excel it looks like 22-1-1950.

We have a lot of formats for locations but the length has a maximum of 10 characters.

I've added some VB code to prevent this, but it doesn't work. I've seached the forum and used google, but I couldn't find a script that does the trick (I'm a VB newbie).

This is what I have now:

sub Aanvullen
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set xlDoc = objExcel.Workbooks.Add
set obj = ActiveDocument.GetSheetObject("CH02")
obj.CopyTableToClipboard true
objExcel.Worksheets(1).Columns("A:C").Numberformat = "@@@@@@@@@@"
objExcel.Range("A1").PasteSpecial
objExcel.Worksheets(1).Rows("1:1000").RowHeight = 15
objExcel.Worksheets(1).Columns("A:C").Autofit
xlDoc.SaveAs "X:\Uitwissel\Logistieke Controles\Aanvullen.xlsx"
xlDoc.Close
end sub

Re: Export to excel changes the format from text to date

MarkWillems — Tue, 06 Aug 2019 08:46:00 GMT

This is because by the time you have pasted down the data, Excel has already internally changed the data to a date. No formatting can change that as internally, that field is now the serial equivalent of that date. So the fix has to be in the way we get the data out.

set obj = ActiveDocument.GetSheetObject("CH02")

obj.ExportBiff "X:\Uitwissel\Logistieke Controles\Aanvullen.xls"

This will fix the issue, but it will export as xls. You may be able to export to xlsx in the latest version, but I can't test that.

Re: Export to excel changes the format from text to date

Guidok — Thu, 15 Aug 2019 10:46:01 GMT

Unfortunately it doesn't work for xlsx files.

Re: Export to excel changes the format from text to date

Frank_Hartmann — Thu, 15 Aug 2019 11:03:30 GMT

try this:

sub Aanvullen Set objExcel = CreateObject("Excel.Application") objExcel.Visible = False objExcel.DisplayAlerts = False Set xlDoc = objExcel.Workbooks.Add set obj = ActiveDocument.GetSheetObject("CH02") obj.CopyTableToClipboard true objExcel.Worksheets(1).Columns("A:C").Numberformat = "@" objExcel.Range("A1").PasteSpecial 1 objExcel.Worksheets(1).Rows("1:1000").RowHeight = 15 objExcel.Worksheets(1).Columns("A:C").Autofit xlDoc.SaveAs "X:\Uitwissel\Logistieke Controles\Aanvullen.xlsx" xlDoc.Close end sub