VBA 5 Dateien auswählen, umbennen und speichern
25.08.2014 14:02:09
D.Hensel
Hallo, habe ein Problem mit folgendem Makro. Erhalte einen Debug Fehler bei " wb5.Sheets(1).copy Before:=wb1.Sheets(1)" Wenn ich Before und after lösche, dann gehts. Dann öffnet er aber mehrere Dateien. Ziel des Makros ist das man 5 Dateien auswählt, das Makro diese öffnet und im selben Worksheet abspeichert.
Sub ImportFiles()
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*", _
, "Bla")
If Ret1 = False Then Exit Sub
'~~> Get the 2nd File
Ret2 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "fsfsdf")
If Ret2 = False Then Exit Sub
'~~> Get the 3rd File
Ret3 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "sfsdfsd")
If Ret3 = False Then Exit Sub
'~~> Get the 4th File
Ret4 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "sfsfsf")
If Ret4 = False Then Exit Sub
'~~> Get the 5th File
Ret5 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "sfsfsfsfsfs")
If Ret5 = False Then Exit Sub
'Change name and open workbooks
Set wb5 = Workbooks.Open(Ret1)
wb5.Sheets(1).copy Before:=wb1.Sheets(1)
ActiveSheet.Name = "Blah 1"
wb5.Close SaveChanges:=False
Set wb5 = Workbooks.Open(Ret2)
wb5.Sheets(1).copy After:=wb1.Sheets(1)
ActiveSheet.Name = "Blah 2"
wb5.Close SaveChanges:=False
Set wb5 = Workbooks.Open(Ret3)
wb5.Sheets(1).copy After:=wb1.Sheets(1)
ActiveSheet.Name = "Blah 3"
wb5.Close SaveChanges:=False
Set wb5 = Workbooks.Open(Ret4)
wb5.Sheets(1).copy After:=wb1.Sheets(1)
ActiveSheet.Name = "Blah 4"
wb5.Close SaveChanges:=False
Set wb5 = Workbooks.Open(Ret5)
wb5.Sheets(1).copy After:=wb1.Sheets(1)
ActiveSheet.Name = "Blah 5"
wb5.Close SaveChanges:=False
Set wb2 = Nothing
Set wb1 = Nothing
Set wb3 = Nothing
Set wb4 = Nothing
Set wb5 = Nothing
End Sub