Query Relative Path for Folder.Files

Question

Excel 2016 VBA, Windows 10

I'm trying to use VBA to Get Data. I want to use a relative reference. I just want to get data from 'Raw Keyword.csv' in the same folder as the xlsm file. It never seems to recognize the relative path. I tried building it with all the quotes around it (option A, preferred) and passing that variable to Folder.Files. I saw a suggestion to put the Path and filename in File.Contents in another thread (link below) but that didn't work either. Any suggestions?

' 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"") ...

Saw a similar answer here, but no luck with that. Relative path for Folder.Files

Are you expecting for the file contents to be extracted and is there any reason why you can't use a macro to record and then edit in Activeworkbook.Path? You would have something like ActiveWorkbook.Queries.Add Name:="Raw Keyword", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""" & ActiveWorkbook.path & "\Raw Keyword.csv""))" but with additional info before the last ")" — QHarr, Mar 15 '18 at 14:18
I don't understand what you've changed. It looks like you've given me the option B that didn't work for me. — , Mar 15 '18 at 14:26

score 0 · Answer 1 · answered Mar 15 '18 at 13:59

0

I think this is what you're looking for:

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

answered Mar 15 '18 at 13:59

tigeravatar

26,199
5
30
38

I appreciate that, but I'm not sure how to integrate it into what I have. You're using QueryTables.Add. I'm trying to fit it into Csv.Document(File.Contents(""C:\Users\Jason\Documents\ftf\gigs\Distributed 20180302\Raw Keyword.csv"") as part of Queries.Add . Can you help translate to that context? – Mar 15 '18 at 14:13
@JasonTorpy That sounds like you're using Power Query? I'm not too familiar with that, but I don't think you can use a variable in the source. Have you tried using this provided code? – tigeravatar Mar 15 '18 at 15:58
I don't think I'm using PowerQuery. It's just the 'Get Data' link on the Data tab, and I recorded and used the VBA there. I'd use your suggestion, but I'm not sure how to connect it to the code I have. There are a lot of other import specifications that have to be retained after the ... – Mar 15 '18 at 16:08
If possible, open a brand new excel workbook -> open the Visual Basic Editor (VBE) -> Insert -> Module -> Copy and paste the code and then run it. If it doesn't work like you want, no harm because this isn't on your actual workbook. And if it doesn't work like you want, you'll need to provide more information by editing your original post and showing your whole code. Preferably you would also include sample data and expected results. – tigeravatar Mar 15 '18 at 16:10

Query Relative Path for Folder.Files

1 Answers1