VBScript循环遍历Excel文件并更改宏 [英] VBScript to loop through Excel-files and change macro

问题描述

我已经在上周发布了一个密切相关的问题 VBScript将代码添加到Excel工作簿，由程序员解决。但是我碰到了这个任务的下一个问题：

使用以下代码，我尝试循环遍历一个Excel文件夹，然后逐个打开它们，并更改 DieseArbeitsmappe 中的宏。这对第一个文件工作正常，但第二个结束时出现此错误消息。
错误讯息

  Set objFSO = CreateObject（Scripting.FileSystemObject）
 sFolder =P：\Administration\Reports\operativ\Tagesbericht\templates\START07 \TestTabsiNeu\
设置objExcel = CreateObject（Excel.Application）
 objExcel.Visible = True 
 objExcel.DisplayAlerts = False 
 
对于每个objFile在objFSO.GetFolder（sFolder）.Files 
设置objWorkbook = objExcel.Workbooks.Open（sFolder& objFile.Name）
 
设置组件= objworkbook.VBProject.VBComponents（DieseArbeitsmappe） 
 
 strCode = _ 
Sub WorkBook_Open（）仅用于测试& vbCr& _ 
Application.Run（'CommonMacro.xlsm'！Workbook_Open）& vbCr& _ 
End Sub
 component.CodeModule.AddFromString strCode 
 
 objWorkbook.SaveAsP：\Administration\Reports\operativ\Tagesbericht\templates\ START07\TestTabsiNeu\& objFile.Name 
 objExcel.Quit 
 
设置objWorkbook = Nothing 
设置组件=无
 
下一个
 
设置objFSO =没有
设置objExcel =没有
  

行10是设置组件=对我有用[0] / code>被称为 ThisWorkbook 。所以我将不得不基于第10行抛出的错误代码引入 if-else 或者是否有更好的解决方案？

提前感谢您的帮助。

解决方案

这不是一个完美的答案，因为我更多困惑比我确定的确切问题...但希望这将有所帮助。

命令 objExcel.Quit 将关闭Excel应用程序。

我不太确定代码（在下一个循环中）然后成功执行

  Set objWorkbook = objExcel.Workbooks.Open（sFolder& objFile.Name）
  

当 objExcel 应用程序已经退出。但是， .Visible 和 .DisplayAlerts 将不再设置为True / False。

因此，我建议您更换

  objExcel.Quit 
  

与

  objWorkbook.Close 
  

I already posted a closely related question last week VBScript to add code to Excel workbook which got solved by a fellow programmer. But I ran into the next problem with that task:

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 DieseArbeitsmappe. This works fine for the first file but the second ends with this error message. Error message

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.AddFromString 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

Line 10 is Set component = objworkbook.VBProject.VBComponents("DieseArbeitsmappe")

Another problem I will face soon is that sometimes the VBComponent is called ThisWorkbook. So I will have to introduce if-else based on the Error code thrown by Line 10. Or is there a better solution for this?

Thanks in advance for your help.

解决方案

This isn't a perfect answer, as I am more confused than I am certain of the exact problem... However hopefully this will help.

The command objExcel.Quit is going to close the Excel application.

I'm not quite sure how the code (in the next loop) then successfully executes

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

when the objExcel application has been quit. However, the .Visible and .DisplayAlerts will no longer be set True/False. The latter could cause your failure in line 10.

Therefore I suggest replacing

 objExcel.Quit

with

objWorkbook.Close

这篇关于VBScript循环遍历Excel文件并更改宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！