Excel 2000 Linked Spreadsheets

[KenReay]

Hi

I have inherited a spreadsheet which produces a weekly analysis from a series of daily spreadsheets. The daily spreadsheets are one (.xls) file per day. The file name and folder location changes each day. Sometimes there is no file for a given day (eg Saturday and or Sunday are often intentionally missing)

At present some poor soul is re-making the links each day to adjust for the changing folder and file name(s).

I have tried automating the relinking using .ChangeLink.

What I would really like to do is to automate placing the individual link paths in the cells, so that I could first check for missing files.

I am reasonably competent in VBA, but more used to Access object model than Excel.

Anyone done something like this willing to share how they did it?

Thanks in advance



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SkipVought]

Ken,

Instead of linking (I HATE LINKS!!!)...

try Data/Get External Data/Import data or Data/Get External Data/New Database Query. Both can be used with external Excel workbooks. The import/query wizard will prompt you.

Then add a macro to COPY the imported or queried data to the sheet you want to work on.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[KenReay]

Hi

Thanks Skip, I will look into that....

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi Again Skip

Have experimented with using Data/Get External Data/Import data etc, but without success, the daily spreadsheets used for data input are not tabular, they are set up to look like a paper form. The Import commands appear to expect data in a tabular form.

However whilst playing around I have discovered it is possible to set a "link to address" in code ... so back to playing (sorry evaluating and researching I meant to say)

Regards

Ken

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[xlbo]

Ken - I share Skip's dislike of linking but to be honest, to do it any other way will require a complete rework of the report. For the question in hand, I have never sone anything exactly as you describe but I have some code that may help you on the way. the following is useful for dealing with file attributes:

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("A65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
        ' display file properties
        Cells(r, 1).Formula = FileItem.path & FileItem.Name
        Cells(r, 2).Formula = FileItem.Size
        Cells(r, 3).Formula = FileItem.Type
        Cells(r, 4).Formula = FileItem.DateCreated
        Cells(r, 5).Formula = FileItem.DateLastAccessed
        Cells(r, 6).Formula = FileItem.DateLastModified
        Cells(r, 7).Formula = FileItem.Attributes
        Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName

        r = r + 1 ' next row number
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.path, True
        Next SubFolder
    End If
    Columns("A:H").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

Sub ListAllFiles()
ListFilesInFolder SourceFolderName:="\\Lothian\Knowledge", IncludeSubfolders:=True
End Sub

You will then probably need to make use of the FIND method to look at the filenames picked up and compare them to your list of expected names. Something like this would probably work (assuming range names have been defined for your list of expected files "Expected_Files" and your list of files found by the (hopefully) the code above "Found_Files"):

dim myCel as range, fCell as range
with Range("Found_Files")
 For each myCel in Range("Expected_Files")
  set fCell = .find(myCel,lookin:=xlvalues,lookat:=xlwhole)
  if not fCell is nothing then
    'File is found - process linking etc
  else
    'Filename not found
  end if
 next
end with

Hope this helps somewhat...


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenReay]

Hi Geoff & Skip

I am making progress now thankyou, I got on off to a bad start by trying to define a function which returned a link name and setting cell (Value) to =FunctionName(), but my brain has now clicked into ger and I realise I should be setting .Formula = FunctionName(),

Thanks for the pointers, that was what I needed a nudge in the right direction

Regards

Ken

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK