×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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.

CODE

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

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

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

lowbto.sheets('Tab1').activate 

Must be changed into

lowbto.sheets('Tab1').activate(.T.) 

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

(OP)
Tore

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

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close