VBA that will update all existing formulas path and name to X

[Havok390]

Imagine I have my original workbook called 001.xlsx, in cell A1 I have the below formula already saved:

='C:\Users\mbxyz\File_001\[001.csv]Sheet1'!A1

This formula is returning the value from that specific cell, from that new workbook, from that folder.
Now I need the 001 to be a dynamic value... So if my original workbook was named 999333 and saved in a folder titled 999333 then it would update the original and existing formula to:

='C:\Users\mbxyz\File_999333\[999333.csv]Sheet1'!A1

The concept here is that I want an inexperienced staff member to download a new CSV and name it 123456.csv in its corresponding folder.
Once that action has been done a different staff member can then save a file called 123456.xlsx into the same folder. Then when they open 123456.xlsx they will activate a VBA macro from their personal macro workbook which will update the formula based on those above variables.

It would be amazing if the VBA macro only updated the variables in all of the formulas that were already in the spreadsheet, so if there were any changes to spreadsheet it would automatically apply the variable change to everything. In the above example I am only asking it to update one cell (A1) this would allow me to then update all cells and their formulas to the respective variable.

Thank you all so much for reading my question and I hope this made sense, if you have any questions please of course don't hesitate to ask

 

[KOKOSEK]

You want to dynamically (based on name of workbook) link into cell A1 from other sheet, right?

 

[Havok390]

You want to dynamically (based on name of workbook) link into cell A1 from other sheet, right?

Maybe I am looking at this with the assumption of that's how it needs to be done.
Imagine you got a trashy CSV file that contains all your data. Then you have an amazing template XLSX with all this pretty formatting just waiting to be populated from the CSV.
You know the value of A1 on XLSX file will always be the value of A1 on the CSV, and A2 will be B6, and A3 will be B12 etc. those are static

So how do I link the data between the two spreadsheets? Well I use this formula:

='C:\Users\mbxyz\Desktop\File_123456\[123456.csv]123456'!$A$1

So that works once, what happens when the file name changes and is saved in a new file path, how am I going to quickly update all of the above file names/paths across to that of the new location/name so it pulls the correct data?

P.S. In my original post I had the sheet name as Sheet1. CSV does not allow you to save the sheet as anything other than the file name, so it should have read:
='C:\Users\mbxyz\File_999333\[999333.csv]Sheet1999333'!A1

 

[KOKOSEK]

So cell have to be linked into:

='C:\Users\mbxyz\Desktop\File_WORKBOOKNAME\[WORKBOOKNAME.csv]WORKBOOKNAME'!$A$1