Can I automate this???

srburk

Board Regular
Joined
Mar 25, 2002
Messages
154
I've got a big tasks awaiting me at work.

I have 100 cost centers. Each manager reporting to me must evaluate their cost centers and explain variances between actuals and budgets. They must develop action plans to rectify any unfavorable variances. Each file contains multiple links to files on a network drive that shows actual data. All of the links have been placed on a menu page. There are about 50 links.

I created a template file for the first cost center. I must duplicate this file for each cost center. My problem is this, I must update the links for each cost center so that they point to the appropriate data. The files to which the links post are different for each cost center.

Example of file name located on network drive:
t:/departments/2313/cost_center_2313-variance _FY2005_Period01.xls

Example of file for another cost center (only directory and file name changes):
t:/departments/3225/cost_center_3225-variance _FY2005_Period01.xls

Is there any way to automate the creation of each new cost center's file with the new links? Or, is there anyway to edit these hyperlinks without having to "edit" each hyperlink individually?

Scott
Houston, TX
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Scott

Can you use the Edit, Links, Change Source to change the links on the template to the new source, then save the template as the required file?


Tony
 

srburk

Board Regular
Joined
Mar 25, 2002
Messages
154
acw said:
Can you use the Edit, Links, Change Source to change the links on the template to the new source, then save the template as the required file?

I am unable to use the edit/links in this situation.

Scott
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Scott

How about the edit / replace? If these are not available, could you advise why?? It may be possible to circumvent the reason.


Tony
 

srburk

Board Regular
Joined
Mar 25, 2002
Messages
154

ADVERTISEMENT

acw said:
How about the edit / replace?

I tried the edit/replace and it only replaces the displayed text. It doesn't replace the hyperlink.

***displayed example***
TRENDS
Trends 01 Trends 07
Trends 02 Trends 08
Trends 03 Trends 09
Trends 04 Trends 10
Trends 05 Trends 11
Trends 06 Trends 12

Each period has an assigned hyperlink. The first
t:\data1\Departments\2290\Trend_2290 FP1_06.xls
the second goes to:
t:\data1\Departments\2290\Trend_2290 FP2_06.xls

Now, I could go in and change each of these hyperlinks manually but there are 48 on each menu. Each cost center has one menu. I have over 100 cost centers.

Not certain why the EDIT LINKS feature doesn't work. I can click on edit but I can't click on links.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

We may have a terminology mixup here. Are you talking about a cell that is linked to another file to bring in data, or a hyperlinked that when selected will open another file, but doesn't bring data into a cell????


Tony
 

srburk

Board Regular
Joined
Mar 25, 2002
Messages
154

ADVERTISEMENT

acw said:
Are you talking about a cell that is linked to another file to bring in data, or a hyperlinked that when selected will open another file, but doesn't bring data into a cell????

I am speaking of a cell (actually multiple cells) that are hyperlinked. No data is brought into the cells.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
HI

It should be possible to build a loop that will take the existing links, and change the relevant parts for both the address and text to display.

Using your original example
t:/departments/2313/cost_center_2313-variance _FY2005_Period01.xls
and assuming that you want to change the 2313 to 3225 it would be something like (untested)
Code:
newval = 3225
for i = 1 to 48
holder = activesheet.hyperlinks(i).address
holder = worksheetfunction.substitute(holder, 2313,newval)
activesheet.hyperlinks(i).address = holder
activesheet.hyperlinks(i).texttodisplay = holder
next i

Update the value of newval for each new template. The existing 2313 should be the same for existing entries in template.


HTH

Tony
 

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
Probably not directly relevant to your needs, but you got me to thinking of other ways to gather a list of folder names, make new sheets with those names and put formulas onto the sheets referencing files in that folder.

(too much time on my hands)

Code:
Dim myText As Variant
Dim myFolders As Variant
Dim numFolders As Long
Dim found As Boolean

directory = "t:\departments"                        'set to the path name you want to search
numFolders = 0

With Application.FileSearch
.NewSearch
.LookIn = directory
.Filename = "cost_center_*.xls"                     'the filenames we are looking for
.SearchSubFolders = True
.Execute

    If .FoundFiles.Count > 0 Then                   'if we found some files
        ReDim myFolders(1 To .FoundFiles.Count)     'resize the array to hold the folder names
        For i = 1 To .FoundFiles.Count              'loop through the found files
            myText = Split(.FoundFiles(i), "\")     'split the path name up into bits using the "\" as a delimiter
            
            found = False
            
            For j = 1 To UBound(myFolders)          'check we haven't already found that folder name
                If myText(2) = myFolders(j) Then
                    found = True
                End If
            Next j
            
            'NOTE: myText(0) will hold "t:"
            'myText(1) will hold "departments"
            'myText(2) will hold the sub directory names
            
            If found = False Then                   'if its a new folder name, add it to the myFolders list
                numFolders = numFolders + 1
                myFolders(numFolders) = myText(2)
            End If
        Next i
    End If
End With

'You can then use the list of folder names in a Loop to create a new copy of a blank template sheet and give it the cost centre name
'then overwrite write formulas on the new sheet to insert the different folder names where needed

For i = 1 To numFolders
    Sheets("myTemplate").Copy After:=Sheets("myTemplate")       'make a copy of the Template sheet
    ActiveSheet.Name = myFolders(i)                             'rename it to the folder name
    
    'lets write a formula to the new sheet using the folder name in the path of the file to get data from
    ActiveSheet.Cells(1, 3).Formula = "=A1 * " & "'t:\departments\" & ActiveSheet.Name & "\[cost_center_2313-variance _FY2005_Period01.xls]Sheet2'!$B$5"
    
Next i
 

srburk

Board Regular
Joined
Mar 25, 2002
Messages
154
HI

It should be possible to build a loop that will take the existing links, and change the relevant parts for both the address and text to display.

Using your original example
t:/departments/2313/cost_center_2313-variance _FY2005_Period01.xls
and assuming that you want to change the 2313 to 3225 it would be something like (untested)
Code:
newval = 3225
for i = 1 to 48
holder = activesheet.hyperlinks(i).address
holder = worksheetfunction.substitute(holder, 2313,newval)
activesheet.hyperlinks(i).address = holder
activesheet.hyperlinks(i).texttodisplay = holder
next i

Update the value of newval for each new template. The existing 2313 should be the same for existing entries in template.


HTH

Tony

I am using this macro again. This time, it doesn't address every cell's hyperlink. How can I get it to work for every cell located within B5:S21 on one particular worksheet?

Scott
 
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,167,834
Messages
5,855,905
Members
431,772
Latest member
dannyboi1

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