excel - ActiveSheet.QueryTables.Add（连接：在 VBA Excel Mac 中的问题）

Question

我尝试在现有的特殊格式 Excel 表中导入 csv 文件，我通过 MacScript 选择其文件名。然后，经过视图修改，文件名是：“/Users/用户名/Downloads/文件名.csv”

我将它存储在字符串变量 MyCSV

然后开始导入：

使用 ActiveSheet.QueryTables.Add(Connection:MyCSV, Range($A$2))

然后宏停止：运行时错误 1004（由应用程序或对象定义的错误）

我对其进行了测试，将变量 MyCSV bij 替换为完整的路径和文件名，它就像做梦一样！

现在，是否不允许使用变量，用 Connection: 标识文件？

非常感谢您的帮助

问候哈利

    Sub ING_Import()

' ING_Import Macro

Dim browsePath, MyBrowseType, MyScript, MyFiles, MyFiles2, MyCSV, myPrompt As String

myPrompt = "Selecteer een CSV-bestand"  
browsePath = "/Users/hanshuizinga/Downloads/"  
MyBrowseType = Chr(34) & "csv" & Chr(34)  
MyScript = "set CSVfile to (choose file of type {" & MyBrowseType & "} " & _  
            "with prompt """ & myPrompt & """ default location (""" & _  
            browsePath & """ as posix file as alias) multiple selections allowed " & "False" & ")" & vbNewLine & _  
            "set {TID, text item delimiters} to {text item delimiters, "",""}" & vbNewLine & _  
            "set CSVfile to CSVfile as text" & vbNewLine & _  
            "set text item delimiters to TID" & vbNewLine & _  
            "return CSVfile"  

MyFiles = MacScript(MyScript)  

MyFiles2 = Replace(MyFiles, "Macintosh SSD", "")  
MyFiles2 = Replace(MyFiles2, ":", "/")  
MyCSV = Chr(34) & "TEXT;" & MyFiles2 & Chr(34)  

    With ActiveSheet.QueryTables.Add(Connection:=MyCSV, Destination:=Range("$A$2"))  
        .Name = "Import laatste ING Transacties"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With  
End Sub 

标签： excelcsvimport