Filepaths in VBA - (taken from Microsoft)

[PipBoy808]

I was looking at this macro on Microsoft's support website when something occurred to me:

Sub MyMacro()

Dim MyWorkbook As Workbook

' Run the Error handler "ErrHandler" when an error occurs.
On Error GoTo Errhandler

ChDrive "B:"
ChDir "B:\"
ChDir "B:\XLFiles"
Workbooks.Open "Book1.xls"

What are the last four lines describing? Are they detailing a file path to 'Book1.xls' that's in B:\XLFiles or is it something else? Furthermore, how can 'ChDir' be defined twice?

Thanks!

 

[Smallman]

Hi Pipboy808

When looking at a problem like this it is best to post a link to the ful code so members can run some tests. The code is located here;

http://support.microsoft.com/kb/141571

Now I could not get the code to work on my computer. This was the offending line.

ChDir "C:\Users\HYMC\XLFiles"

Where I put a file called Book1. It seems from initial inspection that your question is a valid one. It seems Msoft want to step through the most basic of steps. The ChDrive and ChDir are the directories your browser will default to when fired from XL. So by refering to these Msoft is changing the default directory to the one named in this case the B Drive. Book1.xls is merely the name of the file which is attempting to be open.

You can simplify Microsoft's full code without losing much fidelity. Here is my take on the full procedure.

  Sub MyMacro2()
    Const OpenMeBad = "C:\Users\HYMC\book1.xls"
      ' Run the Error handler "ErrHandler" when an error occurs.
      On Error GoTo Errhandler
      Workbooks.Open OpenMeBad
      ' Disable the error handler.
      On Error GoTo 0
      MsgBox "The destination workbook is " & ActiveWorkbook.Name
      ' Exit the macro so that the error handler is not executed.
      Exit Sub
Errhandler:
      ' If an error occurs, display a message and end the macro.
      MsgBox "An error has occurred. The macro will end."
 
  End Sub

If you substitute this C:\Users\HYMC\ with the name of the path on your computer and have an XL 03 file saved with the original title book1, you should get the file to open no problems at all. I think it is an easier read but that is just me.

Hope that helps a little.

SMallman

 

[SirJB7]

@SirJB7
Hi, myself!
So long...
Who's been the SIBTA (aka slightly intelligent below the average) of the Redmond guys that used "B:" as drive letter in an example? Maybe one (because there're still two!) running an 8086 with 2 360Kb floppy drives...
Regards!
PS: Did Excel version -9.11 run in extended or expanded memory? Ohhh... I've got it... it only used less than 640 Kb... Cheers, Bill.