CSV Import - Runtime Error 1004

[leeksleeks]

Hi,

I have a problem when everytime I run my macro for importing and converting CSV files that I get a Runtime error 1004 saying that it cant find the text file etc. The thing is that if I record a Macro to import the data and copy and paste the path (which is exactly the same as the one I have in my macro) it works. Any ideas as to why this would work but when I run it when I open my file I get the Runtime 1004 error and the Refresh part is highlighted?

Sub CSV_Import()
Dim ws As Worksheet, strFile As String


Set ws = ActiveWorkbook.ActiveSheet


strFile = Dir("Z:\business\02 All\Business Reports\Raw Data\*.csv")
Do While Len(strFile) > 0
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileSemicolonDelimiter = True
     .Refresh
         
  End With

strFile = Dir
Loop
End Sub

Any help would be greatly appreciated. I think i have read every conceivable thread without a solution so far

 

[John_w]

Dir returns only the file name, without the parent folder path. Therefore you need:

With ws.QueryTables.Add(Connection:="TEXT;Z:\business\02 All\Business Reports\Raw Data\" & strFile, Destination:=ws.Range("A1"))

 

[leeksleeks]

Thank you very much John_w. That now works perfectly