How to use a range, from another workbook, in a formula in VBA?

Question

I'm automating a large number of emails which deliver KPIs and backing data to managers on a daily basis.

How do I get NETWORKDAYS(startdates, enddates, holidays) to use the range holidays on another Excel workbook?

The whole process is:

Task scheduler opens an XLSM file daily.
The XLSM file opens a csv output, edits and analyses it - including using NETWORKDAYS.
The csv file is then emailed out, with an email that reports the KPI it calculates.

I want to make sure holidays is kept on one Excel workbook only, separate from the xlsm files, because there are going to be many such xlsm files scheduled - if holiday dates change, I only want to have to update one range.

I have tried a number of things using with, set, named ranges. (I've only used VBA for a few days, so may be missing something obvious, however.)

Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=MAX(NETWORKDAYS([@[Effective Requested Date]], [@[Scheduled Date]], holidays), NETWORKDAYS([@[Effective Requested Date]], [@ExamDate], holidays))"

My NETWORKDAYS column outputs as #VALUE. I am expecting the output to be a whole number.

You should need to include the workbook name & table name in the formula. Can you try building such a formula manually (via point & click in Excel, for instance) and then compare versus the formula you're currently using? It should be easy to spot the difference in syntax. — David Zemens
– David Zemens, Commented Jun 25, 2019 at 13:51
Hi David, I did that yesterday. It worked in that session. But when the macro ran today, it asked me to update the file location of the holidays file. — TomChivs
– TomChivs, Commented Jun 25, 2019 at 13:59
I'm not sure what you mean. Can you include the exact prompt/message that you get from that attempt? Has the file location changed? — David Zemens
– David Zemens, Commented Jun 25, 2019 at 14:00
Hi David, I'll try to recreate the error. When I copy and paste from the XLSM file, the code changes to ActiveCell.FormulaR1C1 = _ "=MAX(NETWORKDAYS([@[Effective Requested Date]], [@[Scheduled Date]], holidays.xlsx!HolidaysTable[holidays]), NETWORKDAYS([@[Effective Requested Date]], [@ExamDate], holidays.xlsx!HolidaysTable[holidays]))". <p> This produces an #REF — TomChivs
– TomChivs, Commented Jun 25, 2019 at 14:27
That formula is valid when you do it manually, right? Like it returns a value? But it is giving you the #REF when made from VBA? — David Zemens
– David Zemens, Commented Jun 25, 2019 at 14:31

David Zemens · Accepted Answer · 2019-06-25 15:53:19Z

This seems to be an explicit limitation of using structured references to external ListObject tables in a formula string.

Use workbooks with external links to Excel tables in other workbooks If a workbook contains an external link to an Excel table in another workbook, that linked source workbook must be open in Excel to avoid #REF! errors in the destination workbook that contains the links. If you open the destination workbook first and #REF! errors appear, they will be resolved if you then open the source workbook. If you open the source workbook first, you should see no error codes.

One way around this would be to not use structured references, and instead refer to the range's absolute address instead, assuming the sheet name is "HolidaysTable", or modify as needed:

C:\Users\TChivs\Desktop\Macro\[holidays.xlsx]HolidaysTable!$A$2:$A:$20

Alternatively, since it seems you're producing a CSV with these computations, it would be better to simply produce the calculation on the VBA side (i.e., not in a worksheet formula, that is subsequently destroyed into its Value only, when saving as a CSV), I think I would take a slightly different approach to your application design:

Instead of opening one of many XLSM files that do (the same?) something, use only ONE .xlsm file that contains the holiday information and whatever other macro/VBA code you need. Now, as for the "many" XLSM files, each of these should be given as a parameter to each respective scheduled task, so you'd have something like

Task1 = Opens main.xlsm with parameter "C:\pathto\bar.xlsm"
Task2 = Opens main.xlsm with parameter "C:\pathto\foo.xlsm"
Task3 = Opens main.xlsm with parameter "C:\pathto\another.xlsm"

In this way, you have all of the code & holidays & etc in a single source ("main.xlsm") which does something(s) with the other files as scheduled via task manager.

Collectives™ on Stack Overflow

How to use a range, from another workbook, in a formula in VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related