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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » macro to insert file name in cell link (Excel 2000)

    Author
    Topic
    #414778

    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.

    Viewing 1 reply thread
    Author
    Replies
    • #923139

      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.

      • #926201

        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.

        • #926217

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

    • #923138

      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

      • #923147

        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?

        • #923151

          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

        • #923155

          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

      • #923195

        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.

        • #923214

          Here is your workbook with my code inserted.

        • #923230

          Here is a copy with the macro I listed.

          Steve

          • #923496

            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.

            • #923498

              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.

            • #925559

              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?

            • #925560

              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.

    Viewing 1 reply thread
    Reply To: macro to insert file name in cell link (Excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: