Using filename as variable in external reference formula

[armlegx]

I am trying to automate several time consuming tasks.
I have ~25 worker logs used in new worker training.
I have a summary sheet that gathers information from each of those logs.

I currently have the log creation process automated, but I would like to automate the creation of the summary sheet as well.
The log creation process runs several log creation macros in a "For i =1 to lastrowA1" loop.

One of the variables derived from that loop is myfilenames(i). I want to use the myfilenames(i) variable in formulas, replacing the worker name so that I can run a macro to create the needed external references in the summary view and pull the table data from the individual worker's tables into a master table for analysis.


Current code:

Please Login or Register  to view this content.

What I have tried so far (and doesn't work)

Please Login or Register  to view this content.

How can I make this type of formula work, where part of the filename is a variable?


Attachments:
Log creator (using module 7 as test module)
Alison (example worker log)
Ginny view (summary sheet)

[Greg M]

Hi there.

The Case Review file is password-protected, so it's not possible to test a solution

I suspect that you might start moving in the right direction if you use the following code:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[armlegx]

Fixed, sorry.

[Greg M]

Hi again,

See the amended version of my previous post.

Your application is far too complicated for a "quick fix" which can be fully tested by me, but try my previous suggestion and see if it gets you going in the right direction.

Feel free to shout if you think I can give you any further information.

Regards,

Greg M

[armlegx]

It is definitely putting me on the right track.

I'm getting a sort of error where it is looking for the file to link to, and since the file doesn't exist yet (it opens the open file dialogue box to select the file for the links), the formulas are not going in place after canceling past the dialogue.

I think I might be trying to be too ambitious and will try another approach to build the summary after the initial logs are done and not as part of the creation process.

Thanks!

[Greg M]

Hi again,

You're welcome, and thanks for the feedback.

One possible suggestion: use Dir to check for the existence of the file, and skip the formula creation steps if the file cannot be located.

Let me know if you need any further assistance with this.

Regards,

Greg M



P. S. Many thanks for the Reputation increase - much appreciated