VBScript fait la boucle dans les files Excel et modifie la macro

J'ai déjà posté une question étroitement liée la semaine dernière VBScript pour append un code au classur Excel qui a été résolu par un autre programmeur. Mais j'ai rencontré le problème suivant avec cette tâche:

Avec le code suivant, j'essaie de bouger un dossier de files Excel puis de les ouvrir un par un et de changer la macro dans DieseArbeitsmappe . Cela fonctionne bien pour le premier file, mais le second se termine par ce message d'erreur. Message d'erreur

 Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing Définir objFSO = CreateObject ("Scripting.FileSystemObject") Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing sFolder = "P: \ Administration \ Reports \ operativ \ Tagesbericht \ templates \ START07 \ TestTabsiNeu \" Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing Définir objExcel = CreateObject ("Excel.Application") Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing objExcel.Visible = True Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing objExcel.DisplayAlerts = False Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing Pour chaque objFile dans objFSO.GetFolder (sFolder) .Files Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing Définir objWorkbook = objExcel.Workbooks.Open (sFolder & objFile.Name) Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing Set component = objworkbook.VBProject.VBComponents ("DieseArbeitsmappe") Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing strCode = _ Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing "Sub WorkBook_Open () 'just for testing" & vbCr & _ Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing "Application.Run (" "'CommonMacro.xlsm'! Workbook_Open" ")" & vbCr & _ Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing "End Sub" Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing component.CodeModule.AddFromSsortingng strCode Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing objWorkbook.SaveAs "P: \ Administration \ Reports \ operativ \ Tagesbericht \ templates \ START07 \ TestTabsiNeu \" & objFile.Name Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing objExcel.Quit Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing Set objWorkbook = Nothing Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing Set component = Nothing Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing Définir objFSO = Rien Set objFSO = CreateObject("Scripting.FileSystemObject") sFolder = "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False For Each objFile In objFSO.GetFolder(sFolder).Files Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe") strCode = _ "Sub WorkBook_Open() 'just for testing" & vbCr & _ " Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" & vbCr & _ "End Sub" component.CodeModule.AddFromSsortingng strCode objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" & objFile.Name objExcel.Quit Set objWorkbook = Nothing Set component = Nothing Next Set objFSO = Nothing Set objExcel = Nothing 

La ligne 10 est Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe")

Un autre problème auquel je serai bientôt confronté est que parfois le VBComponent s'appelle ThisWorkbook . Donc, je vais devoir introduire if-else fonction du code d'erreur lancé par la ligne 10. Ou y a-t-il une meilleure solution pour cela?

• Les valeurs alphanumériques cfspreadsheet se terminant par d
• Conversion des lettres de colonne Excel aux numéros de colonne correspondants
• Obtenez une seule date à partir de la date-time sql sans convertir en varchar afin que je puisse le sortinger dans Excel
• Comment puis-je prendre un file excel et convertir ses colonnes en lists en Python?
• Code VBA pour afficher la boîte de dialog Boîte aux lettres si la formule dans la cellule cible dépasse une certaine valeur

Merci d'avance pour votre aide.

• Matlab nombre de lignes dans le file excel
• Comment accéder aux groupes de contacts dans Excel VBA?
• Excel VBA: "erreur définie par l'application ou définie par l'object"
• Ajouter des feuilles à la fin du classur dans Excel (la méthode normale ne fonctionne pas?)
• Excel: Existe-t-il une formule pour ce grand espace entre les deux?

 Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name) 

  objExcel.Quit 

 objWorkbook.Close