Export to excel

[thepiapus]

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

[m_woolf]

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

[thepiapus]

Doesn't help..

[m_woolf]

Maybe set the column width before the paste?

Can you attach a sample qvw?

[thepiapus]

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

[m_woolf · Accepted solution]

Try the attached qvw

[thepiapus]

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

[m_woolf]

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

[thepiapus]

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

[m_woolf]

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.