VBA與ChatGPT聯(lián)手，輕松實現(xiàn)Excel數(shù)據(jù)提取與匯總！
完整代碼如下：
Sub SalesSummary()
??Dim ws As Worksheet
??Dim summaryWs As Worksheet
??Dim wb As Workbook
??Dim myPath As String
??Dim myFile As String
??Dim myExtension As String
??Dim targetRow As Long
???
??'定義目標(biāo)工作簿和工作表
??Set summaryWs = ThisWorkbook.Sheets("Sheet1")
???
??'定義文件路徑和文件類型
??myPath = "C:\Users\Administrator\Desktop\DEMO\每月銷售數(shù)據(jù)\"
??myExtension = "*.xlsx"
???
??'目標(biāo)行從第2行開始（假設(shè)第1行是標(biāo)題）
??targetRow = 2
???
??'獲取文件夾中的第一個文件
??myFile = Dir(myPath & myExtension)
???
??'循環(huán)，直到?jīng)]有文件
??Do While myFile <> ""
?????
????'打開文件
????Set wb = Workbooks.Open(Filename:=myPath & myFile)
?????
????'設(shè)置工作表為 "銷售數(shù)據(jù)"
????Set ws = wb.Sheets("銷售數(shù)據(jù)")
?????
????'從"銷售數(shù)據(jù)"工作表復(fù)制數(shù)據(jù)到"銷售匯總表"
????summaryWs.Cells(targetRow, "A").Value = ws.Range("A2").Value
????summaryWs.Cells(targetRow + 1, "A").Value = ws.Range("A3").Value
????summaryWs.Cells(targetRow + 2, "A").Value = ws.Range("A4").Value
????summaryWs.Cells(targetRow, "B").Value = ws.Range("B2").Value
????summaryWs.Cells(targetRow + 1, "B").Value = ws.Range("B3").Value
????summaryWs.Cells(targetRow + 2, "B").Value = ws.Range("B4").Value
????summaryWs.Cells(targetRow, "C").Value = myFile
????summaryWs.Cells(targetRow + 1, "C").Value = myFile
????summaryWs.Cells(targetRow + 2, "C").Value = myFile
?????
????'關(guān)閉工作簿，不保存更改
????wb.Close SaveChanges:=False
?????
????'獲取下一個文件
????myFile = Dir
?????
????'準(zhǔn)備下一行
????targetRow = targetRow + 3
??Loop
End Sub
