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

Havok390

New Member
Joined
Oct 21, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
438
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
You want to dynamically (based on name of workbook) link into cell A1 from other sheet, right?
 

Havok390

New Member
Joined
Oct 21, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Apr 8, 2019
Messages
438
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
So cell have to be linked into:

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,043
Messages
5,767,797
Members
425,436
Latest member
MSPaperclipMan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top