Excel VBA，导入文件夹中所有文件的名称，而无需使用Application.FileDialog()

Question

我有一个文件夹(每次都是同一个文件夹 - 所以我不需要浪费时间使用Application.FileDialog选择它(，我需要将所有文件名提取到Excel列C中。

这是我在 application.FileDialog(( 的堆栈溢出上找到的代码，但我想对文件夹的路径进行硬编码(C：\Users\michal\SkyDrive\csv\bossa\mstcgl_csv(。

我的另一个问题(重要(xDirectory$，xFname$和InitialFoldr$变量末尾的$符号是什么，为什么我不能将它们声明as String？ ？这些变量不是字符串吗？

？

这是代码：

Sub GetFileNames()    
  Dim Lista As Worksheet              
  Dim xRow As Long                     
  Dim xDirectory$                     
  Dim xFname$                         
  Dim InitialFoldr$
  Dim start As Double    
  Dim finish As Double    
  Dim total_time As Double
  start = Timer              ' remember time when macro starts.
     ThisWorkbook.Sheets("Lista").Range("C1").Select
     InitialFoldr$ = "C:UsersmichalSkyDrivecsvbossa"
     With Application.FileDialog(msoFileDialogFolderPicker)
         .InitialFileName = Application.DefaultFilePath & ""
         .Title = "Please select a folder to list Files from"
         .InitialFileName = InitialFoldr$
         .Show                           ' creates list of files  ? ? ?
          If .SelectedItems.Count <> 0 Then                  
                xDirectory$ = .SelectedItems(1) & ""  
                xFname$ = Dir(xDirectory$, 7)
                    Do While xFname$ <> ""
                         ThisWorkbook.Sheets("Lista").ActiveCell.Offset(xRow, 0) = xFname$
                         ActiveCell.Offset(xRow) = xFname$                         
                         xRow = xRow + 1             
                         xFname$ = Dir               
                    Loop                            
         End If
    End With
   finish = Timer                   ' Set end time.   
  total_time = Round(finish - start, 3)    ' Calculate total time.   
  MsgBox "This code ran successfully in " & total_time & " seconds", vbInformation
End Sub

你们能帮我做吗？我只是在学习我的 VBA 基础知识，但我仍然不明白很多东西。请回答$ 符号问题：-(

Answer 1

废弃FileDialog并直接使用 Dir 函数：

Sub GetFileNames()
    Const InitialFoler As String = "C:UsersmichalSkyDrivecsvbossa"
    Dim Lista As Worksheet
    Dim filename As String
    Dim xRow As Long
    Dim start As Double, finish As Double, total_time As Double
    start = Timer              ' remember time when macro starts.
    xRow = 1
    filename = Dir(InitialFoler & "*.*")
    With ThisWorkbook.Sheets("Lista")
        Do While Len(filename) > 0
            .Range("C" & xRow).Value = filename
            xRow = xRow + 1
            filename = Dir
        Loop
    End With
    finish = Timer                   ' Set end time.
    total_time = Round(finish - start, 3)    ' Calculate total time.
    MsgBox "This code ran successfully in " & total_time & " seconds", vbInformation
End Sub

您可以在此处阅读有关Dir函数的更多信息。

Answer 2

此行将 Application.FileDialog 的路径写入字符串：

xDirectory$ = .SelectedItems(1) & "" 

现在，您只需要将文件夹硬编码为该字符串：

xDirectory$ = "C:UsersmichalSkyDrivecsvbossa"

并删除与FileDialog部分相关的所有代码。

至于"$"部分，见@BrakNicku上面的回答。