[AccessD] Excell Question on Reference to Named Cell

I use the following function to change existing links every month to a
new spreadsheet. The required path strings, etc. are in cells in the
"Dates" worksheet. Maybe some tinkering with this will get you where you
want to go.
HTH
Jim Hale

Sub Linkfiles()
 Dim x As Integer, strCompletedDir As String, strOldlink As String
 Dim rngTemp As Range, wksInput As Worksheet, strWBook As Workbook
 Dim strFilenameold As String, strFilenamenew As String, strdir As
String
 
On Error GoTo Err_Process
Set wksInput = Worksheets("Dates")
Set strWBook = ActiveWorkbook
    Dim lngRetval As Long
    lngRetval = MsgBox( _
        "Do you want to Link the new month's files?", _
        vbYesNoCancel + vbQuestion + vbDefaultButton1, "File Linking")
Select Case lngRetval
Case vbYes
    Application.Calculation = xlManual
    Set rngTemp = wksInput.Range("G18")
    strdir = wksInput.Range("G18").Value
  For x = 1 To 2
    strFilenameold = rngTemp.Offset(x, 0).Value
    strFilenamenew = rngTemp.Offset(x, 1).Value
    strCompletedDir = strdir & strFilenamenew
    strOldlink = strdir & strFilenameold
    Workbooks.Open strCompletedDir, 0 'open workbook to link to
    strWBook.Activate
    'change link to new file
    strWBook.ChangeLink strOldlink, strCompletedDir, xlExcelLinks
    Workbooks(strFilenamenew).Close SaveChanges:=False
  Next x
    MsgBox "The files were successfully linked"
Case vbNo
    GoTo The_End
Case vbCancel
    GoTo The_End
End Select
The_End:
    Application.Calculation = xlAutomatic
    Set rngTemp = Nothing
    Set wksInput = Nothing
    Set strWBook = Nothing
Exit Sub

Err_Process:
If Err.Number = 1004 Then
    MsgBox "The links were already updated"
Else
    MsgBox Err.Number & " " & Err.Description
    MsgBox "The Links were NOT updated"
End If
Resume The_End
End Sub

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith
Williamson
Sent: Tuesday, June 19, 2007 4:44 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excell Question on Reference to Named Cell


Anybody ever use this Indirect Function?  I've just incorporated it in a
Master Spreadsheet to bring in data from multiple subsidiary worksheets.
However, I am finding that once I close the subsidiary worksheet....my
data from the Indirect function disappears.  It seems like it only
populates while the other worksheet is open.

Does anyone know a comparable function, that does not require the other
worksheet to be open, at the same time?

Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com

RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
21231-3305

410-537-6098 direct | 410-276-4182 fax | www.rtkl.com


***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.