Merge 3 Excel files - works with Excel 2007 but not with Excel 2016 1

[mm0000]

The code below is for merging 3 separate excel files into 1 file. It works for Excel 2007 but does not work with Excel 2016. The code below is written for Visual Foxpro but you should understand it. Are there any changes in Excel 2016 VBA I should be aware of that stops the code from working. With Excel 2016 data is not copied into tabs 2 and 3 but the tabs are created.

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.Selection.pastespecial(-4104,-4142,.f.,.f.)
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

 

[combo]

Is d:\ trusted? Try to open the file from excel, do you get it editable or in protected view?
If possible, make excel visible (loXL.Visible = True after GETOBJECT) and execute line by line.
[tt]loxl.Selection.pastespecial &&loxl.Selection.pastespecial(-4104,-4142,.f.,.f.)[/tt] seems to have strange argument, do you copy all (as PasteSpecial without arguments does) or only values?
Instead of copying values you could rename and copy whole sheets.



combo

 

[mm0000]

The the Excel files are trusted and they are editable.
The statement loxl.Selection.pastespecial &&loxl.Selection.pastespecial(-4104,-4142,.f.,.f.) the portion after && is a comment and is to be ignored. I tried both loxl.Selection.pastespecial [without parameters] and loxl.Selection.pastespecial(-4104,-4142,.f.,.f.) [with parameters] but there was not difference - they both didnt work.

 

[combo]

Have you tried to set excel visible (if it isn't) and execute step by step?
I don't know Foxpro, do you have something like VB editor in office programmes? If so, can you see local variables? Again, can you execute the code line by line and observe excel?
Try simpler (adjust syntax if required, I assumed that there are no Tab2 and Tab3 sheets in merged workbooks):

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

loXl=GETOBJECT(',','Excel.Application')
loXL.visible=.t. && for testing

**Open To file (file to merged into)
lcTo='d:\excel5.xlsb'
loWbTo=loXL.Workbooks.open(lcTo)
loWbTo.Worksheets('tab1').Move(,lowbto.Worksheets(1))

***Merge file 1 - excel1
loWbFrom=loXl.workbooks.open(lcFrom1)
loWbFrom.Worksheets(1).Name="Tab2"
loWbFrom.Worksheets(1).Copy(,loWbTo.Worksheets(1))
loWbFrom.close(0)

***Merge file 2 – excel2
loWbFrom=loXl.workbooks.open(lcFrom2)
loWbFrom.Worksheets(1).Name="Tab3"
loWbFrom.Worksheets(1).Copy(,loWbTo.Worksheets(2))
loWbFrom.close(0)

loWbTo.Worksheets('Tab1').activate
loWbTo.Worksheets('Tab1').range("A1:A1").select
loWbTo.Save
loXl.visible=.t.
ELSE
=MESSAGEBOX('Error Opening Excel. Cannot create XLS report',16,'Error Message')
ENDIF

combo

 

[SkipVought]

If you were to coding and running this from Excel (in order to simplify)...
Note that the SHEET is copied...

Sub MergeSheets()
'copies the first Sheet to the wbTO workbook
    Dim i As Integer, sWB(2) As String
    Dim wbTO As Workbook, wbFR As Workbook
    
    sWB(0) = "d:\excel5.xlsb"
    sWB(1) = "d:\excel1.xlsb"
    sWB(2) = "d:\excel2.xlsb"
    
    
    For i = 0 To 2
        Select Case i
            Case 0
                Set wbTO = Workbooks.Open(sWB(i))
            Case Else
                Set wbFR = Workbooks.Open(sWB(i))
        End Select
        
        If i > 0 Then
            wbFR.Sheets(1).Copy After:=wbTO.Sheets(wbTO.Sheets.Count)
            wbFR.Close
        End If
    Next
    
    wbTO.Save
    wbTO.Close
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mm0000]

Combo,
Your code with copy works with Excel 2016. I still have no idea why pastespecial does not work. A star for you.

 

[combo]

It should work, there may be only a problem with proper ranges referencing. Sometimes you activate, select and copy. As a consequence you may have on the top something different from what you expect (as in loxl.selection.copy, refers to excel application current selection in active window). Most of excel processing can be done without selecting and with direct objects referencing, this may speed up the code, make it more robust and easier to trace.

combo