Where to store VBA code for executing against xls in email?

  • Thread starter Thread starter Diwa
  • Start date Start date
D

Diwa

I get daily an xls report in my outlook email against which I would
like run some VBA code

Where should I keep this VBA code?
Storing in sheet or workbook won't help because I get a new xls daily.

I am a beginner to excel programming.

Thanks for your help.
 
Do you run code against the report (and that's all you do with it) or do you
run code against the daily report, and shove the results into a master data
workbook that stores your output across multiple days? Will other workers
need to be able to run the code when you are on vacation or sick?

As a general rule, when I am aggregating data over time, I store the code in
the workbook where the aggregate data is stored.

If I just need to see a one-time result (and not store the results in a
centralized workbook), and if I'm generally the only one who will need to run
the code, I save it in my "personal" workbook.

In Excel2003, if you press alt-F11 to open the VBA window, then press Ctrl-R
to make sure the "Project-VBA Project" sub-window is visible, you will see an
item called "VBAProject(Personal.xls). I keep all sorts of code snippets in
there that I use on multiple different workbooks so I don't have to add the
code to each of those workbooks. In Excel 2007 I don't believe that the
personal.xls is shown in the "project" window by default, and there is
probably some cool key combination to show it, but I'm so rarely in 2007 I
haven't looked for it yet- my workaround is to record a macro and tell it to
store it in the personal workbook (I just select one cell to create the
macro), then personal.xls will show in the 2007 VBA project window.

When placing code in the personal workbook, be sure to maintain good coding
practices and fully reference your target workbook, e.g. something like
"activeworkbook.activeworksheet.range("A1").value instead of just
range("A1").value


HTH,
Keith
 
Do you run code against the report (and that's all you do with it) or do you
run code against the daily report, and shove the results into a master data
workbook that stores your output across multiple days? Will other workers
need to be able to run the code when you are on vacation or sick?
Click to expand...

Thanks for the detailed response. Its a one-time execution. No
aggregation.
Eventually, this should be usable by other team members too.
 
In Excel2003, if you press alt-F11 to open the VBA window, then press Ctrl-R
to make sure the "Project-VBA Project" sub-window is visible, you will see an
item called "VBAProject(Personal.xls). I keep all sorts of code snippets in
there that I use on multiple different workbooks so I don't have to add the
code to each of those workbooks. In Excel 2007 I don't believe that the
personal.xls is shown in the "project" window by default, and there is
probably some cool key combination to show it, but I'm so rarely in 2007 I
haven't looked for it yet- my workaround is to record a macro and tell it to
store it in the personal workbook (I just select one cell to create the
macro), then personal.xls will show in the 2007 VBA project window.
Click to expand...

Thanks, keeping my xls in excel\xlstart dir worked. It was in my home
drive on network (not in c:). I used the cmd in Immediate windows to
get the actual dir.
 
Back
Top