Merge Excel files - working using Excel 2007 but not with Excel 2016

[mm0000]

I have this code which merges files without problems with Excel 2007, but it does not work with Excel 2016. With Excel 2016 the 2nd and 3rd tabs are not populated but remain blank. Could anyone confirm that the code below does/ does not work with Excel 2016.

ERASE d:\x*.xlsb

**copy 3 excel files for testing code
COPY FILE d:\test\excel1.xlsb to d:\excel1.xlsb
COPY FILE d:\test\excel2.xlsb to d:\excel2.xlsb
COPY FILE d:\test\excel5.xlsb to d:\excel5.xlsb

loXL=GETOBJECT(',','Excel.Application')

*mxlver = loXL.version
*WAIT WINDOW mxlver

IF VARTYPE(loXL) = [O]
	   lcfrom1='d:\excel1.xlsb'
	   lcfrom2='d:\excel2.xlsb'

	   loXL=GETOBJECT(',','Excel.Application')

	**Open To file (file to merged into)
		lcto='d:\excel5.xlsb'
		loWBto=loXL.workbooks.open(lcto)
	    lowbto.Sheets('tab1').Move(,lowbto.Sheets(1))
		lowbto.sheets('Tab1').activate
  	 
	 ***Merge file 1 - excel1
		loWBfrom=loxl.workbooks.open(lcFrom1)
		lowbto.sheets.add.name="Tab2"
	    lowbto.Sheets('tab2').Move(,lowbto.Sheets(2))
		lowbfrom.sheets(1).activate
  
 		loxl.cells.select

		loxl.selection.copy
		lowbto.sheets('tab2').activate
		loxl.Selection.pastespecial
        
		loxl.range("A1:A1").select
	    lowbfrom.application.cutcopymode = .f.
        loWBfrom.close(0)

	***Merge file 2 - excel2
		loWBfrom=loxl.workbooks.open(lcFrom2)
		lowbto.sheets.add.name="Tab3"
    	lowbto.Sheets('Tab3').Move(,lowbto.Sheets(3))
		lowbfrom.sheets(1).activate
		loxl.cells.select
		loxl.selection.copy
		lowbto.sheets('Tab3').activate
		loxl.Selection.pastespecial
		loxl.range("A1:A1").select
	    lowbfrom.application.cutcopymode = .f.
		loWBfrom.close(0)

	    lowbto.sheets('Tab1').activate
	    loxl.range("A1:A1").select
	    lowbto.sheets(1).activate
	    lowbto.save
	    loXL.visible=.t.
ELSE
    =MESSAGEBOX('Error Opening Excel. Cannot create XLS report',16,'Error Message')
ENDIF

Thanks

 

[Tore Bleken]

AFAIK in newer vbscript code you can't call methods by only using their name. You must use parenthesis, and you also must pass a parameter, you can use .T. or .F. as dummies. So for instance this line

[pre]lowbto.sheets('Tab1').activate[/pre]

Must be changed into

[pre]lowbto.sheets('Tab1').activate(.T.)[/pre]

 

[mm0000]

Tore

Putting the (.t.) after activate gives an OLE error message 'Invalid number of parameters'

 

[Tore Bleken]

OK, then remove the parameter in that line. As I wrote, I am not sure about all the details, but I seem to remember that all the method calls should have parenthesizes.

Update: On second thought realize that my suggestion may be irrelevant in this case. Try to step through your code, and see if you notice anything unusual. Do you get any errors? Making Excel visible will be helpful until you fix this.