全面自動化的Excel VBA腳本指南

提升效率，無縫整合資料，全面掌控Excel工作流程

關鍵要點

動態處理檔案名稱與日期轉換：確保VBA腳本能靈活應對檔案名稱中的日期變化。
自動化資料複製與貼上：高效將選取的資料從來源工作簿轉移至目標工作簿的指定位置。
整合網頁資料與執行後續分析：自動從網頁抓取內容並進行資料剖析，最終開啟相關CSV檔案。

VBA腳本概述

本指南將提供一個全面的VBA腳本，用於自動化一系列Excel操作。此腳本包括動態處理檔案名稱、資料複製貼上、執行外部模組的子程序、從網頁提取內容並貼上至指定工作表，以及最終開啟CSV檔案。以下將逐步解析每個步驟，並提供完整的VBA代碼範例。

詳細步驟解析

1. 動態處理檔案名稱與日期轉換

由於來源檔案的名稱中包含日期，且日期可能隨時變動，因此需要在VBA腳本中動態獲取當前日期，並生成對應的檔案名稱。民國年份的轉換也是關鍵步驟，需將西元年轉換為民國年，並以三位數表示。

日期處理與檔案名稱生成

使用VBA內建的日期函數來獲取當前日期，並格式化為所需的格式。以下是核心代碼片段：


Dim currentDate As Date
Dim gregorianYear As Integer
Dim rocYear As String
Dim fileDate As String
Dim sourceFileName As String
Dim targetFileName As String

currentDate = Date
gregorianYear = Year(currentDate)
rocYear = Format(gregorianYear - 1911, "000")
fileDate = Format(currentDate, "yyyymmdd")

sourceFileName = "Bloomberg 報價-" & Format(currentDate, "YYYYMMDD") & ".xlsm"
targetFileName = "基金淨值覆核表-" & rocYear & Format(currentDate, "MMDD") & ".xlsm"

2. 資料複製與貼上

在確保檔案名稱正確後，腳本將打開來源工作簿，複製當前選取的資料，並將其貼上至目標工作簿的指定單元格。

打開工作簿與複製資料

以下代碼展示如何打開工作簿並進行資料複製貼上：


Dim wbSource As Workbook
Dim wbTarget As Workbook

Set wbSource = Workbooks.Open(ThisWorkbook.Path & "\" & sourceFileName)
Set wbTarget = Workbooks.Open(ThisWorkbook.Path & "\" & targetFileName)

' 複製選取的範圍並貼上值至目標工作簿的E5單元格
wbSource.ActiveSheet.Selection.Copy
With wbTarget.Worksheets("基金淨值覆核表")
    .Range("E5").PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False

3. 執行外部模組的子程序

完成資料貼上後，需要執行目標工作簿中的Module21模組下的Sub 清除()來清理或處理資料。

調用外部子程序

使用Application.Run方法來執行目標工作簿中的子程序：


Application.Run "'" & wbTarget.Name & "'!Module21.清除"

4. 從網頁提取內容並貼上至指定工作表

根據「fundDJ網址」工作表中儲存的URL，腳本將自動訪問這些網頁並將內容貼上至對應的工作表。

網頁內容的自動提取與貼上

使用IE自動化對象來訪問網頁並提取內容：


Dim wsFundDJ As Worksheet
Dim wsTargetSheet As Worksheet
Dim url As String
Dim ie As Object

Set wsFundDJ = wbTarget.Worksheets("fundDJ網址")

For i = 2 To 5
    Select Case i
        Case 2
            Set wsTargetSheet = wbTarget.Worksheets("T-1 境外")
        Case 3
            Set wsTargetSheet = wbTarget.Worksheets("T-2 境外")
        Case 4
            Set wsTargetSheet = wbTarget.Worksheets("T-1 境內")
        Case 5
            Set wsTargetSheet = wbTarget.Worksheets("T-2 境內")
    End Select
    
    url = wsFundDJ.Range("C" & i).Value
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False
    ie.navigate url
    
    ' 等待網頁加載完成
    Do While ie.Busy Or ie.readyState <> 4
        DoEvents
    Loop
    
    ' 將網頁內容貼上至目標工作表的A1單元格
    wsTargetSheet.Range("A1").Value = ie.document.body.innerText
    
    ie.Quit
    Set ie = Nothing
Next i

5. 執行資料剖析子程序並處理詢問窗口

對於每個目標工作表，執行Module22模組下的Sub 資料剖析()，並自動處理任何詢問窗口，確保腳本能夠順利執行。

自動處理詢問窗口

利用Application.DisplayAlerts來自動確認或忽略提示：


Dim sheetNames As Variant
Dim sheetName As Variant

sheetNames = Array("T-1 境外", "T-2 境外", "T-1 境內", "T-2 境內")

For Each sheetName In sheetNames
    With wbTarget.Worksheets(sheetName)
        .Activate
        Application.DisplayAlerts = False
        Application.Run "'" & wbTarget.Name & "'!Module22.資料剖析"
        Application.DisplayAlerts = True
    End With
Next sheetName

6. 開啟指定的CSV檔案

最後，腳本將跳轉回「基金淨值覆核表」工作表，並開啟指定路徑的CSV檔案。

跳轉工作表與開啟CSV檔案

使用Activate方法切換工作表，並利用Workbooks.Open方法開啟CSV檔案：


' 切換至基金淨值覆核表工作表
wbTarget.Worksheets("基金淨值覆核表").Activate

' 開啟CSV檔案
Workbooks.Open "C:\Users\VILEE00\Downloads\FundPriceTop.csv"

完整VBA腳本範例

以下是整合上述所有步驟的完整VBA腳本：


Sub 自動化流程()
    Dim currentDate As Date
    Dim gregorianYear As Integer
    Dim rocYear As String
    Dim fileDate As String
    Dim sourceFileName As String
    Dim targetFileName As String
    Dim wbSource As Workbook
    Dim wbTarget As Workbook
    Dim wsFundDJ As Worksheet
    Dim wsTargetSheet As Worksheet
    Dim url As String
    Dim ie As Object
    Dim sheetNames As Variant
    Dim sheetName As Variant
    Dim i As Integer
    
    ' 1. 日期處理與檔案名稱生成
    currentDate = Date
    gregorianYear = Year(currentDate)
    rocYear = Format(gregorianYear - 1911, "000")
    fileDate = Format(currentDate, "yyyymmdd")
    
    sourceFileName = "Bloomberg 報價-" & Format(currentDate, "YYYYMMDD") & ".xlsm"
    targetFileName = "基金淨值覆核表-" & rocYear & Format(currentDate, "MMDD") & ".xlsm"
    
    ' 2. 打開來源與目標工作簿
    On Error GoTo ErrHandler
    Set wbSource = Workbooks.Open(ThisWorkbook.Path & "\" & sourceFileName)
    Set wbTarget = Workbooks.Open(ThisWorkbook.Path & "\" & targetFileName)
    
    ' 3. 複製選取的範圍並貼上值至目標工作簿的E5單元格
    wbSource.ActiveSheet.Selection.Copy
    With wbTarget.Worksheets("基金淨值覆核表")
        .Range("E5").PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
    
    ' 4. 執行清除子程序
    Application.Run "'" & wbTarget.Name & "'!Module21.清除"
    
    ' 5. 從fundDJ網址工作表中提取URL並貼上至指定工作表
    Set wsFundDJ = wbTarget.Worksheets("fundDJ網址")
    
    For i = 2 To 5
        Select Case i
            Case 2
                Set wsTargetSheet = wbTarget.Worksheets("T-1 境外")
            Case 3
                Set wsTargetSheet = wbTarget.Worksheets("T-2 境外")
            Case 4
                Set wsTargetSheet = wbTarget.Worksheets("T-1 境內")
            Case 5
                Set wsTargetSheet = wbTarget.Worksheets("T-2 境內")
        End Select
        
        url = wsFundDJ.Range("C" & i).Value
        Set ie = CreateObject("InternetExplorer.Application")
        ie.Visible = False
        ie.navigate url
        
        ' 等待網頁加載完成
        Do While ie.Busy Or ie.readyState <> 4
            DoEvents
        Loop
        
        ' 將網頁內容貼上至目標工作表的A1單元格
        wsTargetSheet.Range("A1").Value = ie.document.body.innerText
        
        ie.Quit
        Set ie = Nothing
    Next i
    
    ' 6. 執行資料剖析子程序並自動處理詢問窗口
    sheetNames = Array("T-1 境外", "T-2 境外", "T-1 境內", "T-2 境內")
    
    For Each sheetName In sheetNames
        With wbTarget.Worksheets(sheetName)
            .Activate
            Application.DisplayAlerts = False
            Application.Run "'" & wbTarget.Name & "'!Module22.資料剖析"
            Application.DisplayAlerts = True
        End With
    Next sheetName
    
    ' 7. 切換至基金淨值覆核表工作表並開啟CSV檔案
    wbTarget.Worksheets("基金淨值覆核表").Activate
    Workbooks.Open "C:\Users\VILEE00\Downloads\FundPriceTop.csv"
    
    ' 完成提示
    MsgBox "自動化流程已成功完成！", vbInformation
    
    Exit Sub
    
ErrHandler:
    MsgBox "發生錯誤：" & Err.Description, vbCritical
    Application.DisplayAlerts = True
End Sub