使用列中的值作为参数

Question

我需要从列中读取值，并将单个值作为函数的参数传递。下面是我的代码:

Sub Function3()
Dim FileFull As String
Dim wb As Workbook
Worksheets("Foglio3").Activate
FileFull = Dir(Range("A1").Value)
Do While FileFull <> ""
Set wb = Workbooks.Open(FileFull)
Delete_Sheet wb
InsertCol wb
wb.Close SaveChanges:=True
Filename = Dir()
Loop
End Sub

其他函数将使用存储在FileFull变量中的值。Foglio3上的值为:

C:scriptestFolderA20213-CorrsfileA_2021.xlsm
C:scriptestFolderB20213-CorrsfileB_2021.xlsm
C:scriptestFolderC20213-CorrsfileC_2021.xlsm
C:scriptestFolderD20213-CorrsfileD_2021.xlsm
C:scriptestFolderE20213-CorrsfileE_2021.xlsm

我有两个问题。我如何循环进入从A1到A300的Foglio3单元格的范围，并使用这些值作为其他函数的参数?谢谢!

Answer 1

处理多个工作簿

Option Explicit
Sub ProcessMultipleWorkbooks()
Const ProcTitle As String = "Process Multiple Workbooks"

Dim swb As Workbook: Set swb = ThisWorkbook ' workbook containing this code
Dim sws As Worksheet: Set sws = swb.Worksheets("Foglio3")
' Either...
Dim srg As Range: Set srg = sws.Range("A1:A300")
' ... or to make it dynamic:
'Dim slCell As Range: Set slCell = sws.Cells(sws.Rows.Count, "A").End(xlUp)
'Dim srg As Range: Set srg = sws.Range("A1", slCell)

Dim dwb As Workbook
Dim sCell As Range
Dim dFilePath As String

For Each sCell In srg.Cells
dFilePath = CStr(sCell.Value)
If Len(Dir(dFilePath)) > 0 Then ' destination file path exists
On Error Resume Next
Set dwb = Workbooks.Open(dFilePath)
On Error GoTo 0
If Not dwb Is Nothing Then ' file opened
' Debug.Print dwb.Name
Delete_Sheet dwb
InsertCol dwb
dwb.Close SaveChanges:=True
'Else ' file not opened
End If
'Else ' destination file path doesn't exist
End If
Next sCell

MsgBox "Done.", vbInformation, ProcTitle
End Sub