CODEWITHSUNDEEP
vbaexcel
cgnx
cgnx

Reputation: 113

VBA - changing link source macro

I have one workbook(I will call it "master" ) with multiple sheets, each is linked to different workbooks in same folder. Every month I make new folder and copy the content of old folder to the new one. Basically I have folder with path "/path/1"(number represents the month) and every month I want to change it to "/path/2" ...then from "/path/2" to "/path/3" etc. How would I do that using macro? My idea is that I would have a cell with name of the new folder. And my macro would change the path accordingly. Could you help me?

Upvotes: 0

Views: 3334

Answers (1)

lowak
lowak

Reputation: 1284

Ok, your path should something look like this:

ActiveCell.FormulaR1C1 = "='C:\Users\My_user\Desktop\example_folder\1\[Testowanie_fak1.xlsm]Test'!R1C1"

If you want to change the path so your cells will be linked to a different folder all you need to do this is change the formula just a little bit. In this case I added Month(Date) which will automatically add number with current month (so now it will add 8).

ActiveCell.FormulaR1C1 = "='C:\Users\My_user\Desktop\example_folder\" & Month(Date) & "\[Testowanie_fak1.xlsm]Test'!R1C1"

Upvotes: 2

Related Questions