Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
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
Highlighted
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! 🙂
Highlighted
Contributor II
Contributor II

Unfortunately it doesn't work for xlsx files.

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