Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro for every Excel File (Excel 2000)

    How can you add a macro to be available in any excel file that is open?

    Thanks

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro for every Excel File (Excel 2000)

    I found this in a previous post; not sure which one.

    Sub ImportModule()
    'You need references to the Excel and the "Microsoft Visual Basic for Applications Extensibility" library.
    Dim oXL As Excel.Application
    Dim VBP As VBIDE.VBProject
    Dim sFilename As String
    Set oXL = New Excel.Application
    With oXL
    .Visible = True
    .Workbooks.Open "CataTest.xls"
    Set VBP = .ActiveWorkbook.VBProject
    sFilename = "c:dataModule1.bas"
    VBP.VBComponents.Import sFilename
    .ActiveWorkbook.Save
    .Quit
    End With
    Set oXL = Nothing
    End Sub

    Option Explicit

    Public Sub DeleteModule()
    'This one shows how to delete a module and insert lines of code on the fly
    Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook
    Dim vbeProject As VBIDE.VBProject
    Dim vbeModule As VBIDE.CodeModule
    Dim strBook As String
    Dim i As Integer
    strBook = xlApp.GetOpenFilename
    Set xlBook = xlApp.Workbooks.Open(strBook)
    xlApp.Visible = True
    Set vbeProject = xlBook.VBProject
    With vbeProject
    On Error Resume Next
    .VBComponents.Remove .VBComponents("myMod")
    On Error GoTo 0
    With .VBComponents.Add(vbext_ct_StdModule)
    .name = "myMod"
    Set vbeModule = .CodeModule
    End With
    With vbeModule
    i = .CountOfLines + 1
    .InsertLines i, _
    "Sub Hi()" & Chr(13) & _
    " Msgbox ""Hi"" " & Chr(13) & _
    "End Sub"
    End With
    End With
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro for every Excel File (Excel 2000)

    Create the macro in personal.xls that file is open and hidden.

    When you use the macro recorder, just select to "store macro in" personal macro workbook

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro for every Excel File (Excel 2000)

    That does not seem to work unless i am doing something wrong. eventhough the new macro is assigned to the personal workbook, I still get an error that the macro cannot be found [img]/forums/images/smilies/sad.gif[/img]

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro for every Excel File (Excel 2000)

    How are you calling the macro and where is the macro in personal.xls?
    Have you prefaced the macro name with:
    PERSONAL.XLS!

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro for every Excel File (Excel 2000)

    Yes I have. I am not in the office now, but at home, it will take sometime to simulate the setup at the office. I will get back wiith you soon about this [img]/forums/images/smilies/smile.gif[/img]

    Thanks

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro for every Excel File (Excel 2000)

    Just a thought. When you save your macro's to a personal folder its good only for the excel files on your computer. If you are working on a network and have multple users using a copy of the spreadsheet, you will run into problems on other machines unless you are sure to install the macro's in the personal folder on those machines. I found that to be a royal pain so I resorted to always keeping the macro's in the same workbook. Its a pain to reproduce macro's you use over and over, but less painful than users whining that they can't run my programs!

    Just a thought,
    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro for every Excel File (Excel 2000)

    If you want to make macros and custom functions available on the network, you can have users point their Alternate Startup Location at a network folder, and store the macros in an add-in (.xla) in this folder. The alternate Startup Location can be set in the General tab of Tools | Options...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro for every Excel File (Excel 2000)

    See <post#=272311>post 272311</post#> for Legare Coleman's tutorial on Personal.xls.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro for every Excel File (Excel 2000)

    Hmmmmmmm. That's an interesting idea. Would this mean then that the workbooks are being stored from a network drive vs the C drive on the individual computer?? If that is the case then in some cases it would be OK, but in others, where users on multiple workstations use a copy of the same workbook, I would think I'd run into issues if the workbook is already open by another user, making it read only.

    If you mean that the excel starts from a folder on the network drive, and the personal folder is saved there as well..............when the user opens a workbook saved to their C drive, would it look to the personal folders on the from the network drive for the macros???? And if this is the case, is that same personal folder available to multiple users at a time??

    Leesha

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro for every Excel File (Excel 2000)

    Excel can use two startup locations:

    1. A folder XLSTART that is usually located on the user's hard disk. The user's Personal.xls will be in XLSTART, so will not be shared with others.

    2. An optional Alternate Startup Location that can be anywhere. By default, the Alternate Startup location is empty, i.e. not used. If you set it to a network folder, you can make it read-only to standard users.

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro for every Excel File (Excel 2000)

    Ok, bear with me. Would the personal folder then be put in the alternate startup location as a read-only file?

    Leesha

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro for every Excel File (Excel 2000)

    I will try to explain it once more. Excel can use two startup locations simultaneously - workbooks and add-ins from both locations will be opened automatically when Excel starts. It's not either/or but both/and.

    The user's personal macro workbook Personal.xls (I assume that you mean the personal macro workbook, not a personal folder) is stored in the XLSTART folder, which is local. It is not stored in the Alternate Startup Location.

    You can make macros generally available by putting them in an Excel add-in (.xla file), and storing this add-in in a network folder. To prevent clashes and accidental overwriting, you can make the add-in and/or the folder read-only to "ordinary" users. The add-in will be loaded automatically (thereby making the macros it contains available) if the user sets the network folder as Alternate Startup Location in the General tab of Tools | Options...

    I don't know how to make it clearer...

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro for every Excel File (Excel 2000)

    Got it.

    Thank you,
    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •