[問題] 工作表合併，不要放副檔名

看板Office作者SergioLeone (窮窮)時間1年前 (2023/03/05 03:30)推噓0(0推 0噓 1→)

留言1則, 1人參與討論串1/1

(若是和其他不同軟體互動之問題請記得一併填寫) 軟體: 版本: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呢拜託願贈特大星巴克予以解惑之人 ----- Sent from JPTT on my iPhone -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 39.14.26.114 (臺灣) ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1677958234.A.A2B.html ※ 編輯: SergioLeone (39.14.26.114 臺灣), 03/05/2023 03:33:33