Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Xl startup (XL97)

    Just a quick question - in word you can (1) specify where the Startup folder is to be stored and (2) use the AutoExec sub to run macros on opening XL, but I can't find (can't remember? <img src=/S/doh.gif border=0 alt=doh width=15 height=15> memory like a sieve) how to do it in XL.

    Anyone tell me? I'm sure I've done it before ... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Beryl M


  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Xl startup (XL97)

    You should have a folder called XLStart in your Office installation directory, typically<pre> Program FilesMicroSoft OfficeOfficeXLStart.</pre>

    It may differ depending on the version of office and your OS.

    You can specify an alternate startup folder in Tools, Options, General.

    All files in either startup directory are loaded each time Excel is started. If you want a macro to run automatically when the file is opened, you could name the macro Auto_Open and place in a general module. Alternately you could use the Workbook_Open() event of the ThisWorkbook object to call whatever macro you want to run.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Xl startup (XL97)

    A bit of delayed reaction here - about six months worth! - but I now need to get a macro to run on opening XL, and I'm fairly certain this means that it should be called autoexec or auto_open and placed in the thisWorkbook of an xls which is in the xlstart folder. Yes?

    So why doesn't it work?! <img src=/S/weep.gif border=0 alt=weep width=21 height=16>
    Beryl M


  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Xl startup (XL97)

    Hi Beryl,
    Try placing your code in the Workbook_Open event of the ThisWorkbook module and see if that does the trick.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Xl startup (XL97)

    Got it! Many thanks!
    Beryl M


  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Xl startup (XL97)

    FWIW, with a lot of help from Jan Karel and others, I wrote this little bit of silliness a few months back. The called "funcPathExists" is a REALLY BAD name for this function because it is misleading, but it was a quick and dirty effort:

    Sub listDefaultLocations()
    MsgBox _
    " Application Path: " & funcPathExists(Application.Path) & Chr(10) _
    & " Default File Save Path: " & funcPathExists(Application.DefaultFilePath) & Chr(10) _
    & " Local Templates Path: " & funcPathExists(Application.TemplatesPath) & Chr(10) _
    & " Network Templates Path: " & funcPathExists(Application.NetworkTemplatesPath) & Chr(10) _
    & " Standard Startup Files Path: " & funcPathExists(Application.StartupPath) & Chr(10) _
    & " Alternative Startup Files Path: " & funcPathExists(Application.AltStartupPath) & Chr(10) _
    & " Library Path: " & funcPathExists(Application.LibraryPath)
    If Val(Application.Version) > 8 Then
    Chr (10) & " User Library Path: " & funcPathExists(Application.UserLibraryPath)
    End If

    End Sub

    Function funcPathExists(ByRef Path As String) As String
    funcPathExists = UCase(Path)
    If Len(Path) = 0 Then funcPathExists = "<none specified>"
    End Function
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Xl startup (XL97)

    I don't think it's silly at all - and it works fine (in XL)! I don't suppose you know what needs changing to get it to work in Word as well, do you?

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Beryl M


  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Xl startup (XL97)

    My skill with Word is as Ernest Borgnine's dancing is to Fred Astaire's. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    (Plagiarized from a travel writer whose name I do not remember.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Xl startup (XL97)

    You don't really want to know! Here's a start. Of course, you need to iterate thru all of the collections that I used index 1 for, I used 13 for the dictionary because it was the only one that had an active spelling dictionary, and this is on Word2K, so you may not have all of these. (I know that you will be disapointed!) Have fun! --Sam
    <pre>Sub Macro1()
    Dim fpc As Variant
    MsgBox Application.NormalTemplate.Path
    For Each fpc In Array(wdAutoRecoverPath, wdBorderArtPath, _
    wdCurrentFolderPath, wdDocumentsPath, wdGraphicsFiltersPath, _
    wdPicturesPath, wdProgramPath, wdProofingToolsPath, _
    wdStartupPath, wdStyleGalleryPath, wdTempFilePath, _
    wdTextConvertersPath, wdToolsPath, wdTutorialPath, _
    wdUserOptionsPath, wdUserTemplatesPath, wdWorkgroupTemplatesPath)
    MsgBox Application.Options.DefaultFilePath(fpc)
    Next fpc
    MsgBox Application.Path
    MsgBox Application.StartupPath
    On Error Resume Next
    MsgBox Languages(13).ActiveSpellingDictionary.Path
    On Error GoTo 0
    If ActiveDocument.Subdocuments.Count > 0 Then
    MsgBox ActiveDocument.Subdocuments(1).Path
    End If
    MsgBox FileConverters(1).Path
    MsgBox RecentFiles(1).Path
    MsgBox Templates(1).Path
    End Sub
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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