在Excel中循环记录的宏

Question

我不熟悉VBA，所以请原谅这个问题的简单性。我有一个录制的宏，它从我的一列中的超链接中选择、打开并保存文件。我只想做一个循环，在工作表中所有有数据的行中重复这个宏。下面是录制宏的代码，感谢大家的帮助。

Sub Extract()
'
'Extract Macro
'
'
Range("D2").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Workbooks.Open Filename:= _
"https://channele.corp.etradegrp.com/communities/teams02/performance-monitoring/TPEF%20Library/A2Consulting_Tech_5650_VSAF.xlsm"
ActiveWindow.Visible = False
Windows("A2Consulting_Tech_5650_VSAF.xlsm").Visible = True
ChDir "O:Procurement PlanningQA"
ActiveWorkbook.SaveAs Filename:= _
"O:Procurement PlanningQACopy of A2Consulting_Tech_5650_VSAF.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWindow.Close
End Sub 

Answer 1

类似的东西可能已经起作用了：

Sub Extract()

Dim RngTarget As Range
Dim StrFileName As String

Set RngTarget = Range("D2")

Do Until RngTarget.Value = ""

RngTarget.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Workbooks.Open Filename:=RngTarget.Value
StrFileName = Split(RngTarget.Value, "/")(UBound(Split(RngTarget.Value, "/")))
Windows(StrFileName).Visible = True
Workbooks(StrFileName).SaveAs Filename:="O:Procurement PlanningQACopy of " & Split(StrFileName, ".")(0) & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
Workbooks(StrFileName).Close

Set RngTarget = RngTarget.Offset(1, 0)
Loop

End Sub