macro to insert file name in cell link (Excel 2000)

Topic

New Reply

I have two spreadsheets. #1 is the customer submission form spreadsheet and #2 is a log of all customer data. On #2 I want to type in the customer name in cell A2. (The customer name is also the name of the customer submission spreadsheet. IE Grassland is the name of the company and Grassland_Submission_Form.xls is the name of the customer’s submission spreadsheet which is linked back to the log.) Is there a way to type in the customer name in A2 and have that name inserted to all the links across the row? Thank you.

Reply | Quote

Replies

You can create a Worksheet_Change event handler for the Prospect Log worksheet that checks if A2 has changed, and if so, replaces the file name in B2:H2.
– Activate the Visual Basic Editor (Alt+F11)
– Double click Sheet1 (Prospect Log) in the Project Explorer.
– Copy the following code into the module that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strSearch As String, strReplace As String
Dim intPos1 As Integer, intPos2 As Integer
If Not Intersect(Target, Range(“A2”)) Is Nothing Then
strSearch = Range(“B2”).Formula
intPos1 = InStr(strSearch, “[“)
intPos2 = InStr(intPos1 + 1, strSearch, “]”)
strSearch = Mid(strSearch, intPos1 + 1, intPos2 – intPos1 – 1)
strReplace = Range(“A2”) & “_Submission_Form.xls”
If Not strReplace = strSearch Then
Range(“B2:H2”).Replace What:=strSearch, Replacement:=strReplace, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End If
End If
End Sub

Note: you’ll get an error message if you type the name of a non-existent file. You could add error handling for this.

Reply | Quote

I used your suggestion and changed the code. However, I can not figure out what the formula should be in B6 for the next (potential) customer . Error messages are OK. thank you.

Reply | Quote

I think I have completely misunderstood your question, sorry about that. Better use Steve’s solution.

Reply | Quote

How about something like this add to the sheet object?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("a2:A65536")) Is Nothing Then
        Dim rCell As Range
        Dim sPath As String
        Dim sFile As String
        sPath = "c:MyPath"
        For Each rCell In Intersect(Target, Range("a2:A65536"))
            sFile = "='" & sPath & "[" & rCell & _
                "_Submission_Form.xls]2004 Submission - Page 1'!"
            Application.DisplayAlerts = False
            rCell.Offset(0, 1).Formula = sFile & "$D$4"
            rCell.Offset(0, 2).Formula = sFile & "$C$10"
            rCell.Offset(0, 3).Formula = sFile & "$J$4"
            Application.DisplayAlerts = True
            Set rCell = Nothing
        Next
    End If
End Sub

When you add or copy anything into A2:A65536 it will add the formula/links to the cells in col b,c and D. Change the path as appropriate.

Steve

Reply | Quote

I am going to try both your solution and Hans. Thank you. What I forgot to explain is that row 2 is one customer, row 3 is another customer, etc. Do I need to change the code to accomodate this?

Reply | Quote

Steve anticipated that, I didn’t. You’d have to adapt my code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strSearch As String, strReplace As String
Dim intPos1 As Integer, intPos2 As Integer
Dim rngCell As Range
If Not Intersect(Target, Range(“A2:A65536”)) Is Nothing Then
For Each rngCell In Intersect(Target, Range(“A2:A65536”)).Cells
strSearch = rngCell.Offset(0, 1).Formula
intPos1 = InStr(strSearch, “[“)
intPos2 = InStr(intPos1 + 1, strSearch, “]”)
strSearch = Mid(strSearch, intPos1 + 1, intPos2 – intPos1 – 1)
strReplace = rngCell & “_Submission_Form.xls”
If Not strReplace = strSearch Then
rngCell.Offset(0, 1).Resize(1, 7).Replace What:=strSearch, Replacement:=strReplace, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End If
Next rngCell
End If
Set rngCell = Nothing
End Sub

Reply | Quote

As Hans mentioned, I anticipated your desire to fill in values in row 2 to 65536 of Col A.

I also have the code “ignore” the dialog (and not display it) if the workbook referenced does not exist. Your formula will result in a error to indicate that the workbook is non-existent.

Steve

Reply | Quote

I am sorry. I can’t get this to work. I created a macro and then pasted in your text. When I run the macro it stops at Option Explicit. Can you help.

Reply | Quote

Here is your workbook with my code inserted.

Reply | Quote

Here is a copy with the macro I listed.

Steve

Reply | Quote

Both your and Hans’ spreadheets worked. However, where do I go to change the code. I only sent you a sample, I need to build the rest of the spreadsheet. I looked in Tools, Macro, but that did not look correct. Please explain. Thank you.

Reply | Quote

The code Steve and I provided is a worksheet-level event handler. It goes into the module belonging to the worksheet it needs to act on. Right-click the worksheet tab and select View Code to see this module. Alternatively, you can double click the worksheet in the Project Explorer in the Visual Basic Editor.

Reply | Quote

When I attempt to change your file location with my file location in the formulas, I get an error message with the formula.
Your formula: =’http://www.wopr.com/w3tfiles/%5BGreenboro_Submission_Form.xls%5D2004 Submission – Page 1′!$D$4
My formula: =C:My documetsCaptives[Greenboro_Submission_Form.xls]2004 Submission – Page 1′!$D$4
Any suggestions?

Reply | Quote

Since you responded to yourself, it’s not clear who the “you” in your post is. The procedure in the workbooks Steve and I attached will only do something if you change a value in column A, and then only in the row of the changed cell(s). It won’t adjust already existing formulas in other rows.

Reply | Quote