文件夹选择对话框（VBA、Excel 2010）导致文件不可读

Folder Selection Dialog Box (VBA, Excel 2010) causing files to be unreadable

提问by xkoh

This may be quite a lengthy description, so please bear with me. The problems I am experiencing with Excel are related to custom macros, VBA, and file access.

这可能是一个很长的描述，所以请耐心等待。我在 Excel 中遇到的问题与自定义宏、VBA 和文件访问有关。

Background: I am trying to write a macro to process multiple CSV workbooks of data. I have files named RawData_1.csv until RawData_x.csv where x is the number of files I have in a particular folder.

背景：我正在尝试编写一个宏来处理多个 CSV 数据工作簿。我有名为 RawData_1.csv 的文件，直到 RawData_x.csv，其中 x 是我在特定文件夹中的文件数。

My code for the macro looks like this:

我的宏代码如下所示：

Sub ImportData()

    Application.ScreenUpdating = False

    Dim strDir As String
    Dim strFileName As String
    Dim wbToCopy As Workbook
    Dim intCol As Integer

    Set master = ActiveSheet

    **PLEASE SEE BELOW FOR 2 VERSIONS OF CODE THAT CAN GO HERE!**

    strFileName = Dir(strDir & "\*.csv")

    intCol = 2

    Do While Len(strFileName) > 0
        Set wbToCopy = Workbooks.Open(strFileName, , True)

        //Do other things I need it to do here

        wbToCopy.Close (False)

        strFileName = Dir
        intCol = intCol + 2
    Loop

    Application.ScreenUpdating = True

End Sub

Problem 1 (tiny problem): When I execute the macro, the files somehow "change," and the next time I execute the same macro in the same folder, it will report that the files are not found. The exact error is:

问题1（小问题）：当我执行宏时，文件不知何故“改变”，下次我在同一个文件夹中执行相同的宏时，它会报告找不到文件。确切的错误是：

Run-time error '1004':

'RawData_1.csv' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files, make sure that the file has now been renamed, moved, or deleted.

运行时错误“1004”：

找不到“RawData_1.csv”。检查文件名的拼写，并验证文件位置是否正确。

如果您尝试从最近使用的文件列表中打开该文件，请确保该文件现在已被重命名、移动或删除。

I have figured out a solution to this problem. All I have to do is go into the folder with all the csv files, open the FIRST one in the list, and "Save As" as a MS-DOS CSV file. Once I have done this, I can run the macro and it will be able to open ALL the files (not just the first file that I "save as"-ed).

我已经找到了解决这个问题的方法。我所要做的就是进入包含所有 csv 文件的文件夹，打开列表中的第一个，然后“另存为”作为 MS-DOS CSV 文件。完成此操作后，我可以运行宏，它将能够打开所有文件（不仅仅是我“另存为”-ed 的第一个文件）。

While this is annoying, it's not the worst thing in the world. If there is a reason why excel is doing this, I would love to know! If there is a solution to this problem, even better!

虽然这很烦人，但这并不是世界上最糟糕的事情。如果excel这样做有什么原因，我很想知道！如果这个问题有解决方案就更好了！

Problem 2 (Big problem)This is the main puzzle that I would like to solve. In the code above, the section that is missing is the part of the code that tells Excel (or the macro) where to find the files. I can do this by hardcoding in the path as follows:

问题 2（大问题）这是我想解决的主要难题。在上面的代码中，缺少的部分是告诉 Excel（或宏）在何处查找文件的代码部分。我可以通过在路径中硬编码来做到这一点，如下所示：

Method 1:

方法一：

strDir = "C:\whateverPath"

This method ALWAYS works (except when Problem 1 is encountered above).

此方法始终有效（除非遇到上述问题 1）。

However, this is clearly not the best way to write the macro, since I will not only be using it once, but will need to use it multiple times, and the data files I wish to import will be in various folders. Therefore, I tried to write it as follows:

但是，这显然不是编写宏的最佳方式，因为我不仅会使用它一次，还需要多次使用它，而且我希望导入的数据文件将位于不同的文件夹中。因此，我尝试将其编写如下：

Method 2:

方法二：

Dim folderDialog As fileDialog 
Set folderDialog = Application.FileDialog(msoFileDialogeFolderPicker)
folderDialog.AllowMultiSelect = False
folderDialog.Show

strDir = folderDialog.SelectedItems(1)

I compared strDir from Method 1 and strDir from Method 2 and found no discernable differences in their values. They both contain the correct path "C:\whateverPath".

我比较了方法 1 中的 strDir 和方法 2 中的 strDir，发现它们的值没有明显差异。它们都包含正确的路径“C:\whateverPath”。

However, using Method 2, excel will be unable to read any of the files in the selected folder. It will return with The same run-time error 1004 as above, and the quick fix I found for Problem 1 above does nothing to help the macro run.

但是，使用方法 2，excel 将无法读取所选文件夹中的任何文件。它将返回与上述相同的运行时错误 1004，并且我为上述问题 1 找到的快速修复对宏运行没有任何帮助。

If anyone has any idea what is going on here, I would really appreciate some help figuring this out!

如果有人知道这里发生了什么，我真的很感激帮助解决这个问题！

采纳答案by UberNubIsTrue

Edit:I think I found the issue. Set wbToCopy = Workbooks.Open(strFileName, , True) , strFileNamedoes not use the fully qualified path. So when you call the .openmethod, I believe VBA is using the CurDirvalue and appending it to strFileName. When you perform your "Save As", the CurDirvalue is changed to the directory you are saving the .csv files in. This gives the illusion that the "Save As" action is what allows your macro to run. In actuality, it is the act of changing the CurDirvalue to the directory where the files are located. Use a fully qualified file name with .openand it should run everytime.

编辑：我想我发现了这个问题。Set wbToCopy = Workbooks.Open(strFileName, , True) ,strFileName不使用完全限定的路径。因此，当您调用该.open方法时，我相信 VBA 正在使用该CurDir值并将其附加到strFileName. 当您执行“另存为”时，该CurDir值将更改为您保存 .csv 文件的目录。这给人一种错觉，即“另存为”操作允许您的宏运行。实际上，这是将CurDir值更改为文件所在目录的行为。使用完全限定的文件名，.open它应该每次都运行。

Previous Suggestion:I don't think your file name is full qualified (your error message should read 'C:\whateverPath\RawData_1.csv' could not be found.not 'RawData_1.csv' could not be found.).

I was having a tough time tracking down the error in the code. This is kind of hacked together but if you get stuck, try using this:

以前的建议：我认为您的文件名不是完全限定的（您的错误消息应该是'C:\whateverPath\RawData_1.csv' could not be found.not 'RawData_1.csv' could not be found.）。

我很难追踪代码中的错误。这是一种黑客攻击，但如果您遇到困难，请尝试使用以下方法：

Option Explicit
Sub ImportData()

Application.ScreenUpdating = False

Dim strDir As String
Dim strFolderName As String
Dim wbToCopy As Workbook
Dim intCol As Integer
Dim master As Excel.Worksheet

Dim FSO As Object
Dim FSO_FOLDER As Object
Dim FSO_FILE As Object
Dim FILE_EXT As String

FILE_EXT = "csv"
strFolderName = Get_Folder_Path() & "\"

''Create FileSystem Objects
Set FSO = CreateObject("Scripting.FileSystemObject")

Set FSO_FOLDER = FSO.GetFolder(strFolderName)

Set master = ThisWorkbook.ActiveSheet

''**PLEASE SEE BELOW FOR 2 VERSIONS OF CODE THAT CAN GO HERE!**

intCol = 2

If FSO_FOLDER.Files.Count > 0 Then

''Loop through each File in Folder
For Each FSO_FILE In FSO_FOLDER.Files

   ''Test extension
   If FSO.GetExtensionName(FSO_FILE.Name) = FILE_EXT Then

       Set wbToCopy = Workbooks.Open(strFolderName & FSO_FILE.Name, , True)

        ''//Do other things I need it to do here

        wbToCopy.Close (False)
        intCol = intCol + 2

   Else: End If

Next

Else

MsgBox "No Files Found at " & strFolderName

End If

Set FSO = Nothing
Set FSO_FOLDER = Nothing

Application.ScreenUpdating = True

End Sub

Function Get_Folder_Path() As String
Dim folderDialog As FileDialog
Set folderDialog = Application.FileDialog(4)
folderDialog.AllowMultiSelect = False
folderDialog.Show

Get_Folder_Path = folderDialog.SelectedItems(1)

End Function

Note that this uses the FileSystem library instead of the native Dirfunction. You also will select the folder name instead of a file name for the dialog box.

请注意，这使用 FileSystem 库而不是本机Dir函数。您还将为对话框选择文件夹名称而不是文件名。