Workbooks.Open with out full path help needed.

[dk_]

Below is the first part of a macro that works 'almost' perfectly.

In my macro, I don't want to use the 'full path' to the file, because I
move the set of files to different directories at times. The macro works
well on my Macintosh without a full path.

The macro will open the "testfile.xls" when called, if the file has been
perviously opened once, via the 'File' menu in Excel. However, opening
the testfile.xls via Windows Explorer does not work for the macro. I'd
like to be able to either not have to manually open the file, or at
least be able to open the file in Windows Explorer without using the
File menu.

The file can then be closed and the macro will open the file if I
haven't quit and restarted Excel, and if the file was opened via the
File menu.

The "testfile.xls" is in the same directory as the Excel file that is
running the macro.

First part of macro here...

Sub QuoteCopy_Tester()
Application.ScreenUpdating = False
Workbooks.Open FileName:="testfile.xls"
Range("QuoteArea").Select

....macro continues.

Thanks for the help.

Sorry if I've talked in circles.

-Dennis

 

[Guest]

Sub QuoteCopy_Tester()
Dim dir As String
Application.ScreenUpdating = False
dir = ActiveWorkbook.Path
Workbooks.Open Filename:=dir & "\testfile.xls"

Try this..
I think this can help you out.

Best

Petter

dk_ skrev:

 

[dk_]

Petter,

Your code below definitely works in Excel97. Thank you.

....But ...it breaks my macro when run on my Macintosh with Excel98.

Any more ideas for solving this inconvenience?

On the Mac, my previous macros worked under all conditions (i.e., I did
not have to use the 'File' 'Open' menu to run the proceedure. I would
like to have the Macro be movable if possible, between Excel97 and
Excel98 (Mac).


This line below does not work on the Mac...

Workbooks.Open Filename:=dir & "\testfile.xls"


Thanks your help.

-Dennis

 

[Dave Peterson]

maybe...

Sub QuoteCopy_Tester()
Dim mydir As String
Application.ScreenUpdating = False
mydir = ActiveWorkbook.Path
Workbooks.Open Filename:=mydir & application.pathseparator & "testfile.xls"
End Sub

 

[dk_]

....Maybe perfectly!

Dave,

I just tested your modification below with both Excel98 (Mac), and with
Excel 97 (Window). Both machines played perfectly with the code.

The macro works on both machine. Great trick!

Thank you.

-Dennis

 

[dk_]

Dave,

I also tested your code (below), leaving out the following line...

Dim mydir As String

The macro ran perfectly on both the Macintosh and
on the Window machine, without the line above.

Thank you!

-Dennis