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"&" "×tamp&".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?
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?
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..
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.