title: VBScript to loop through Excel-files and change macro
body: <p>I already posted a closely related question last week <a href="https://stackoverflow.com/questions/42537940/vbscript-to-add-code-to-excel-workbook">VBScript to add code to Excel workbook</a> which got solved by a fellow programmer. But I ran into the next problem with that task:</p>
  
  <p>With the following code, I try to loop through a folder of Excel files then open them one by one and change the macro in <code>DieseArbeitsmappe</code>. This works fine for the first file but the second ends with this error message.
  <a href="https://i.stack.imgur.com/h7Eds.png" rel="nofollow noreferrer">Error message</a></p>
  
  <pre><code>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 &amp; objFile.Name)
  
      Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe")
  
         strCode = _
         "Sub WorkBook_Open() 'just for testing" &amp; vbCr &amp; _
         "   Application.Run (""'CommonMacro.xlsm'!Workbook_Open"")" &amp; vbCr &amp; _
         "End Sub"
          component.CodeModule.AddFromString strCode
  
      objWorkbook.SaveAs "P:\Administration\Reports\operativ\Tagesbericht\templates\START07\TestTabsiNeu\" &amp; objFile.Name
      objExcel.Quit
  
      Set objWorkbook = Nothing
      Set component = Nothing
  
  Next
  
  Set objFSO = Nothing
  Set objExcel = Nothing
  </code></pre>
  
  <p>Line 10 is <code>Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe")</code></p>
  
  <p>Another problem I will face soon is that sometimes the <code>VBComponent</code> is called <code>ThisWorkbook</code>. So I will have to introduce <code>if-else</code> based on the Error code thrown by Line 10. Or is there a better solution for this?</p>
  
  <p>Thanks in advance for your help.</p>