我试图对一些excel工作簿(超过20个)进行大量更改.每个工作簿包含大约16个单独的工作表,我想编写一个脚本,它将循环遍历每个工作簿,工作表包含内部并写入/修改我需要的单元格.我需要保留所有字符串验证,宏和格式.所有工作簿都是2007年的格式.
我已经看过python excel库和PHPexcel,但是宏,按钮,公式,字符串验证和格式化,并且在编写新工作簿时不会保留.有没有一种简单的方法可以做到这一点,或者我是否必须单独打开每个工作簿并提交更改.我试图避免在VBscript中创建一个宏,并且必须单独打开每个工作簿以提交我需要的更改.
解决方法:
我避免使用像瘟疫这样的多个工作簿这是一个痛苦,如果这是一个持续的要求,那么我建议回顾你的工作簿设计,看看你是否可以合并回一个工作簿.我经常看到每个工作簿都按月保存,当时他们应该有一个工作簿,其中一个工作表包含原始数据,每行代表一个月,然后是另一个显示用于查找用户选择的原始数据的工作表.这是一个非常大的概括,你很可能处于完全不同的情况.
如果它一次性 – 我知道它不是你想要的,但我认为你最好使用VBA循环工作簿.像(未经测试)的东西:
Excel 2003:
Sub AdjustMultipleFiles()Dim lCount As LongDim wbLoopBook As WorkbookDim wsLoopSheet As WorksheetWith Application .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = FalseEnd WithWith Application.FileSearch .NewSearch '// Change path to suit .LookIn = "C:\MyDocuments" '// ALL Excel files .FileType = msoFileTypeExcelWorkbooks '// Uncomment if file naming convention needed '.Filename = "Book*.xls" '// Check for workbooks If .Execute > 0 Then '// Loop through all. For lCount = 1 To .FoundFiles.Count '// Open Workbook x and Set a Workbook variable to it Set wbLoopBook = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0) '// Loop through all worksheets For Each wsLoopSheet In wbLoopBook.Worksheets '//Update your worksheets here... Next wsLoopSheet '// Close Workbook & Save wbLoopBook.Close SaveChanges:=True '// Release object variable Set wbLoopBook = Nothing Next lCount End IfEnd WithWith Application .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = TrueEnd WithEnd Sub
对于EXCEL 2007:
Sub AdjustMultipleFiles() Dim sFileName As String Dim wbLoopBook As Workbook Dim wsLoopSheet As Worksheet With Application .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False End With '// Change path to suit ChDir "C:\Documents" '// ALL Excel 2007 files sFileName = Dir("*.xlsx") Do While sFileName <> "" '// Open Workbook x and Set a Workbook variable to it Set wbLoopBook = Workbooks.Open(Filename:=sFileName, UpdateLinks:=0) '// Loop through all worksheets For Each wsLoopSheet In wbLoopBook.Worksheets '//Update your worksheets here... Next wsLoopSheet '// Close Workbook & Save wbLoopBook.Close SaveChanges:=True '// Release object variable Set wbLoopBook = Nothing '//Next File sFileName = Dir '//End Loop Loop With Application .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True End WithEnd Sub
Excel 2007(FileSystemObject – LateBinding)
Sub AdjustMultipleFiles() Dim wbLoopBook As Workbook Dim wsLoopSheet As Worksheet With Application .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False End With With CreateObject("Scripting.FileSystemObject") '// Change path to suit For Each File In .GetFolder("C:\Documents").Files '// ALL Excel 2007 files If .GetExtensionName(File) = "xlsx" Then '// Open Workbook x and Set a Workbook variable to it Set wbLoopBook = Workbooks.Open(Filename:=File.Path, UpdateLinks:=0) '// Loop through all worksheets For Each wsLoopSheet In wbLoopBook.Worksheets '//Update your worksheets here... Next wsLoopSheet '// Close Workbook & Save wbLoopBook.Close SaveChanges:=True '// Release object variable Set wbLoopBook = Nothing End If Next File End With With Application .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True End WithEnd Sub
来源:https://www.icode9.com/content-1-255301.html
联系客服