Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel updating from XML file - file path specific? | Excel Discussion (Misc queries) | |||
Link path not updating when renamed | Excel Discussion (Misc queries) | |||
ThisWorkbook.Path & "\" & .... not WAVFile, but AVI file... | Excel Programming | |||
ThisWorkbook.Path | Excel Programming | |||
Custom Footer using ThisWorkbook.Path? | Excel Programming |