Refresh .CSV Connection - No Prompts

[Bench]

Hi i'm trying to refresh a connection in a workbook using vba, (using a refresh every ten minutes will take up too much resource), i want to avoid prompts coming on screen.

So far this is what i have

it breaks at:
.Refresh BackgroundQuery:=False

And comes up with:

Run-Time error '1004':
Excel cannot find the text file to refresh this external data range.
Check to make sure the text file has not been moved or renamed, then try the refresh again

Sub Rifresh()
pathfile = "C:\Documents and Settings\user.name\DataLog.csv"
Sheets(k + 1).Activate
Range("A1").Select
With Selection.QueryTable
    .Connection = _
    "TEXT;" & "C:\Documents and Settings\user.name\DataLog.csv"
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileCommaDelimiter = True
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .TextFilePromptOnRefresh = False
    .Refresh BackgroundQuery:=False
End With
End Sub

This is the code i worked it from if it helps

pathfile = "C:\" & path & "\" & uest & "\" & filename(k) 
Sheets(k + 1).Activate 
Range("A1").Select 
With Selection.QueryTable 
    .Connection = _ 
    "TEXT;" & pathfile 
    .TextFilePlatform = 850 
    .TextFileStartRow = 1 
    .TextFileParseType = xlDelimited 
    .TextFileTextQualifier = xlTextQualifierDoubleQuote 
    .TextFileCommaDelimiter = True 
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1) 
    .TextFileTrailingMinusNumbers = True 
    .TextFilePromptOnRefresh = False 
    .refresh BackgroundQuery:=False 
End With

I've also tried it with the Pathfiles referencing the xls file that gets updated as i'm unsure what should be referenced.

Any help would be appreciated.

Thanks

 

[Trevor G]

Try using the display warnings option. I also think your path maybe wrong as you don't normally have a full stop

pathfile = "C:\Documents and Settings\user.name\DataLog.csv"

Sub upd()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
'Your code
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

End Sub

 

[manishchristie]

Please add the following before refreshing querytable

Selection.QueryTable.TextFilePromptOnRefresh = False