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 )
helper function:
Intend to call the procedure as follows:
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 Subhelper 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 FunctionIntend 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
