Can I automate this???

[srburk]

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

 

[acw]

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]

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]

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]

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]

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]

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]

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)

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]

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)

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]

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)

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