VERY Difficult VBA question

[tljenkin]

Hi Guys,

I dont have the HTML maker so I am going to try to best explain this with words and I will make up an example for ease. Thanks!

Ok I have a master file that is linked to 2 feeder files.
Its January and a January folder has been created and the master file copied over.
However the 2 feeder files come from another source and the current month convention is reflected in them "MMYY". These feeder files have their own folder, within which is a date hierarchy of holders.

Without having to either manually update links, how can I use a macro that will do the following:

1) Look at a drop down in the master file and pick up the current month "MMYY"
2) Use the month above to detect which folder to look in
3) Be smart enough to substitute last month's name in the file "1214" with "0115" and link the master file to the correct feeder file

PLEASE NOTE: The reality is there is a 100 feeder files and 1 master file, so imagine how painful this is every month especially as there is very complicated sub folder structure where the feeder files can be found!!

 

[Arithos]

Hi,

The month/year format is easily enough formattet. What are the criteria for substituting "1214" with "0115"? first time running then every month?
The january file, is "0115"?
and you want to change the "current month convention" in the feeder files to "0115", from the previous "1214"?

is "1214" included in the filepaths to the feeders? or somewhere inside the feeder files.

 

[tljenkin]

Hi apologies for only replying now, I have been off work for a bit. To answer you questions, yes 1214 uses the convention MM/YY and every month, that is rolled over so it will be dec 14 to jan 15, jan 15 to feb 15 and so on...

In the paths, the month folders are named with the convention thus and NOT MMYY so there in lies the problem :"MM. Mmm YY" (eg 12. Dec 14) or (01. Jan 15)

So the path to Jan 15 feeder files will include "01. Jan 15" in there, same as Dec 14 will be "12. Dec 14"

Please let me know if you have anymore questions

Many thanks

Hi,

The month/year format is easily enough formattet. What are the criteria for substituting "1214" with "0115"? first time running then every month?
The january file, is "0115"?
and you want to change the "current month convention" in the feeder files to "0115", from the previous "1214"?

is "1214" included in the filepaths to the feeders? or somewhere inside the feeder files.

 

[Arithos]

Could you please show me a File Path to one of theese feeders(including filename.xlsx)? (would show me the format a lot easier)

And explained again, you want to extract info from a feeder file(s) ( A whole month?) based on a dropdown list?

And you would want the whole macro? Meaning you have not started creating any code by yourself?

 

[tljenkin]

Yes I have not done a macro yet.

In short I want to use the drop down in the master file which has months formatted thus : MMYY. This is an input into the macro. So choosing 0115 will mean 01. Jan 15 but in the 2015 folder


Eg. its now January 15 and I want the macro to update all links in the master file so that they change from

X:\01 - Finance\Consolidated View\Trackers\2014\12. Dec 14\T1 Partners\JCI\UKMEA

TO

X:\01 - Finance\Consolidated View\Trackers\2015\01. Jan 15\T1 Partners\JCI\UKMEA

I simply change the drop down from 1214 to 0115 and the macro runs the change.

Thank you

Could you please show me a File Path to one of theese feeders(including filename.xlsx)? (would show me the format a lot easier)

And explained again, you want to extract info from a feeder file(s) ( A whole month?) based on a dropdown list?

And you would want the whole macro? Meaning you have not started creating any code by yourself?

 

[Arithos]

I want the macro to update all links in the master file

Have you been talking about External References ("link") all this time?
And want a macro to change the External Reference strings?

e.g: something like this?

=[COLOR=#333333]X:\01 - Finance\Consolidated View\Trackers\[/COLOR][B]2015\01. Jan 15\T1 Partners\JCI\UKMEA[Workbook.xlsx]Sheet1'!A1[/B]

And simply change parts of all strings?
Or do you want a macro to get information from the sheets?

 

[tljenkin]

I have been talking about external references and changing a part of the string only , correct!

 

[Arithos]

Assuming this is not a one time change, I would use Indirect() together with a formatting (done by a Worksheet_Change event is nice)

Then use formulas like this.

=INDIRECT("[COLOR=#333333]X:\01 - Finance\Consolidated View\Trackers\[/COLOR]"&"[COLOR=#ff0000]B3"[/COLOR]&"[B]\T1 Partners\JCI\UKMEA[Workbook.xlsx]Sheet1'!A1[/B]";TRUE)

Red B3 is a dropdownlist cell/the formatted part of your string, everything else would remain the same.

Should do what you want quite easily, you will have to implement this for each cell with a formula referencing another sheet. (set it up once and drag/copy should do it)

 

[shg]

INDIRECT doesn't work with closed files.

How about Data > Edit Links, Change Source.

 

[tljenkin]

Thats exactly what I am trying to avoid, using Data > Edit Links, Change Source.

Because to change links, I have to do it with so many files one at a time each month.

And I dont want to open all the feeder files so the INDIRECT solution will be a problem as well

Do you see the problem now guys? Thanks