打开Excel文件,修改它并将其保存回原点。 [英] Open an Excel file, modify it and save it back to its origin.
本文介绍了打开Excel文件,修改它并将其保存回原点。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
亲爱的,
我想浏览特定文件夹中的现有Excel文件并打开 其中之一,更改Excel文件中的内容并将其写回具有相同文件名的原始文件夹。我是怎么做到的?
非常感谢
Michael
解决方案
Mac Mac,
您将使用FSO(FileSystemObject)扫描文件夹。找到所需文件后,进行必要的更改并关闭图书(savechanges = True)。
Sub UpdateExcelFilesInFolder()
Dim oExcel 作为 Excel.Application
Dim oWorkbook 作为 Excel.Workbook
Dim fso As Scripting.FileSystemObject
Dim oFolder As Scripting.folder
Dim oFile As Scripting.File
Dim sFolder As String
设置 fso = 新 Scripting.FileSystemObject
sFolder = " c:\"
设置 oFolder = fso.GetFolder(sFolder)
设置 oExcel = 新 Excel.Application
对于 每个 oFile 在 oFolder中。文件
    '对于两个Excel 2003 / Excel 2007文件都返回true
    如果 InStr(1,Right(oFile.Name,4)," xls" , vbTextCompare)> 0 然后
        设置 oWorkbook = oExcel.Workbooks.Open(sFolder& oFile.Name)
        '进行更改,例如
        'oWorkbook.Sheets(1).Cells(1,1).Value =" 1"
        oWorkbook.Close True
    结束 如果
下一步 oFile
设置 oFile = 没什么
设置 oFolder = Nothing
设置 fso = Nothing
设置 oExcel = 没什么  
结束 Sub
Dear all,
I want to browse through existing Excel files in a specific folder and open one of them, change something in that Excel file and write it back into the original folder with the same file name. How do I do that?
Thanks a lot
Michael
解决方案Hi Mac,
You'll use FSO (FileSystemObject) to scan a folder. Once you find the file you want, do the required changes and close the book (savechanges = True).
Sub UpdateExcelFilesInFolder() Dim oExcel As Excel.Application Dim oWorkbook As Excel.Workbook Dim fso As Scripting.FileSystemObject Dim oFolder As Scripting.folder Dim oFile As Scripting.File Dim sFolder As String Set fso = New Scripting.FileSystemObject sFolder = "c:\" Set oFolder = fso.GetFolder(sFolder) Set oExcel = New Excel.Application For Each oFile In oFolder.Files 'Returns true for both Excel 2003 / Excel 2007 files If InStr(1, Right(oFile.Name, 4), "xls", vbTextCompare) > 0 Then Set oWorkbook = oExcel.Workbooks.Open(sFolder & oFile.Name) 'Do your change, like 'oWorkbook.Sheets(1).Cells(1, 1).Value = "1" oWorkbook.Close True End If Next oFile Set oFile = Nothing
Set oFolder = Nothing Set fso = Nothing Set oExcel = Nothing End Sub
这篇关于打开Excel文件,修改它并将其保存回原点。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文