Excel 2016 VBA, Windows 10
我正在尝试使用 VBA 来获取数据。我想使用相对引用。我只想从与xlsm文件位于同一文件夹中的"原始关键字.csv"中获取数据。它似乎从未识别出相对路径。我尝试用所有引号(选项A,首选(构建它并将该变量传递给Folder.Files。我看到一个建议将路径和文件名放在另一个线程(下面的链接(中的 File.Contents 中,但这也没有用。有什么建议吗?
' Option A
Dim RawK As String
RawK = """""" & ActiveWorkbook.Path & "Raw Keyword.csv" & """"""
ActiveWorkbook.Queries.Add Name:="Query Keyword", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(RawK)...
' Option B:
ActiveWorkbook.Queries.Add Name:="Query Keyword", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(ActiveWorkbook.Path & ""Raw Keyword.csv"") ...
在这里看到了类似的答案,但没有运气。文件夹文件的相对路径
我认为这就是你要找的:
Sub tgr()
Dim CSVName As String
Dim qt As Object
CSVName = "Raw Keyword"
On Error Resume Next
Set qt = ActiveWorkbook.Connections(CSVName)
On Error GoTo 0
If qt Is Nothing Then
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & ActiveWorkbook.Path & "" & CSVName & ".csv", Destination:=Range("$A$1"))
.FieldNames = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.AdjustColumnWidth = True
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
Else
qt.Refresh
End If
End Sub