最近工作需要处理excel相关操作,写一个记录下
指定文件夹下的多个文件合并,并保存在指定文件夹下
代码如下:
Sub combinesheets()
Dim wb As Workbook
Dim ws As Worksheet
Dim openfile As String
Dim desfile As String
oppth = InputBox("请输入待处理文件夹路径")
openfile = Dir(oppth & "\*.csv")
desfile = InputBox("请输入保存文件路径")
'将指定文件夹下的文件合并到当前打开的文件中
Do While openfile <> ""
Set wb = Workbooks.Open(oppth & "\" & openfile)
For Each ws In ActiveWorkbook.Sheets
ws.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next ws
wb.Close
openfile = Dir
Loop
ThisWorkbook.Activate
'工作簿另存到指定路径,完整路径包含要保存的文件名
ThisWorkbook.SaveAs Filename:=desfile
End Sub