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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,384
Members
412,590
Latest member
Velly
Top