我拼凑了一个 Excel VBA 脚本,该脚本将打开的工作簿中的所有工作表写入单独的制表符分隔文件(这仍然是“宏”吗?我正在 Excel 真空中学习这一点)。它一次只处理一本工作簿,效果很好。这里是。
Sub exportSheetsToText()
Dim sWb As String
Dim sFile As String
Dim oSheet As Worksheet
sWb = Left(ActiveWorkbook.FullName, InStr(ActiveWorkbook.FullName, ".") - 1)
For Each oSheet In Worksheets
oSheet.Copy
sFile = sWb & "-" & oSheet.Name & ".txt"
ActiveWorkbook.SaveAs fileName:=sFile, FileFormat:=xlText
ActiveWorkbook.Close SaveChanges:=False
Next oSheet
End Sub
我想扩大规模,以便我可以将此宏应用于工作簿文件夹。我编写了我认为会循环遍历满足过滤器的每个工作簿的内容,但它不写入任何 .txt 文件。这里是。
Sub exportsSheetsToTextForAll()
Dim sPath As String
Dim sWildcard As String
Dim sMacro As String
Dim oWb As Workbook
Dim oPersWb As Workbook
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Set oPersWb = Workbooks("PERSONAL.XLSB")
sMacro = "'" & oPersWb.Name & "'" & "!exportSheetsToText()"
sPath = "C:\Users\richard\Documents\Research\Data\Excel\Datastream - payout"
sWildcard = "New*.xlsx"
sFile = Dir(sPath & "\" & sWildcard)
Do While Len(sFile) > 0
Workbooks.Open Filename:=sPath & "\" & sFile
Application.Run sMacro
ActiveWorkbook.Close SaveChanges:=False
sFile = Dir
Loop
End Sub
它循环遍历我的所有测试文件,但我没有看到任何效果(即没有 .txt 文件,也没有错误)。
最终,我将在带有宏的非常大的工作簿上运行它,因此禁用宏(我本地没有宏,它们位于专用数据机上)并在打开下一个大型工作簿之前关闭一个大型工作簿非常重要。
有任何想法吗?谢谢!
@Siddarth 将参数传递给的想法exportSheetsToText()
是关键。我也遇到了宏名称传递给的错误Application.Run
。下面的方法有效并且更加干净。
Sub exportsSheetsToTextForAll()
Application.AutomationSecurity = msoAutomationSecurityForceDisable
excelFiles = Dir(ThisWorkbook.Path & "\" & "New*.xlsx")
fromPath = ThisWorkbook.Path
Do While Len(excelFiles) > 0
Debug.Print Files
Set oWb = Workbooks.Open(Filename:=fromPath & "\" & excelFiles)
Application.Run "exportSheetsToText", oWb
oWb.Close SaveChanges:=False
excelFiles = Dir
Loop
End Sub
Sub exportSheetsToText(iWb As Workbook)
For Each ws In iWb.Worksheets
ws.Copy
Set wb = ActiveWorkbook
textFile = Left(iWb.FullName, InStr(iWb.FullName, ".") - 1) & "-" & ws.Name & ".txt"
wb.SaveAs Filename:=textFile, FileFormat:=xlText
wb.Close SaveChanges:=False
Next ws
End Sub
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)