Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Guidok
Contributor II
Contributor II

Export to excel changes the format from text to date

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

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

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

 

View solution in original post

3 Replies
MarkWillems
Contributor III
Contributor III

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.

Hey, Please don't forget to add kudos, like or mark as a solution if my reply has helped you at all! 🙂
Guidok
Contributor II
Contributor II
Author

Unfortunately it doesn't work for xlsx files.

Frank_Hartmann
Master II
Master II

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