|
Hallo,
ich habe versucht mit dem MakroRecorder ein Makro aufzuzeichen.
in der DailyRec&Disb.MasterfileEU_VBA existiert fuer jedes Land eine Lasche. Jede LAsche ist zu den jeweiligen Laenderdateien verlinkt (die sammelt man regelmaessig mit updates ein).
Nun soll dass Makro in der file DailyRec&Disb.MasterfileEU_VBA folgende Schritte ausfuehren und genau diese Schritte habe ich ausgefuehrt waehrend der Makro Recorder lief.
1. Oeffne alle Laenderdateien (damit die aktualisierung der Verlinkung in Schritt 2 schnell vonstatten geht)
2. Aendere die Quelle fuer jede Verknuepfung zur neuen Laender Datei (unten steht im Code 0 updates weil es keine update gab in der neuen Datei gab)
3. schliesse die Dateien
DAs Makro fuehrt nur den Schritt 1 aus und das auch nur fuer die erste DAtei - danach tut sich nichts mehr.
Hat jemand eine Idee? Ich bin der totale Anfaenger in VBA.
Viele Dank
Sub Makro1()
'
' Makro1 Makro
' open country file and renew link/source data
'
' Tastenkombination: Strg+Umschalt+A
'
ChDir _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day"
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\AT_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\BEL_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\BENL_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\DE_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\ES_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\FR_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\IT_HY_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\NO_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\PL_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\PT_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\RUS_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\SK_Masterfile.xlsx" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:\Users\gillnerc\Desktop\Desktop\Automatization DailyRec.&Disb\prior day\UK_Masterfile.xlsx" _
, UpdateLinks:=0
Windows("DailyRec&Disb.MasterfileEU_VBA.xlsm").Activate
ActiveWorkbook.ChangeLink Name:="AT_Masterfile.xlsx", NewName:= _
"AT_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="BEL_Masterfile.xlsx", NewName:= _
"BEL_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="BENL_Masterfile.xlsx", NewName:= _
"BENL_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="DE_Masterfile.xlsx", NewName:= _
"DE_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="ES_Masterfile.xlsx", NewName:= _
"ES_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="FR_Masterfile.xlsx", NewName:= _
"FR_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="IT_HY_Masterfile.xlsx", NewName:= _
"IT_HY_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="NO_Masterfile.xlsx", NewName:= _
"NO_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="PL_Masterfile.xlsx", NewName:= _
"PL_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="PT_Masterfile.xlsx", NewName:= _
"PT_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="RUS_Masterfile.xlsx", NewName:= _
"RUS_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="SK_Masterfile.xlsx", NewName:= _
"SK_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="UK_Masterfile.xlsx", NewName:= _
"UK_Masterfile.xlsx", Type:=xlExcelLinks
ActiveWorkbook.Save
Windows("UK_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("SK_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("RUS_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("PT_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("PL_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("NO_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("IT_HY_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("FR_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("ES_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("DE_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("BENL_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("BEL_Masterfile.xlsx").Activate
ActiveWindow.Close
Windows("AT_Masterfile.xlsx").Activate
ActiveWindow.Close
End Sub |