Consulting

Results 1 to 6 of 6

Thread: Cell To show Data rather than a reference link

  1. #1

    Solved: Cell To show Data rather than a reference link

    I know it can be done but have just ha a brain f%# (Gas).

    I have a worksheet that has over 100 cells linked to another workbook that is not open. I NEVER want to "update" the data once it is read the first time. What is the easiest way to insert the data referenced rather than the link?

    ttfn
    Kicker
    Last edited by Kicker; 07-29-2004 at 06:53 PM. Reason: solved

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    Copy, Paste Special / Values. With so many, it kind of depends on how your data is structured. I guess we couldn't get lucky enough that the sheets are all in order with the formula(s) in the same cell address?

  3. #3
    Unfortunately NO.

    Here is basically what I have.

    Master workbook
    Shift 1 workbook
    shift 2 workbook
    Shift 3 workbook

    Each shift workbook will have 14 worksheets with specific names depending on the date. Cols A, D, and E (out of A-J) are important to the Master workbook.

    Daily (or periodically), I will need to get the information from each shift workbook and put it in a different location in the master workbook.

    I already have it working with finding and copying ranges and the paste special into the master workbook. I was just hoping that I could do it an easier way.

    ttfn
    Kicker

  4. #4
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hey Kicker,

    Try the following sub:

    [vba]Sub StopSpecificExternalLink()
    Dim cL As Range
    Dim extLinkDir As String, extLinkFileName As String, extLink As String

    'Either of these two variables can be blank, but not both
    extLinkDir = "C:\temp\"
    extLinkFileName = "filename.xls"

    If extLinkDir = "" And extLinkFileName = "" Then Exit Sub
    If extLinkDir <> "" Then
    If Right(extLinkDir, 1) <> Application.PathSeparator Then
    extLinkDir = extLinkDir & Application.PathSeparator
    End If
    End If
    If extLinkFileName <> "" Then extLinkFileName = "[[]" & extLinkFileName & "[]]"
    extLink = "*" & extLinkDir & extLinkFileName & "*"

    For Each cL In ActiveSheet.UsedRange.Cells
    If UCase(cL.Formula) Like UCase(extLink) Then cL = cL.Value
    Next
    End Sub[/vba]

    Matt

  5. #5
    I "see" said the blindman. Matt...I think that will work. thanks a million

    ttfn
    Kicker

  6. #6
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Not a problem, I'm sure there are faster ways to do it, but that was the best I could think of. Let me know if you need any help with it!

Posting Permissions

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