PDA

View Full Version : ActiveWorkbook.Path



lynnnow
06-27-2012, 11:25 PM
Hi All,

I've been using ActiveWorkbook.Path quite successfully for all this while, but now when I am opening my workbook and need to read a text file from the activeworkbook's folder, the folder path is being displayed as the temp folder, and that stops the macro from running. I've added an On Error Resume Next line, but it breaks at the file Open line:

Sub ToolBarAuditor()

Dim popUpBar As CommandBar
Dim newButton As CommandBarControl
Dim strAudis As String
'Dim blnintext, strdatarng

On Error Resume Next
CommandBars("AuditBar").Delete
On Error GoTo 0

Dim strAudiT As String
strAudiT = ActiveWorkbook.Path & "\AudiT.txt" '--- PATH DISPLAYED IS THE TEMP FOLDER
Open strAudiT For Input As #1 '---THIS IS WHERE IT BREAKS SINCE FILE IS NOT FOUND
Line Input #1, strDataRng
Close #1

I have to use activeworkbook, since I've got other workbooks also open and only this workbook uses this bit of code.

Also, I'm using Office 2003 on Win XP with the 2007 compatibility pact installed. My wbk is in xlsm format.

Bob Phillips
06-28-2012, 12:21 AM
Try ThisWorkbook.Path

lynnnow
06-28-2012, 12:28 AM
Same result XLD... I'm tossed abt this right now, cos I feel that since I'm using XL 2003 to open the file, it shows the "File conversion" dialog box and that is what is causing the temporary shift of the file to the temp folder. Is there some way to wait for the file to get fully loaded and then try getting the path?

Bob Phillips
06-28-2012, 12:52 AM
I don't think I have ever seen that. I need to try it in 2003.

lynnnow
07-03-2012, 12:04 AM
Bump!! :confused2 :confused4

Kenneth Hobs
07-03-2012, 07:40 AM
I may have 2003 with the compatibility pack on an old computer at home.

Did you install SP3 before the compatibility pack? http://support.microsoft.com/kb/924074

If you do a File > SaveAs, is it starting at the default folder or the Temp folder?

If you opened the file from Outlook for example, it opens from the Temp folder. Instead, save the file (detach) and then open it.

lynnnow
07-03-2012, 10:31 PM
Hi Ken, yes, updates have been installed and I checked your suggestion and things are as they should be. However, I'm closing this thread since I've used a workaround.

I used the BeforeClose event to write the path of the ActiveWorkbook to a text file on the root folder and read that text file on the Workbook_Activate event.

Just for a little understanding... would the Workbook_Open event be a better place to read the text file for the path, or is Workbook_Activate a suitable place? The reason I'm asking is the "Temp" folder error I got was only when I opened the workbook for the first time in the day (the sheet remains open for the rest of my shift or as long as I have to work with it), the other times, it got the path correctly.

snb
07-04-2012, 12:12 AM
Probably


Msgbox Application.DefaultFilePath

will satisfy your needs.

Kenneth Hobs
07-04-2012, 02:26 AM
You can try and see. That is how I do it. Of course Activate will run each time that you activate that workbook whereas Open will run just with you Open the workbook for the first time.