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

koskesh

New Member
Joined
May 30, 2014
Messages
41
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)

Code:
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi Andrew,

runtime error: -2147221080 (800401a8)
Automation error

Thanks for the quick respnse
 
Upvote 0
I imagine the error occurs because you have closed wb1 (which was originally the ActiveWorkbook):

Code:
wb1.Close savechanges:=False

You need wb1 to wb6.
 
Upvote 0
I fixed the issue with the runtime error.

Code:
    '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
 
Upvote 0
Hi Andrew, thank you. I tried to change it from wb2 to wb6. But I get the same automation error as above.

Code:
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
 
Upvote 0
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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,214,822
Messages
6,121,772
Members
449,049
Latest member
greyangel23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top