Import CSV via QueryTables

[R2ah1ze1l]

I'm sorry this is close to redundant, however I have not seen where anyone has handled this: (forced to select '*.csv', strfile will not allow me to concatenate the file path/name with a .csv extension)
I do not want myself or other users to manually select the data, I want it pulled from the input fields I can control in my 'Data_Loc' sheet.

Set ws = ActiveWorkbook.Sheets("Data_Loc")
path_l = Range("c1").Value
file_1 = Range("c2").Value
'pick the file to work with, automation fails do to .csv vs .txt extensions
strfile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
Set ws = ActiveWorkbook.Sheets("Update_CSV")
ws.Cells.Clear
'populates Update_CSV sheet with CSV values
With ws.QueryTables.Add(Connection:="TEXT;" & strfile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

How can I further automate this process to stop the manual file selection?
Thank you for your assistance.

 

[John_w]

Like this:

path_l = ws.Range("c1").Value
file_1 = ws.Range("c2").Value
If Right(path_1, 1) <> "\" Then path_1 = path_1 & "\"
strfile = path_1 & file_1

 

[R2ah1ze1l]

Like this:

path_l = ws.Range("c1").Value
file_1 = ws.Range("c2").Value
If Right(path_1, 1) <> "\" Then path_1 = path_1 & "\"
strfile = path_1 & file_1

Thank you,
However the concatenation is not what stops the file, when a '*.csv' is entered to the strfile the querytable errors out on the .refresh line because it is not recognized as a TEXT file.

 

[John_w]

the concatenation is not what stops the file

I don't understand what you mean.

when a '*.csv' is entered to the strfile the querytable errors out on the .refresh line because it is not recognized as a TEXT file

The Application.GetOpenFilename allows you to select a specific file. My changes replace this with a specific folder path in C1 and file name in C2, which is what I understand you want.

ws.QueryTables.Add(Connection:="TEXT;" & strfile

The QueryTables.Add must specify a specific file name, in this case the strfile variable. You can't specify a wildcard file name, if that is what you've put in C2.

 

[R2ah1ze1l]

When I attempt to concatenate the string and utilize the '.csv' extension of the saved file, this is the error set I receive. When I manually select the .csv file the code runs without issue.

 

[John_w]

What is the value of strfile and is it the correct full path and file name of the .csv file you want to import?

Your code requires "Data_Loc" to be the active sheet when you run the macro, otherwise path_1 and file_1 won't pick up the correct cell values. That's why I added the "ws." sheet qualifier to:

path_l = ws.Range("c1").Value
file_1 = ws.Range("c2").Value

 

[R2ah1ze1l]

path_1 = folder located on my desktop
file_1= name of file without extension (ie. filename.csv is "filename")
strfile = path_1 & "/" & file_1 & ".csv"

I can open the desired file with this concatenation, just not use it in the query for some reason.

 

[R2ah1ze1l]

With the manual selection, I notice the 'Text Files(*.csv)', which functions and allows the file to run with no errors.
When attempting to concatenate a path that has a '.csv' as the strfile I get the '1004' Runtime Error at the line '.Refresh'

 

[John_w]

Are the 2 methods specifying exactly the same file? To find out, reinstate the GetOpenFileName and add a bit of simple debugging (note Windows paths use the backslash "\" to separate folders, not the forward slash "/" that your code uses):

strfile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
If strfile <> path_1 & "\" & file_1 & ".csv" Then
  MsgBox "The files are different." & vbCrLf & _
         "strfile = " & strfile & vbCrLf & _
         "concatenation = " & path_1 & "\" & file_1 & ".csv"
Else
  MsgBox "The files are the same." & vbCrLf & _
         "strfile = " & strfile & vbCrLf & _
         "concatenation = " & path_1 & "\" & file_1 & ".csv"
End If

 

[R2ah1ze1l]

Are the 2 methods specifying exactly the same file? To find out, reinstate the GetOpenFileName and add a bit of simple debugging (note Windows paths use the backslash "\" to separate folders, not the forward slash "/" that your code uses):

strfile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
If strfile <> path_1 & "\" & file_1 & ".csv" Then
  MsgBox "The files are different." & vbCrLf & _
         "strfile = " & strfile & vbCrLf & _
         "concatenation = " & path_1 & "\" & file_1 & ".csv"
Else
  MsgBox "The files are the same." & vbCrLf & _
         "strfile = " & strfile & vbCrLf & _
         "concatenation = " & path_1 & "\" & file_1 & ".csv"
End If

Something is off, but not sure what.
I have more to learn about this.