Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving info in EXCEL (ExcelXP)

    Is there a way (using VBA) to (sort of) save two numbers somewhere without writing files and/or doing difficult stuff with the registry? Users run a read-only sheet and I want to capture the last settings (probably can do that in a few bytes) so that next time they start-up the Auto_open code can set-up things similar to how they were left (I recall vaguely that some variables can survive between Excel sessions and/or reboots..I'd be looking for that but I'm not sure if this was something in Excel in the first place... must be gettin' old)

    EJ

  2. #2
    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: Saving info in EXCEL (ExcelXP)

    I would lean towards saving it in a text file. Have the open macro look for the file, if it does not exist, then use "default settings" (in open macro) or else use value read from text file.

    I would "shy away" from writing to the registry.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving info in EXCEL (ExcelXP)

    Thanks, but like I said... I'd even like to avoid writing to files...

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

    Re: Saving info in EXCEL (ExcelXP)

    Since the Excel file is read-only, you can't save the values in cells in a hidden worksheet. But you must save them somewhere, there is no magic way of preserving values. I don't think that "some variables can survive between Excel sessions and/or reboots" without storing them on disk (i.e. in some kind of file)

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving info in EXCEL (ExcelXP)

    I'm afraid you're right and it's not really possible. I found this link http://www.cpearson.com/excel/hidden.htm but that too does not survive stopping and restarting Excel. I also thought one might think of 'sticking' some data into a toolbar or so (isn't that being saved upon closing Excel in Excel.xlb??).

    Anyway, without files being really clearly available, I re-visited the registry and found Savesetting and Getsetting VBA-functions. Why shouldn't I go there afterall then???

    Erik Jan

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

    Re: Saving info in EXCEL (ExcelXP)

    The SaveSetting and GetSetting functions store and retrieve information in the user's registry (somewhere under HKEY_CURRENT_USER if that means anything to you). If you want the settings to be user-specific, that is fine, but if you want them to be shared between all users of the workbook, this will not do what you want.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving info in EXCEL (ExcelXP)

    Why is the file read-only? (There could be a valid reason.) If you control this, you could use password protection, even with the entire workbook protected from changes, and store your information somewhere. Though this would require saving/writing the file when your users are finished.

    As for an out-of-the-way place to save data off the workbook but in the file, try using Activeworkbook..CustomDocumentProperties("My_Info" ) in your macros. (The "My_Info" is your own custom property name.

    Errol

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving info in EXCEL (ExcelXP)

    The file will be made available through our Intranet and is therefore on a server/share that I'm having no access to (apart from that, I doubt if they would let me even if I wanted to). The option to save info with saving the sheet itself is therefore not possible (apart from the fact that this would potentially delay closing of the tool a lot).

    I'm slowing moving over to the "Registry side"... having user specific settings saved would not be a problem... it's only that I do not like the Registry in the first place.... <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

    EJ

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving info in EXCEL (ExcelXP)

    If you don't like the Registry, why not use a file on the user's disk?
    Legare Coleman

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Saving info in EXCEL (ExcelXP)

    FWIW IMO using the VBA SaveSetting, GetSetting, and DeleteSetting functions to save, retrieve, or delete user-specific data in the Registry would be simplest approach. These functions are very limited in scope (compared to using the Windows API directly), which is probably a good thing. You can only read or write to HKEY_CURRENT_USERSoftwareVB and VBA Program Settings key under the subkey you specify for the "application" name, and are limited to reading/writing string values. Numerical values can be saved by converting to strings, and converted back to numbers when retrieved. I use these functions (primarily with Access applications) and they work reliably (where I work there's the added advantage that most users are not able to access the Registry directly by using Regedit or other means, so they have no way to muck around with the settings other than thru the interface provided by application).

    Your other option, as suggested, would be to write the data to a local initialization (.INI) text file.

    HTH

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

    Re: Saving info in EXCEL (ExcelXP)

    How about using .CustomDocumentProperties, which can be manipulated by VBA in a read-only Workbook?
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving info in EXCEL (ExcelXP)

    I don't think that will work if the file is in a read only directory on a server. From an earlier post, that sounds like the case here.
    Legare Coleman

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

    Re: Saving info in EXCEL (ExcelXP)

    You may be right, he states it is on an intranet server he can't access, but if that is the case, how is he going to do -anything- with it? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving info in EXCEL (ExcelXP)

    He can probably add code and get the IT folks to put it on the server.
    Legare Coleman

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

    Re: Saving info in EXCEL (ExcelXP)

    I've heard about companies that have IT folks who do helpful things for users, but I thought they were just a myth.
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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