View Single Post
Old 04-15-2016, 05:53 PM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Join Date: May 2013
Location: USA
Posts: 678
BobBridges will become famous soon enoughBobBridges will become famous soon enough

If I understand you correctly, your program will write some data to a workbook; you just want to instruct your program which workbook to write it to. You'll be running the program many times, and the target workbook will vary from run to run, so being able to put the filename in a cell would be convenient for you.

If that's what you want to do, sure, it'll work. I do it all the time; my current client has me writing many tools, one after the other, and I finally got the idea of having a Settings worksheet in each .xlsm. Each row on the Settings worksheet contains a setting name in col 1 and a value in col 2. I wrote a VBA class that reads the Settings worksheet at the beginning of each run, and puts all the settings in a public collection where the various programs can fetch the values they need.

For example, one common value is Path; the value "Path" is on some row in col 1 of Settings worksheet, and next to it is the Path of the desired output. (A blank setting just means "put the output in the same folder as ThisWorkbook".) Another is UserSpecs, where the user of the program can type in a list of department codes or LAN IDs, which tells the program which users to list in the report. And so on; you can make up almost anything. It's just like having a Constant somewhere in your program, except by putting it in the Settings worksheet you allow the program's user to change a value before each run.

In this case you need a setting for the output filename, that's all. You don't have to write a Settings class if you don't want to, but if you keep on with this I'm betting you will eventually. That makes it much easier to create new settings as the need arises.
Reply With Quote