QueryTables.Add

[30percent]

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 )

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:

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:

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

 

[Domenic]

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

With Worksheets("Today").QueryTables.Add(Connection:="TEXT;[COLOR=#ff0000]" & [/COLOR]pathName, _
     Destination:=Range("Today!$A$1"))