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

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

  

1 Solution

Accepted Solutions
Highlighted
Master II
Master II

Try the attached qvw

View solution in original post

10 Replies
Highlighted
Master II
Master II

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

Highlighted
Contributor II
Contributor II

Doesn't help..

Highlighted
Master II
Master II

Maybe set the column width before the paste?

Can you attach a sample qvw?

Highlighted
Contributor II
Contributor II

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

Highlighted
Master II
Master II

Try the attached qvw

View solution in original post

Highlighted
Contributor II
Contributor II

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

 

Highlighted
Master II
Master II

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

Highlighted
Contributor II
Contributor II

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..

Highlighted
Master II
Master II

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.