topic Re: Export to excel in QlikView

Export to excel

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

Hi,

My code for export is:

sub Export_CrtneKode Timestamp = Year(Date())&"-"&Month(Date())&"-"&Day(Date()) 'Create the Excel spreadsheet Set excelFile = CreateObject("Excel.Application") excelFile.Visible = false 'or false to run in background 'Create the WorkBook Set curWorkBook = excelFile.WorkBooks.Add 'Create the Sheet 'first chart object Set curSheet = curWorkBook.WorkSheets(1) 'paste the object Set i = ActiveDocument.GetSheetObject("CH02") i.CopyTableToClipboard true curSheet.Cells(1, 1).Select curSheet.Paste 'to clear the clipboard curSheet.Cells(1, 100).Copy 'define all cells format curSheet.Columns("A").EntireColumn.NumberFormat = "0" curSheet.Columns(2).ColumnWidth = 50 curSheet.Columns("B").EntireColumn.NumberFormat = "@" curSheet.Columns("C").EntireColumn.NumberFormat = "@" curSheet.Columns("D").EntireColumn.NumberFormat = "0" curSheet.Columns(5).ColumnWidth = 20 curSheet.Columns("E").EntireColumn.NumberFormat = "@" curSheet.Columns("F").EntireColumn.NumberFormat = "@" curSheet.Columns("G").EntireColumn.NumberFormat = "@" curSheet.Columns("H").EntireColumn.NumberFormat = "@" excelFile.Visible = true 'Save the file and quit excel excelFile.DisplayAlerts = False curWorkBook.SaveAs "S:\Porocanje QlikView\Izdelki Farmadent\ČRTNE KODE FD"&" "&timestamp&".xlsx" excelFile.DisplayAlerts = True curWorkBook.Close excelFile.Quit 'Cleanup Set curWorkBook = nothing Set excelFile = nothing end sub

Column E is EAN code formatted as text. After export the cell is displayed e.g. 6,94952E+12 instead of 6949517000018.

Any idea how can I solve this?

TIA, Igor

Re: Export to excel

m_woolf — Thu, 17 Oct 2019 12:56:11 GMT

Maybe format the column before pasting the data into the column?

Re: Export to excel

thepiapus — Thu, 17 Oct 2019 12:59:02 GMT

Doesn't help..

Re: Export to excel

m_woolf — Thu, 17 Oct 2019 13:03:18 GMT

Maybe set the column width before the paste?

Can you attach a sample qvw?

Re: Export to excel

thepiapus — Thu, 17 Oct 2019 13:28:54 GMT

Attached..Setting column width before pasting didn't help

Re: Export to excel

m_woolf — Thu, 17 Oct 2019 14:54:17 GMT

Try the attached qvw

Re: Export to excel

thepiapus — Fri, 18 Oct 2019 06:45:39 GMT

Thanks a lot for your effort.

It's funny..I have version 12.30 at work and the export stops at line curSheet.PasteSpecial "Text",false,False

Error is: pastespecial method of worksheet class failed

At home I have ver. 11.20 and macro works without problem...I have no idea why?

Regards

Re: Export to excel

m_woolf — Fri, 18 Oct 2019 11:58:40 GMT

I'm on 12.4 and it works for me??

Re: Export to excel

thepiapus — Fri, 18 Oct 2019 12:15:11 GMT

I know it's weird. I also tried on qv server and got the same error. Do you think that could be office related?
In both cases where it does not work is office 2010 installed. At home I have office 2013 and it's working..

Re: Export to excel

m_woolf — Fri, 18 Oct 2019 12:19:37 GMT

That sounds likely. I have Office 365.
What I did to figure out the macro was to run it down to the place where it copied the QV chart. Then I recorded a macro in Excel and did Paste Special. It came up with the choices "HTML", "Text", and one other option which I forget. If I chose HTML, the cell would show scientific notation. If I chose Text, it showed the full value. Maybe Excel 2010 will show different Paste Special choices or record the macro differently.

Good luck.

Re: Export to excel

thepiapus — Fri, 18 Oct 2019 12:24:41 GMT

Thx....:-)