Opening Data File automatically

[antonc]

Hi Guys/Girls,

I'm very much a newbie to VBA and therefore need some of your help to my problem.

Instead of manually opening a data file, I want to a macro that will open the data file automatically whenever I open this specific workbook. I do want the data file to open for every excel file, but for this specific workbook.

Can anyone help me with some VBA code for this?

Your assistance will be highly appreciated and thanks for the help in advance...

[Bob Phillips]

What do you mean by a data file exactly?

[antonc]

I'm working on cashflow statement that will work for each branch in our company (+- 70 branches)

The data file is the Income statement and Balance sheet pulled through in excel from the accounting system.

the cashflow statement will pull through specific values from this and then calculate the cashflow statement.

The problem is that I'm using a drop-down list to select the branch and then using an indirect function to pull through from the inc statement and bal sheet. (These have each branch's IS and BS on separate tabs)
The indirect function does not look at closed data file like vlookups and requires that these file be open in order for the values to pull through correctly.

I hope that this clears this up.

I would have attached a file but I doubt it'll be any help.
I need to do this as there will be more than 20 users of this file and I don't want to explain to all of them which file needs to be open.

Thanks again:thumb

[antonc]

Thanks Guys - I've found the answer - You need to save the macro as "Auto_Open"..

Thanks again

[Bob Phillips]

Workbook_Open is the current way, it was introduced a few versions back and is event driven.

[antonc]

Thanks XLD,

I'm using Excel 2007 - does that matter?

[Bob Phillips]

No. It doesn't matter for any version, Auto_Open is still supported. Auto-Open is not fired if you open a workbook via automation, Workbook_Open is.

[antonc]

Would I need to name the macro Workbook_Open then?

[Bob Phillips]

Yes, and make it Private not Public, and put it in the ThisWorkbook code module.