我对Excel和VBA还是很陌生，我正在尝试创建一个函数，该函数采用CSV文件的名称并将其导入到当前工作簿中。首先，我记录了这样做的宏，然后在编辑器中将其打开以尝试使其适用于任何文件名。我使用的原始文件名为“ t_0_20”，但我想仅用fileName替换它。它在大多数情况下都有效，但是在两个特定的实例中却不起作用。它首先在With语句期间发生。Location=t_0_20。它也发生在.CommandText。 .CommandText = Array("SELECT * FROM [t_0_20]")。我注意到在这两种情况下，t_0_20都没有用引号引起来来表明它是一个字符串，但是我不知道它是如何使用的。

当我尝试使用fileName替换t_0_20的两个实例来运行代码时，出现错误：

  

运行时错误“ 1004”   找不到查询“ fileName”

然后调试将我发送到.Refresh语句。任何有关此问题的帮助将不胜感激。谢谢！

Sub importct(fileName As String)
'
' importct Macro
'

'
    fileName = "t_0_20"

activeWorkbook.Queries.Add Name:=fileName,Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\B\Documents\Automation Testing\" & fileName & ".csv""),[Delimiter="","",Columns=5,Encoding=1252,QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"",type number},{""Column2"",Int64.Type},{""Column3"",{""Column4"",{""Col" & _
    "umn5"",Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

activeWorkbook.Worksheets.Add
activeSheet.Name = fileName

With activeSheet.ListObjects.Add(SourceType:=0,Source:= _
    "OLEDB;Provider=microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=t_0_20;Extended Properties=""""" _,Destination:=Range("$A$1")).QueryTable

    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [t_0_20]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = fileName
    .Refresh BackgroundQuery:=False
End With
 End Sub