I am using Excel 2003 on Windows XP.

1) Let us say I log in as foo.
I find PERSONAL.XLS is expected to be in the folder
C:\Documents and Settings\foo\Application Data\Microsoft\Excel\XLSTART.
I have another system where PERSONAL.XLS is expected to be in the folder
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
What controls where excel looks for PERSONAL.XLS?

2) I have some macros which reference a data file from PERSONEL.XLS.
I want to move both files to another machine.
It would be useful to put both files in the same folder - an
installation package is too heavyweight a solution.
Can somebody suggest code to identify the file containing the running
macro?
--
Walter Briscoe

The best way would be to create an excel addin (*.xla) instead of
personal.xls.


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92807

1. MsgBox Application.StartupPath

2. If you want to distribute code, as suggested, probably better in an
addin. It can go anywhere, typically easiest to put it here -

MsgBox Application.UserLibraryPath

Regards,
Peter T

"Walter Briscoe" wrote in message
...
I am using Excel 2003 on Windows XP.

1) Let us say I log in as foo.
I find PERSONAL.XLS is expected to be in the folder
C:\Documents and Settings\foo\Application Data\Microsoft\Excel\XLSTART.
I have another system where PERSONAL.XLS is expected to be in the folder
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
What controls where excel looks for PERSONAL.XLS?

2) I have some macros which reference a data file from PERSONEL.XLS.
I want to move both files to another machine.
It would be useful to put both files in the same folder - an installation
package is too heavyweight a solution.
Can somebody suggest code to identify the file containing the running
macro?
--
Walter Briscoe

Hi Water, see also :
http://www.rondebruin.nl/personal.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Walter Briscoe" wrote in message ...
I am using Excel 2003 on Windows XP.

1) Let us say I log in as foo.
I find PERSONAL.XLS is expected to be in the folder
C:\Documents and Settings\foo\Application Data\Microsoft\Excel\XLSTART.
I have another system where PERSONAL.XLS is expected to be in the folder
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
What controls where excel looks for PERSONAL.XLS?

2) I have some macros which reference a data file from PERSONEL.XLS.
I want to move both files to another machine.
It would be useful to put both files in the same folder - an
installation package is too heavyweight a solution.
Can somebody suggest code to identify the file containing the running
macro?
--
Walter Briscoe

In message of Mon, 4 May 2009
17:27:05 in microsoft.public.excel.programming, Ron de Bruin
writes
Hi Water, see also :
http://www.rondebruin.nl/personal.htm


I saw that and was much impressed. I am still in the process of
digesting it. Application.StartupPath will be very useful to me.
I did some experimentation and found that if
"Application.StartupPath"\Personal.xls does not exist, Personal.xls is
opened from another folder - I infer by all users. For me, that folder
is "C:\Program Files\Microsoft Office\OFFICE11\XLSTART". I don't think
it very useful, but it seems worth putting on the record in the absence
of documentation from Microsoft. Curiously, Sysinternals' procmon.exe
does not seem to notice the double (and possibly longer) search. I don't
care enough to investigate further.

The advice from "royUK " and "Peter T
" to use an add-in seems good and I will
look at that when I have time.

Thank you all! THAT problem is solved and my understanding is enhanced.
--
Walter Briscoe

In message of Tue, 5 May 2009
08:58:12 in microsoft.public.excel.programming, Walter Briscoe
writes
In message of Mon, 4 May 2009
17:27:05 in microsoft.public.excel.programming, Ron de Bruin
writes
Hi Water, see also :
http://www.rondebruin.nl/personal.htm

[snip]

The advice from "royUK " and "Peter T
" to use an add-in seems good and I will
look at that when I have time.

[snip]

I looked at Ron's file and the references from it to creating a
menu/toolbar - particularly
<http://support.microsoft.com/default.aspx?scid=kb;en-us;830502&Product=xlw
which I found particularly helpful as it deals with the elements of a
solution rather than a solution. However, it has to be read carefully as
the same sub name is used more than once and I found part of it did not
print. I also looked at <http://www.cpearson.com/excel/CreateAddIn.aspx
and several other files. I put the following code into my first add-in:

Option Explicit

Sub Auto_Open()
Call CreateMenuBar
End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub

Sub CreateMenuBar()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
With myCmd
.Controls.Add(Type:=msoControlButton, _
Befo=1).Caption = "MacroToRunOne"
.Controls("MacroToRunOne").OnAction = "MacroToRunOne"
End With
End Sub

Sub RemoveMenubar()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
myCmd.Controls("MacroToRunOne").Delete
End Sub

Sub MacroToRunOne()
Dim S As String
S = "Hello World From One:" & vbCrLf & _
"This Add-In File Name: " & ThisWorkbook.FullName
MsgBox S
End Sub

I have a couple of questions:
1) How do I stop CreateMenuBar() being effective more than once?

2) How do I place a breakpoint in an add-in?
I discovered that Debug after an error works well; it seems perverse to
use an error to get control!
--
Walter Briscoe

Hi Walter

Always add this line as first in the CreateMenuBar macro

Call RemoveMenubar

But add a on error line in the RemoveMenubar macro like

Sub RemoveMenubar()
Dim myCmd As Object
On Error Resume Next
Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
myCmd.Controls("MacroToRunOne").Delete
On Error GoTo 0
End Sub

Note your code will not work on a Non English machine
Tools is a local work then

Use it like this to avoid problems

Sub MenuBar_Item_Item()
Dim MenuItem As CommandBarControl
MenuBar_Item_Item_Delete

Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu
If MenuItem Is Nothing Then Exit Sub
With MenuItem.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Ron de Bruin"
.TooltipText = "ttt"
.OnAction = ThisWorkbook.Name & "!TestMacro"
.BeginGroup = True
.Tag = "MenuItemTag"
End With
Set MenuItem = Nothing
End Sub

Sub MenuBar_Item_Item_Delete()
Dim MenuItem As CommandBarControl
Set MenuItem = Application.CommandBars.FindControl(Tag:="MenuItem Tag")
If Not MenuItem Is Nothing Then
MenuItem.Delete
End If
Set MenuItem = Nothing
End Sub

Sub TestMacro()
MsgBox "Hi"
End Sub



2) How do I place a breakpoint in an add-in?
I not understand what you mean with this

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Walter Briscoe" wrote in message ...
In message of Tue, 5 May 2009
08:58:12 in microsoft.public.excel.programming, Walter Briscoe
writes
In message of Mon, 4 May 2009
17:27:05 in microsoft.public.excel.programming, Ron de Bruin
writes
Hi Water, see also :
http://www.rondebruin.nl/personal.htm

[snip]

The advice from "royUK " and "Peter T
" to use an add-in seems good and I will
look at that when I have time.

[snip]

I looked at Ron's file and the references from it to creating a
menu/toolbar - particularly
<http://support.microsoft.com/default.aspx?scid=kb;en-us;830502&Product=xlw
which I found particularly helpful as it deals with the elements of a
solution rather than a solution. However, it has to be read carefully as
the same sub name is used more than once and I found part of it did not
print. I also looked at <http://www.cpearson.com/excel/CreateAddIn.aspx
and several other files. I put the following code into my first add-in:

Option Explicit

Sub Auto_Open()
Call CreateMenuBar
End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub

Sub CreateMenuBar()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
With myCmd
.Controls.Add(Type:=msoControlButton, _
Befo=1).Caption = "MacroToRunOne"
.Controls("MacroToRunOne").OnAction = "MacroToRunOne"
End With
End Sub

Sub RemoveMenubar()
Dim myCmd As Object
Set myCmd = CommandBars("Worksheet menu bar").Controls("Tools")
myCmd.Controls("MacroToRunOne").Delete
End Sub

Sub MacroToRunOne()
Dim S As String
S = "Hello World From One:" & vbCrLf & _
"This Add-In File Name: " & ThisWorkbook.FullName
MsgBox S
End Sub

I have a couple of questions:
1) How do I stop CreateMenuBar() being effective more than once?

2) How do I place a breakpoint in an add-in?
I discovered that Debug after an error works well; it seems perverse to
use an error to get control!
--
Walter Briscoe

In message of Mon, 11 May 2009
21:10:32 in microsoft.public.excel.programming, Ron de Bruin
writes

Ron,
Pardon me if I interleave my response. Answering at the front does not
work for me. Anyway, thanks for another interesting post.
I delayed replying until I had tried the work.

Hi Walter
Always add this line as first in the CreateMenuBar macro

Call RemoveMenubar

I can't see any failure mechanism in that.
It means I can only have zero or one controls at any time.


But add a on error line in the RemoveMenubar macro like

[snipped code allowing RemoveMenubar to accept menu bar removal errors.]

Note your code will not work on a Non English machine
Tools is a local work then

That is a good point. I see no reason why my code would need to work in
a non-English environment, but almost-free internationalisation is good.


Use it like this to avoid problems

Sub MenuBar_Item_Item()
Dim MenuItem As CommandBarControl
MenuBar_Item_Item_Delete

Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu
If MenuItem Is Nothing Then Exit Sub

Should this not be a panic situation?

With MenuItem.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Ron de Bruin"
.TooltipText = "ttt"
.OnAction = ThisWorkbook.Name & "!TestMacro"
.BeginGroup = True
.Tag = "MenuItemTag"
End With
Set MenuItem = Nothing

Why clear MenuItem? I assume it only makes explicit what happens anyway.

[snip]

2) How do I place a breakpoint in an add-in?
I not understand what you mean with this


If I access a macro in PERSONAL.XLS, I have the choice of Run, Step Into
and Edit (only if visible) "methods". With "Step Into", I can set
breakpoint (F9 on a line puts a breakpoint on it); I can add watches
with Debug\Add Watch..., etc. Then I can step through code with F8, etc.
I do not yet see a way of having such control with an add-in.

I did some playing around in my add-in and closed it.
(Open it from Excel, Alt+F11 accesses the code, and close after changes)
I am alarmed that Excel does not ask me if I want to save the work.
Is there an option to do so?

Dank u wel meneer! (Ik spraak niet nederlands ;)
--
Walter Briscoe