ThisWorkbook.Path not updating
 

I run the following code to retrive data from a database which is in the same
directory as my workbook. The file travels between computers into different
directories so I am using ThisWorkbook.Path to update the path.

This works 98% of the time. However occaisionally it doesn't update and
remembers the prior path (on the last machine) it refreshed to. I have no
idea why.

Can I add something to force this?

Bruce

Private Sub myEOD()

pathA = ThisWorkbook.Path & "\ASX.mdb"
pathB = ThisWorkbook.Path
a = "ASX_EOD"

With Worksheets(a).Range("A1").QueryTable
.Connection = _
"ODBC;DSN=MS Access Database;DBQ=" & pathA & _
";DefaultDir=" & pathB & ";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;"
.CommandText = Array( _
"SELECT ASXCode, Open, High, Low, Close, Volume, ImportDate" &
Chr(13) & "" & Chr(10) & _
"FROM qry_ASX_EOD" & Chr(13) & "" & Chr(10) & _
"ORDER BY ASXCode")
.FillAdjacentFormulas = True
.Refresh BackgroundQuery:=False
End With
End Sub

ThisWorkbook.Path not updating
 

Have you tried compiling the macro after it goes screwy? The code below
might be useful if the path name just needs some time to update, but it
would check if the file exists, and if it doesn't it would wait a
second, and look again.
for i = 1 to 10
MyFile = Dir(pathA)
If MyFile < "" Then
Exit for
else
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
end if
next

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!