compatibility vba 2016 VS vba 2019

[stefanov]

Good evening to the whole forum and happy new year, I have a problem that I assume is related to the compatibility of the two versions of excel indicated in the title. I have a macro that with Excel 2016 works perfectly while I move it to another pc where I installed excel 2019the macro starts and immediately goes to the userform ignoring all the written code. Below I'll give you the code. In case it is necessary I can also attach the file... Thanks in advance to those who want to help me.


Ciao

Sub IMPORT_DATA()

Dim oExcel As Excel.Application
Dim strFile As String
Dim FileCorrente As Object
Dim r As Integer
Set FileCorrente = ActiveSheet
Set oExcel = New Excel.Application
'
mFolder = "E:\Bofetti\CHECK LIST\"


strFile = Dir(mFolder & "*.xlsx") ' assicurasi che l'estensione del file sia corretta
r = 12 'variabile riga
'inizia ciclo lettura
Do While strFile <> ""
    ' in oExcel ci vanno a finire di volta in volta _
      i file contenuti nella cartella
    oExcel.Workbooks.Open mFolder & strFile
    
    
    FileCorrente.Cells(r, 2) = oExcel.Worksheets("LGI Certificate Accessories").Cells(7, 4)   'D7
    FileCorrente.Cells(r, 3) = oExcel.Worksheets("LGI Certificate Accessories").Cells(17, 1)   'A17
    FileCorrente.Cells(r, 4) = oExcel.Worksheets("LGI Certificate Accessories").Cells(17, 6)   'F17
    FileCorrente.Cells(r, 5) = oExcel.Worksheets("LGI Certificate Accessories").Cells(31, 4)   'D31
    FileCorrente.Cells(r, 6) = oExcel.Worksheets("LGI Certificate Accessories").Cells(31, 13)   'M31
    FileCorrente.Cells(r, 7) = oExcel.Worksheets("LGI Certificate Accessories").Cells(17, 16)   'P17
    FileCorrente.Cells(r, 8) = oExcel.Worksheets("LGI Certificate Accessories").Cells(18, 19)   'S18
    FileCorrente.Cells(r, 9) = oExcel.Worksheets("LGI Certificate Accessories").Cells(17, 23)   'W17
    FileCorrente.Cells(r, 10) = oExcel.Worksheets("LGI Certificate Accessories").Cells(58, 9)   'I58
    oExcel.ActiveWorkbook.Close False
    strFile = Dir
    r = r + 1
    
    
    
    
  
Loop
' chiude e azzera variabili
oExcel.Quit
Set oExcel = Nothing






Range("A1").Select
End Sub

[rollis13]

Your macro has no incompatibility with Excel 2019, I only cleaned it up a little.
If the path is correct and the files .xlsx are present give it another chance:

Option ExplicitSub IMPORT_DATA()
    Dim oExcel As Excel.Application
    Dim strFile As String
    Dim r      As Integer
    Dim mFolder As String
    
    Set oExcel = New Excel.Application
    mFolder = "E:\Bofetti\CHECK LIST\"       '<= check this path
    strFile = Dir(mFolder & "*.xlsx")             ' assicurasi che l'estensione del file sia corretta
    r = 12                                        'variabile riga
    'inizia ciclo lettura
    Do While strFile <> ""
        'in oExcel ci vanno a finire di volta in volta _
        'i file contenuti nella cartella
        oExcel.Workbooks.Open mFolder & strFile
        With ActiveSheet
            .Cells(r, 2) = oExcel.Worksheets("LGI Certificate Accessories").Cells(7, 4) 'D7
            .Cells(r, 3) = oExcel.Worksheets("LGI Certificate Accessories").Cells(17, 1) 'A17
            .Cells(r, 4) = oExcel.Worksheets("LGI Certificate Accessories").Cells(17, 6) 'F17
            .Cells(r, 5) = oExcel.Worksheets("LGI Certificate Accessories").Cells(31, 4) 'D31
            .Cells(r, 6) = oExcel.Worksheets("LGI Certificate Accessories").Cells(31, 13) 'M31
            .Cells(r, 7) = oExcel.Worksheets("LGI Certificate Accessories").Cells(17, 16) 'P17
            .Cells(r, 8) = oExcel.Worksheets("LGI Certificate Accessories").Cells(18, 19) 'S18
            .Cells(r, 9) = oExcel.Worksheets("LGI Certificate Accessories").Cells(17, 23) 'W17
            .Cells(r, 10) = oExcel.Worksheets("LGI Certificate Accessories").Cells(58, 9) 'I58
        End With
        oExcel.ActiveWorkbook.Close False
        strFile = Dir
        r = r + 1
    Loop
    ' chiude e azzera variabili
    oExcel.Quit
    Set oExcel = Nothing
    Range("A1").Select
End Sub

[p45cal]

I do remember there being a change in whether Excel opens multiple workbooks in the same instance of Excel or not, but I can't remember when this was.
Why are you starting a new instance of Excel anyway?
I've not tested the following but you can try the likes of:

Sub IMPORT_DATA2()
'Dim oExcel As Excel.Application
Dim strFile As String
Dim FileCorrente As Object
Dim r As Integer
Set FileCorrente = ActiveSheet
'Set oExcel = New Excel.Application
'
mFolder = "E:\Bofetti\CHECK LIST\"
strFile = Dir(mFolder & "*.xlsx")    ' assicurasi che l'estensione del file sia corretta
r = 12    'variabile riga
'inizia ciclo lettura
Do While strFile <> ""
  ' in oExcel ci vanno a finire di volta in volta _
  i file contenuti nella cartella
  Set wb = Workbooks.Open(mFolder & strFile)
    
  FileCorrente.Cells(r, 2) = wb.Worksheets("LGI Certificate Accessories").Cells(7, 4)    'D7
  FileCorrente.Cells(r, 3) = wb.Worksheets("LGI Certificate Accessories").Cells(17, 1)    'A17
  FileCorrente.Cells(r, 4) = wb.Worksheets("LGI Certificate Accessories").Cells(17, 6)    'F17
  FileCorrente.Cells(r, 5) = wb.Worksheets("LGI Certificate Accessories").Cells(31, 4)    'D31
  FileCorrente.Cells(r, 6) = wb.Worksheets("LGI Certificate Accessories").Cells(31, 13)    'M31
  FileCorrente.Cells(r, 7) = wb.Worksheets("LGI Certificate Accessories").Cells(17, 16)    'P17
  FileCorrente.Cells(r, 8) = wb.Worksheets("LGI Certificate Accessories").Cells(18, 19)    'S18
  FileCorrente.Cells(r, 9) = wb.Worksheets("LGI Certificate Accessories").Cells(17, 23)    'W17
  FileCorrente.Cells(r, 10) = wb.Worksheets("LGI Certificate Accessories").Cells(58, 9)    'I58
  wb.Close False
  strFile = Dir
  r = r + 1
Loop
' chiude e azzera variabili
'oExcel.Quit
'Set oExcel = Nothing
Range("A1").Select
End Sub

which can be cut down to (again not tested):

Sub IMPORT_DATA3()
Dim strFile As String, mFolder, wb
Dim FileCorrente
Dim r As Long
Set FileCorrente = ActiveSheet
'
mFolder = "E:\Bofetti\CHECK LIST\"
strFile = Dir(mFolder & "*.xlsx")    ' assicurasi che l'estensione del file sia corretta
r = 12    'variabile riga
Do While strFile <> ""
  Set wb = Workbooks.Open(mFolder & strFile)
  With wb.Worksheets("LGI Certificate Accessories")
    FileCorrente.Cells(r, 2).Resize(, 9).Value = Array(.Cells(7, 4), .Cells(17, 1), .Cells(17, 6), .Cells(31, 4), .Cells(31, 13), .Cells(17, 16), .Cells(18, 19), .Cells(17, 23), .Cells(58, 9))
  End With
  wb.Close False
  strFile = Dir
  r = r + 1
Loop
Range("A1").Select
End Sub

[Paul_Hossler]

There were some version changes in the way Multiple Document Interface (MDI) and Single Document Interface (SDI). Don't know if that's the issue

https://docs.microsoft.com/en-us/off...rface-in-excel

https://answers.microsoft.com/en-us/...9-0a6e8a26b896

[p45cal]

This has been crossposted here: https://www.forumexcel.it/forum/thre...on-2019.44227/ where he seems to have hacked off the moderators since the thread's been closed and he's been 'repositioned' (banned from posting?) for a year for bad language.
Given that, I doubt we'll hear from him again, whether or not we've solved his problem…