VBA - can't find the runtime error -2147221080 (800401a8)

[koskesh]

I have a macro to import 5 workbooks into one (and rename them).
The debugger stops after importing the first file. The wb2 part gets a runtime error: -2147221080 (800401a8)

Sub ImportFiles()
    ChDrive "X"
    ChDir "X:\Test"
       
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook, wb4 As Workbook, wb5 As Workbook
    Dim Ret1, Ret2, Ret3, Ret4, Ret5
    Set wb1 = ActiveWorkbook
    '~~> Get the first File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Opening Stock ZR141")
    If Ret1 = False Then Exit Sub
    '~~> Get the 2nd File
    Ret2 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Closing Stock ZR141")
    If Ret2 = False Then Exit Sub
    
     '~~> Get the 3rd File
    Ret3 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "MB5B Opening Stock")
    If Ret3 = False Then Exit Sub
    
        '~~> Get the 4th File
    Ret4 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "MB5B Closing Stock")
    If Ret4 = False Then Exit Sub
    
           '~~> Get the 5th File
    Ret5 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Masterdata")
    If Ret5 = False Then Exit Sub
    
     
    'Change name and open workbooks
     Set wb1 = Workbooks.Open(Ret1)
    wb1.Sheets(1).copy wb1.Sheets(1)
    ActiveSheet.Name = "ZR141 Opening Stock"
    wb1.Close savechanges:=False

    Set wb2 = Workbooks.Open(Ret2)
   [B]wb2.Sheets(1).copy wb1.Sheets(2) <- runtime error!
[/B] ActiveSheet.Name = "ZR141 Closing Stock"
    wb2.Close savechanges:=False
  
    Set wb3 = Workbooks.Open(Ret3)
    wb3.Sheets(1).copy wb1.Sheets(3)
    ActiveSheet.Name = "MB5B Opening Stock"
    wb3.Close savechanges:=False
    
    Set wb4 = Workbooks.Open(Ret4)
    wb4.Sheets(1).copy wb1.Sheets(4)
    ActiveSheet.Name = "MB5B Closing Stock"
    wb4.Close savechanges:=False
    
    Set wb5 = Workbooks.Open(Ret5)
    wb5.Sheets(1).copy wb1.Sheets(5)
    ActiveSheet.Name = "Masterdata"
    wb5.Close savechanges:=False
    
    Set wb2 = Nothing
    Set wb3 = Nothing
    Set wb4 = Nothing
    Set wb5 = Nothing
    Set wb1 = Nothing
    
   End Sub

The runtime error occurs here:
Set wb2 = Workbooks.Open(Ret2)
wb2.Sheets(1).copy wb1.Sheets(2) <- runtime error!
ActiveSheet.Name = "ZR141 Closing Stock"
wb2.Close savechanges:=False

Hope someone can fix this annoying error for me. Thank you.

 

[Andrew Poulsom]

What's the text of the error?

 

[koskesh]

Hi Andrew,

runtime error: -2147221080 (800401a8)
Automation error

Thanks for the quick respnse

 

[Andrew Poulsom]

I imagine the error occurs because you have closed wb1 (which was originally the ActiveWorkbook):

wb1.Close savechanges:=False

You need wb1 to wb6.

 

[koskesh]

I fixed the issue with the runtime error.

    'Change name and open workbooks
     Set wb1 = Workbooks.Open(Ret1)
    wb1.Sheets(1).copy wb1.Sheets(1)
    ActiveSheet.Name = "ZR141 Opening Stock"
    
    Set wb2 = Workbooks.Open(Ret2)
   [B]wb2.Sheets(1).copy wb1.Sheets(2) 
[/B]ActiveSheet.Name = "ZR141 Closing Stock"
    wb2.Close savechanges:=False
  
    Set wb3 = Workbooks.Open(Ret3)
    wb3.Sheets(1).copy wb1.Sheets(3)
    ActiveSheet.Name = "MB5B Opening Stock"
    wb3.Close savechanges:=False
    
    Set wb4 = Workbooks.Open(Ret4)
    wb4.Sheets(1).copy wb1.Sheets(4)
    ActiveSheet.Name = "MB5B Closing Stock"
    wb4.Close savechanges:=False
    
    Set wb5 = Workbooks.Open(Ret5)
    wb5.Sheets(1).copy wb1.Sheets(5)
    ActiveSheet.Name = "Masterdata"
    wb5.Close savechanges:=False
    
    Set wb2 = Nothing
    Set wb3 = Nothing
    Set wb4 = Nothing
    Set wb5 = Nothing
    Set wb1 = Nothing
    
   End Sub

Closing the first workbook caused the error. I am not sure why. But I have another issue with the code. The last file is exactly the same as the first file (only with a different name). I don't know why

 

[Andrew Poulsom]

As I said you need wb1 to wb6. wb1 is the ActiveWorkbook. Open wb2 to wb6 and copy to wb1.

 

[koskesh]

Hi Andrew, thank you. I tried to change it from wb2 to wb6. But I get the same automation error as above.

Option Explicit
Sub ImportFiles()
    ChDrive "X"
    ChDir "X:\Administration\Export Department\ZzzCiamak\Makro Test"
       
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook, wb4 As Workbook, wb5 As Workbook, wb6 As Workbook
    Dim Ret1, Ret2, Ret3, Ret4, Ret5
    Set wb1 = ActiveWorkbook

    '~~> Get the first File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Opening Stock ZR141")
    If Ret1 = False Then Exit Sub
    '~~> Get the 2nd File
    Ret2 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Closing Stock ZR141")
    If Ret2 = False Then Exit Sub
    
     '~~> Get the 3rd File
    Ret3 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "MB5B Opening Stock")
    If Ret3 = False Then Exit Sub
    
        '~~> Get the 4th File
    Ret4 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "MB5B Closing Stock")
    If Ret4 = False Then Exit Sub
    
           '~~> Get the 5th File
    Ret5 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Masterdata")
    If Ret5 = False Then Exit Sub
    
      
    'Change name and open workbooks
    Set wb2 = Workbooks.Open(Ret2)
    wb2.Sheets(1).copy wb1.Sheets(2)
    ActiveSheet.Name = "ZR141 Closing Stock"
    wb2.Close savechanges:=False
  
    Set wb3 = Workbooks.Open(Ret3)
    wb3.Sheets(1).copy wb1.Sheets(3)
    ActiveSheet.Name = "MB5B Opening Stock"
    wb3.Close savechanges:=False
    
    Set wb4 = Workbooks.Open(Ret4)
    wb4.Sheets(1).copy wb1.Sheets(4)
    ActiveSheet.Name = "MB5B Closing Stock"
    wb4.Close savechanges:=False
    
    Set wb5 = Workbooks.Open(Ret5)
    wb5.Sheets(1).copy wb1.Sheets(5)
    ActiveSheet.Name = "Masterdata"
    wb5.Close savechanges:=False
    
    Set wb6 = Workbooks.Open(Ret1)
    wb6.Sheets(1).copy wb1.Sheets(1)
    ActiveSheet.Name = "ZR141 Opening Stock"
    
    Set wb2 = Nothing
    Set wb3 = Nothing
    Set wb4 = Nothing
    Set wb5 = Nothing
    Set wb1 = Nothing
    Set wb6 = Nothing
    
   End Sub

 

[Andrew Poulsom]

On which line do you get the error?

 

[koskesh]

I get the error after opening (and before renaming the first file)
'Change name and open workbooks
Set wb2 = Workbooks.Open(Ret2)


A friend of mine tried to help me with a different version, I get the same error

Sub TestMerge()
  ChDrive "x"
  ChDir "X:\Test"
  
  Dim wb As Workbook, wbAkt As Workbook
  Dim Ret(1 To 5)
  Dim i As Integer
  Dim sNames
  sNames = Array("", "ZR141 Opening Stock", "ZR141 Closing Stock", _
                     "MB5B Opening Stock", "MB5B Closing Stock", _
                     "MasterData")
  Set wbAkt = ActiveWorkbook
  For i = 1 To 5
    '~~> Get the FileNames
    Ret(i) = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
      , sNames(i))
    If Ret(i) = False Then Exit Sub
  Next i
  For i = 1 To 5
    'Change name and open workbooks
    Set wb = Workbooks.Open(Ret(i))
    wb.Worksheets(i).copy wbAkt.Worksheets(i)
    ActiveSheet.Name = sNames(i)
    wb.Close False
  Next i
  
End Sub

The error occurs here: wb.Worksheets(i).copy wbAkt.Worksheets(i)
Same issue: The script opens the first file but renaming the sheet and opening the 2nd file does not work.

 

[Andrew Poulsom]

I think it should be:

wb.Worksheets(1).copy wbAkt.Worksheets(i)