Dynamic link to workbook

[peterjohansson.sthlm]

I have a overview workbook (Status.xls) were I compile data from other
source workbooks (artnr1.xls, art.nr2.xls, ...). All source workbooks
look the same, it's just the data that differs. When a new source
workbook is created it's saved as [article number.xls] eg, 11134.xls

In the overview workbook I've listed the data according to;

A B C D ...
Art1 Data1 Data2 Data3 ...'¨
Art2 Data1 Data2 Data3 ...
Art3 Data1 Data2 Data3 ...
Art4 Data1 Data2 Data3 ...

What I want is, when I write a new article in the A column (eg.
11134), data should be collected from the workbook with corresponding
file name =11134.xls

This should happen automaticly without having to modify or copy the
formula for every new article that's created.
I've gotten this to work with =INDIRECT but then every workbook have
to be open and then you've lost the purpose.

Is there any way to solve this problem or do you have to copy and
modify the formula for every new article that's added? How can you
(with vb) take the art.nr and use it in the link.

The formula will always be eg. [xxxxx.xls]Sheet1'!$D$5 so when I input
eg. 11134 as for a new article I want the formula to change to
[11134.xls]Sheet1'!$D$5.

What I need is =INDIRECT but with the functionallity of a ordinary
external link that askes for update when you open the document and
holds that value until you update again.

best regards
/Peter

 

[Bill Manville]

Through the UI, just copy an entire row (say for art 11111 to the
bottom of your summary table then select the new row and Edit / Replace
/ 11111 / with: 11134 / Replace All.

If you wanted to use a macro, you could detect a change in column 1 of
the worksheet and copy the row above, and then do the replace, as
above. Something like this, in the worksheet's module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Cells.Count = 1 And Not
IsEmpty(Target) And Target.Row > 1 Then
' copy down the formulas from the row above
With Target.Offset(, 1).Resize(, Columns.Count - 1)
.FillDown
.Replace Target.Offset(-1).Value, Target.Value, xlPart
End With
End If
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

 

[peterjohansson.sthlm]

Through the UI, just copy an entire row (say for art 11111 to the
bottom of your summary table then select the new row and Edit / Replace
/ 11111 / with: 11134 / Replace All.

If you wanted to use a macro, you could detect a change in column 1 of
the worksheet and copy the row above, and then do the replace, as
above. Something like this, in the worksheet's module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Cells.Count = 1 And Not
IsEmpty(Target) And Target.Row > 1 Then
' copy down the formulas from the row above
With Target.Offset(, 1).Resize(, Columns.Count - 1)
.FillDown
.Replace Target.Offset(-1).Value, Target.Value, xlPart
End With
End If
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

-----------------------------------------------------------------

Thanks Bill, workes like a charm + a very clean script!!!

I run into a new problem that I'm hoping you also could help me with.
Prior I've used a script (se bellow) to find and list files in a
specified directory. Now, I want to look into a URL insted of a
folder. I've tried by changing the line;
..LookIn = "F:\"
to
..LookIn = "URL;http..."
but this doesn't work

Do you have any idea on how to make this work?

Best regards
/Peter

---------------------------------------------------------------
Dim pathWanted As String
With Application.FileSearch
.NewSearch
.LookIn = "F:\"
.SearchSubFolders = True
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
---------------------------------------------------------------

 

[Bill Manville]

Now, I want to look into a URL insted of a

folder. I've tried by changing the line;
..LookIn = "F:\"
to
..LookIn = "URL;http..."
but this doesn't work

I'm not surprised it doesn't work - any more than it does in File >
Open or in Windows Explorer's address bar.

I don't think it's possible to access a Windows file system via a URL
which is presumably what you are trying to do. But I'm not an expert
on web matters.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup