(若是和其他不同軟體互動之問題 請記得一併填寫)
軟體:
版本:excel xlsx
簡單描述一下狀況好
我有一個資料夾
裡面有二十個excel
每個excel都有五個工作表
分別是 資產 負債 權益 資產分析 負債分析
我輸入以下VBA
把每個excel裡面的‘’資產分析‘’
抓出來合併到一個excel
合併後的excel會有20個工作表
工作表名稱以原本二十個excel的名稱命名
但是後面會加上.xlsx
VBA代碼：將工作簿的指定工作表合併到主工作簿中：
Sub MergeSheets2()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next
xStrPath = " C:\Users\a0921\Desktop\科餘\"
xStrName = "資產分析"
xArr = Split(xStrName, ",")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.count)
xMWS.Name = xStrAWBName
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
這樣合併完之後
每個工作表的名稱都會帶有.xlsx
我還需要一個一個刪除
想請問高手如何不要有.xlsx呢
拜託 願贈特大星巴克予以解惑之人