脚本变更多个Excel工作簿
我试图使大的变化了许多的Excel工作簿(超过20)。每个工作簿都包含大约16张单独的工作表,并且我想编写一个脚本,它将遍历每个工作簿,并且工作表包含里面的内容并编写/修改我需要的单元格。我需要保持所有的字符串验证,宏和格式。所有的工作簿都是2007年的格式。脚本变更多个Excel工作簿
我已经看过蟒蛇擅长libaries和PHPexcel,但宏,按钮,公式,字符串验证和格式和新的工作簿被写入时不存。有没有简单的方法来做到这一点,或者我将不得不单独打开每个工作簿并提交更改。我试图避免在VBscript中创建一个宏,并且不得不单独打开每个工作簿来提交我需要的更改。
我避免与多个工作簿就像是一个痛苦的瘟疫工作,如果这是一个正在进行的要求,那么我会建议看回您的工作簿的设计,看是否可以合并回一个工作簿。我经常看到每个月都保存的工作簿,他们应该有一张工作簿,每张工作簿包含原始数据(其中每行代表一个月份),然后是另一张用于查看用户选择的原始数据的显示工作表。这是一个非常大的概括,你可能会处于完全不同的情况。
如果一次过 - 我知道它不是你想要的,但我认为你会通过使用VBA的工作簿最好的循环。喜欢的东西(未经测试):
Excel 2003中:
Sub AdjustMultipleFiles()
Dim lCount As Long
Dim wbLoopBook As Workbook
Dim wsLoopSheet As Worksheet
With Application
.ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
End With
With 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 If
End With
With Application
.ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
End With
End 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 With
End 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 With
End Sub
Excel 2007中没有Application.Filesearch。您可以使用Dir()或Scripting.FilesystemObject的实例。 – 2011-06-15 00:26:12
谢谢蒂姆。为2007+编辑。 – Reafidy 2011-06-15 01:25:50
当我需要为了遍历文件,我使用了thread on ozgrid中的一些代码,它使用了Dir命令,避免版本问题,同时保留使用通配符过滤文件名的功能。
Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
Dim sTemp As String, sHldr As String
If Right$(fldr, 1) <> "\" Then fldr = fldr & "\" 'append backslash if not already supplied
sTemp = Dir(fldr & fltr)
If sTemp = "" Then
FileList = False
Exit Function
End If
Do
sHldr = Dir
If sHldr = "" Then Exit Do
sTemp = sTemp & "|" & sHldr 'ensures an array is returned
Loop
FileList = Split(sTemp, "|")
End Function
从我这里得到一些代码在一起,利用它:
Sub MySub()
Dim vList As Variant
Dim myWB As Workbook
Dim targetDir As String
targetDir = "C:\path\to\folder\"
If Right$(targetDir, 1) <> "\" Then targetDir = targetDir & "\"
vList = FileList(targetDir, "*.xl*") 'all file extensions starting with xl (xls, xlsx, xlsm, xla, xlsb, etc.)
For n = LBound(vList) To UBound(vList)
Set myWB = Workbooks.Open(targetDir & vList(n))
'your code for each workbook here
myWB.Close
Next n
End Sub
您还可以使用PyWin32库脚本,这与使用典型的COM技术的Python。这使您可以使用Python进行处理,并且仍然保存其他Python Excel库可能无法处理的每个工作簿的所有额外部分。
你或许应该考虑自动化Excel做到这一点:你可以使用VBA或Python如果你更舒服。大多数在那里循环遍历目录中的文件的例子将在VBA中,尽管... – 2011-06-14 18:22:50
这不是VBA是用于*的吗? – 2011-06-14 18:59:34
你甚至可以记录你需要的大部分宏,然后整理它们。 – Fionnuala 2011-06-14 20:06:49