vba在特定文件夹中的特定文件类型 [英] specific file types in specific folders with vba

问题描述

我有几个文件夹，我希望此代码在开始和结束之间进行编辑。当Folder2为SubFolder替换Folder1时，在不同文件夹中循环搜索的代码部分看不到任何* .csv，但是如果我手动将SubFolder的初始条件从 Folder1手动更改为 Folder2 ，它将立即检测到该文件夹​​中的* .csv文件。当它们是SubFolder的初始条件时，还会在 Folder1和 Folder3中检测到* .csv。我确实按照其他问题的建议进行检查，并且在此代码中找不到任何缺少的 \

I have several folders that I want this code to edit inbetween start and finish. The part of the code that recycles the search in a different folder doesn't see any *.csv when Folder2 replaces Folder1 for SubFolder, but if the initial condition for SubFolder at the start is changed manually by me from "Folder1" to "Folder2" it will detect *.csv files in that folder now. It also detects *.csv in "Folder1" and "Folder3" when they are the initial condition for SubFolder. I did check as recommended in other questions and couldn't find any missing "\" in this code

Global Myfile, MyFolder, NewFile, SubFolder As String

Sub SpecificFileTypeInSpecificFolders()
'
SubFolder = "Folder1"
MyFolder = "C:\xxxxxx\" & SubFolder
Myfile = Dir(MyFolder & "\*.csv")
MsgBox SubFolder
MsgBox Myfile
Do While Myfile <> ""
MsgBox SubFolder
MsgBox Myfile
Myfile = Dir
    If Myfile = "" Then
        If SubFolder = "Folder2" Then 'several more folders like this
            SubFolder = "Folder3"
        End If
        If SubFolder = "Folder1" Then
            SubFolder = "Folder2"
        End If
    End If
    MsgBox SubFolder
    MsgBox Myfile
    Loop
End Sub

推荐答案

在代码行 MyFolder = C：\xxxxxx\& amp;之后更改 SubFolder 变量的值; SubFolder 对 MyFolder 变量无效。使用& 运算符连接字符串变量时，您将使用 SubFolder 变量的值并将其附加到 C：\xxxxxx\ 文本，并将结果放入 MyFolder 变量的值。

Changing the value of the SubFolder variable after the line of code MyFolder = "C:\xxxxxx\" & SubFolder has no effect on the MyFolder variable. When you concatenate string variables using the & operator you are taking the value of the SubFolder variable and appending it to the C:\xxxxxx\ text and putting the result into the value of the MyFolder variable.

如果要搜索已知的文件夹列表，请创建文件夹数组，然后遍历该数组。

If you want to search a known list of folders, create an array of the folders and then loop through the array.

避免使用 Globals -最佳做法是声明具有所需最小范围的变量。在VBA中，当您在同一行上声明多个变量时，必须指定每个变量的变量类型，否则它们将被定义为 Variant 。因此，在您的代码行中，仅 SubFolder 被定义为字符串：

Avoid using Globals - best practice is to declare variables with the minimum scope required. In VBA when you declare several variables on the same line you must specify the variable type of each one because otherwise they get defined as Variant. So in your line of code only SubFolder is defined as a string:

Global Myfile, MyFolder, NewFile, SubFolder As String

代替使用：

Dim Myfile As String, MyFolder As String, NewFile As String, SubFolder As String

个人而言，我更喜欢将每个变量声明放在单独的行上。

Personally, I prefer to keep each variable declaration on a separate line.

这段代码应该可以正常运行：

This code should run ok:

Sub SpecificFileTypeInSpecificFolders()
'
Dim myFile As String
Dim subFolder As Variant ' Must be variant to enable looping through the array
Dim folderNames As Variant
Dim mainFolder As String

    folderNames = Array("Folder1", "Folder2", "Folder3")
    mainFolder = "C:\xxxxxx\"

    For Each subFolder In folderNames
        MsgBox subFolder
        myFile = Dir(mainFolder & subFolder & "\*.csv")
        Do While myFile <> ""
            MsgBox myFile
            myFile = Dir
        Loop
    Next subFolder

End Sub

这篇关于vba在特定文件夹中的特定文件类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！