Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

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

    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, "<!t>[")
    intPos2 = InStr(intPos1 + 1, strSearch, "]<!/t>")
    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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 340 Times in 333 Posts

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

    How about something like this add to the sheet object?

    <pre>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</pre>


    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

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

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

    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, "<!t>[")
    intPos2 = InStr(intPos1 + 1, strSearch, "]<!/t>")
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 340 Times in 333 Posts

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

    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

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

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

    Here is your workbook with my code inserted.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 340 Times in 333 Posts

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

    Here is a copy with the macro I listed.

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

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

    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.

  12. #12
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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/[Greenboro_Submission_Form.xls]2004 Submission - Page 1'!$D$4
    My formula: =C:My documetsCaptives[Greenboro_Submission_Form.xls]2004 Submission - Page 1'!$D$4
    Any suggestions?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

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

    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.

  14. #14
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •