Embedded Excel Object - Updating Formulas

[bradlehoux]

I have an excel file I use daily for work that I give an updated file name every few days by changing the date, i.e. PO Log 2018-10-08 would become PO Log 2018-10-10. This helps me feel better about losing minimal data in an unforeseen circumstance.

The workbook has a lot of tabs and information and can sometimes the data displayed can become a bit overwhelming so what I tried doing was embedding a separate excel file as an object inside the main parent file to clean the look up a bit. This embedded file is basically a table that I don't need to reference very often but I would like to have the parent document be able to pull data from the embedded document and vise versa. This works great when the formulas are first set up but as soon as I rename the parent document, the formulas in the embedded document always link back to the old revision of the parent file instead of the newly revised parent file name which is the file its embedded in.

Is there a workaround for this to force excel to always pull data from the file it's embedded in rather than it pulling an external reference from the outdated file name source workbook?

Any help would be appreciated - thanks

[Pauleyb]

For stuff like this, I usually check out the =CELL() function. I played around a bit with an embedded spreadsheet and didn't find anything obvious as the "filename" parameter seemed to show the last one you input.

Barring that, a macro could look at your files (assuming you use a standard naming convention) and determine the most recently updated file or be smart enough to parse the filename. It would then update your formulas. Still a bit messy, as I'm not quite sure how the worksheet open event would handle embedded tables.

[bradlehoux]

I have yet to come up with a viable solution so I am thinking VBA might be the way to go.

I started searching around and came up with this as a starting point with the idea that I can set named ranges in the parent workbook that the embedded workbook can reference. I feel like I am onto something but still have a long way to go.

Please Login or Register  to view this content.

I can get this code to bring up the correct messageboxes when I run the code in the parent workbook but I get a "run time error 1004" when I run it via the embedded workbook. I was hoping somebody could help point me in the right direction as I am a novice when it comes to VBA.

[bradlehoux]

I realized I cannot use my earlier approach because the named ranges are not visible to the embedded sheet. As a workaround I've come up with a solution that will work except that I can't get a piece of code to run in the embedded sheet because for some reason the embedded sheet does not retain any macro or code information (after every save the code I enter disappears as though it was never there).

The code below I placed in the parent worksheet. I have the code set to run when I click a button (all it does is open the embedded workbook which it does just fine).

Please Login or Register  to view this content.

The above code works fine it's the code below I am having an issue with. I've tried having the code run in sequence with the code above, as a second button in the embedded file and have the code run with the workbook_open event but I have had no luck with either approach (there is probably something simple and obvious I am missing). All I am trying to do is populate a cell with either the parent filename or the embedded file name when the file is opened or when I click a button - from there I have other formulas in the spreadsheet that pull that information and retrieve the data I need. Unfortunately the built in =CELL() function can't seem to return the filename I need (maybe if I can get that to work it could solve my issues). Here is what I have for now:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please any help is appreciated. Thanks.

[bradlehoux]

I found a messy workaround using the User Defined Function below

Please Login or Register  to view this content.

I had to store the UDF in the PERSONAL MACRO WORKBOOK and then entered the following formula in a cell that references the UDF

Please Login or Register  to view this content.

For some reason I cannot store macros in an embedded workbook so this was the only solution I could come up with.