萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 辦公室軟體 >  >> Excel 教學 >> Excel 綜合

VBA 將指定區域另存為獨立 XLS 活頁簿檔案

VBA 自動化匯出:複製工作表並清理後另存為 .xls

將「報表」工作表複製為新活頁簿,刪除前兩列、設定欄位格式、寫入輔助陣列資料、移除圖形物件,最後以 A1 值為檔名儲存為 Excel 97-2003 格式(.xls)。

完整程式碼解析

Dim wn As String, shp As Shape, arr As Variant

Application.ScreenUpdating = False
Application.DisplayAlerts = False

wn = Range("A1").Value          ' 取檔名
arr = Range("O3:O" & Range("O65536").End(xlUp).Row).Value  ' 讀取 O 欄資料陣列

Sheets("報表").Copy             ' 複製整張工作表到新活頁簿

With ActiveWorkbook
    With .Sheets(1)
        .Rows("1:2").Delete Shift:=xlUp      ' 刪除前兩列
        .Range("D:D,P:P").NumberFormatLocal = "@"  ' D、P 欄設為文字格式
        .Range("O1").Resize(UBound(arr, 1)).Value = arr  ' 寫入 O 欄陣列資料
        
        .UsedRange.Value = .UsedRange.Value  ' 公式轉值(去除公式保留結果)
        
        For Each shp In .Shapes
            shp.Delete                      ' 移除所有圖形物件
        Next shp
    End With
    
    ' 相容版本儲存:Excel 2003(版本<=11) 用 xlNormal,否則 xlExcel8 (.xls)
    .SaveAs ThisWorkbook.Path & "\" & wn & ".xls", _
            IIf(Application.Version <= 11, xlNormal, xlExcel8)
    .Close False
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "ok"

關鍵技術點

  1. Sheets("報表").Copy 無參數時自動建立新活頁簿並複製該工作表。
  2. NumberFormatLocal = "@" 強制文字格式,防止長數字(身分證、條碼)變科學記號。
  3. UsedRange.Value = UsedRange.Value 經典「公式轉值」技巧,移除所有公式保留計算結果。
  4. IIf(Application.Version <= 11, xlNormal, xlExcel8) 相容 Excel 2003 與 2007+ 的 .xls 儲存格式常數。
  5. 關閉 ScreenUpdating 與 DisplayAlerts 提升速度並抑制彈窗(如覆蓋確認)。

擴充建議

  • 檔名加入時間戳記避免覆蓋:wn & Format(Now, "yyyymmdd_hhnnss")。
  • 輸出格式改為 xlOpenXMLWorkbook (.xlsx) 以支援大資料量。
  • 加入錯誤處理 On Error GoTo ErrHandler 確保 ScreenUpdating 恢復。
copyright © 萬盛學電腦網 all rights reserved