从excel文件vba循环文件夹名称

debugcn 发表于 Dev

罗贝卡蒸汽

我有一个代码可以复制我想要的所需文件，这是代码

Dim saveFolder As String
Dim fname As String

saveFolder = "C:\Copied file"

folder = Workbooks("Macros.xlsb").Worksheets("folder").Range("A2")
FileName = Workbooks("Macros.xlsb").Worksheets("path").Range("B4")

Path = FileName & "\" & folder & "\Samples\*.xlsx"
file = Dir(Path)

Workbooks.Open Path

Sheets("Accounts").copy
ActiveWorkbook.SaveAs saveFolder & "\Accounts.xlsx", FileFormat:=51
Workbooks(file).Close

ActiveWorkbook.Close

它运行良好，但我需要在单元格列中手动输入文件夹名称。但是我每次只在 1 个文件夹的宏完成后单击，依此类推。

我只想知道如何循环它。

这将是我的文件夹名称的工作表（文件夹）：

FOLDER
45
118
180
290

我想为每个单元格循环宏。这样我就不需要一一单击/编辑值。因为文件夹名称可以立即更改。

皮肤

在你要求循环的问题范围内，你可以试试这个......

Dim saveFolder As String
Dim fname As String

Dim lastRow As Long
Dim i As Long

' Set this either statically or dynamically.
lastRow = 100
saveFolder = "C:\Copied file"

' Start from where you want either statically or dynamically.
For i = 4 To lastRow
    folder = Workbooks("Macros.xlsb").Worksheets("folder").Range("A2")
    Filename = Workbooks("Macros.xlsb").Worksheets("path").Range("B" & i)

    Path = Filename & "\" & folder & "\Samples\*.xlsx"
    file = Dir(Path)

    Workbooks.Open Path

    Sheets("Accounts").Copy
    ActiveWorkbook.SaveAs saveFolder & "\Accounts.xlsx", FileFormat:=51
    Workbooks(file).Close
Next

ActiveWorkbook.Close

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。