excel将多个工作簿合并到一个工作簿中

excel想跨文件批量替换，没整明白

准备excel将多个工作簿合并到一个工作簿的不同工作表中，然后再替换

一、Excel宏代码

（一）转换文件格式代码

ren *.xlsx *.xls

（二）宏代码

Sub merge()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim newwb As Workbook
Set newwb = Workbooks.Add
With fd
If .Show = -1 Then
Dim vrtSelectedItem As Variant
Dim i As Integer
i = 1
For Each vrtSelectedItem In .SelectedItems
Dim tempwb As Workbook
Set tempwb = Workbooks.Open(vrtSelectedItem)
tempwb.Worksheets(1).Copy Before:=newwb.Worksheets(i)
newwb.Worksheets(i).Name = VBA.Replace(tempwb.Name, ".xlsx", "")
tempwb.Close SaveChanges:=False
i = i + 1
Next vrtSelectedItem
End If
End With
Set fd = Nothing
End Sub

 

 

二、WPS宏代码

（一）转换文件格式代码

ren *.xls *.et
ren *.xlsx *.et

（二）宏代码

function 合并工作簿中第一个工作表(){
var twb=ThisWorkbook;
var pah=twb.Path;
var str=pah+"\\*.et";
var fn=Dir(str);
while(fn!=twb.Name){
Workbooks.Open(pah+"\\"+fn);
var awb=ActiveWorkbook
awb.Sheets(1).Copy(null,twb.Sheets(twb.Sheets.Count));
awb.Close(false);
try{
fn=Dir();
}
catch(err){break}
}
}