Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: QueryTables.Add

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default QueryTables.Add

    Hi,

    I have the following procedure. Instead of hardcoding the path name, I wanted to assign it to a variable as pass it to the method QueryTables.Add. However, I got the following error message. compile error expected: list separator or )


    Code:
    Sub import_Data(ByVal fso As Object, ByVal file As Object, ByVal minus_day As Integer, ByVal basePath)
    
    
    
    
    Dim pathName As String
    
    
    thisYear = Year(Date)
    month_Name = Left(monthName(Month(Date)), 3)
    
    
    
    
    pathName = basePath & "\" & thisYear & "\" & month_Name & "\" & get_FolderDate(minus_day) & "\" & fso.getbasename(file.Name) & ".csv"
    
    
    'With Worksheets("Today").QueryTables.Add(Connection:="TEXT;C:\Users\2017\Nov\11-13-2017\Order-11-13-2017.csv", _
    'Destination:=Range("Today!$A$1"))
    'When I hardcode the pathname as the comment above, it works but get error message when I pass in a variable.
    
    
    With Worksheets("Today").QueryTables.Add(Connection:="TEXT; pathName, _
    Destination:=Range("Today!$A$1"))
    
    
    
    
        .FieldNames = True
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
    End With
    
    
    End Sub
    helper function:

    Code:
    Public Function get_FolderDate(ByVal minus_day As Integer)
    
    
    Dim folder_Date As String
    Dim thisYear As String
    Dim sessionDay As String
    
    
    thisYear = Year(Date)
    sessionDay = CStr(Day(Date) - minus_day)
    folder_Date = Month(Date) & "-" & sessionDay & "-" & thisYear
    
    
    get_FolderDate = folder_Date
    
    
    End Function
    Intend to call the procedure as follows:

    Code:
    Sub call_import_Data()
    
    
    Dim basePath as String
    Dim fso as Object
    
    
    basePath = "C:\Users"
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    
    For Each file In fso.GetFolder(basePath).Files
    
    
        If UCase(fso.getextensionName(file.Name)) = "CSV" Then
            import_Data fso, file, 1, basePath
        End If
    
    
    End If
    
    
    
    
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,003
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: QueryTables.Add

    It looks like you're missing a quote ("") and an ampersand (&)...

    Code:
    With Worksheets("Today").QueryTables.Add(Connection:="TEXT;" & pathName, _
         Destination:=Range("Today!$A$1"))

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •