Hi,
I have an excel VBA project which interacts with the user entirely through userforms (built it in excel rather than VB since I want to access underlying data from excel sheets for the calculations).
How can I export all the data and calculations on a particular userform into a txt file or other file
There may be multiple userforms open, and saving will just save one of them, loading will load the data from file, initialise a new userform and load the data into it, all without upsetting the other userforms or the underlying workbook.
I'm thinking that I can use one sheet of the workbook as a scratchpad, so when the macro is called, write the userform data onto that sheet, then save the sheet only as a text file by Sheet2.SaveAs, but two problems,
- I'd like a saveas dialogue box to come up to let the user choose filename and directory
- I can't figure out how to load that data back into the sheet
Is there a neat way to do this
Cheers
saving data in a userform without saving the workbook
mnkumar
thanks for the help.
Unfortunately I don't think that approach is going to work for me, the user will have several instances of the userform open at any one time, some of which may have been saved from previous sessions, some of which they will want to save and some they won't. Don't really want to start spawning a new workbook for every form. I guess what I really need is the ReadAllText/WriteAllText methods that VB has.
The GetSaveAsFileName dialogue box method will be useful though, thanks
Kevin Jacobson
What's your reluctance with having extra workbooks open You can hide them to keep them out of sight of the user, and it's probably easier to deal with workbooks than text files. You can bury the code which deals with the specific workbooks within the class module of the user form to simplify things.
lostromich
Instead of using one sheet of the main workbook as a scratch pad, set up a sheet that's configured nicely, with room for any and all data, and save it as a template. For each scenario, create a new workbook based on this template, then save this workbook. It will be easier than using a text file. To get a user name and directory, look into GetSaveAsFileName, which returns a full path + file name to the program, and the program then has to save the file.
This helps with version control of the main workbook, because transient changes to the data are kept separate from the workbook with the program.
textman