Making headway on formula

[kluitna]

Thanks to Al, I have made some head way on placing variables in this formula.

strfile = Source
ActiveCell.FormulaR1C1 = "='" & strfile & "'!R21C4"

I adjusted my code so it will not open the source file. It made the the formula work well and a popup box come up and reqested the sheet in the given file I wanted to link. The question now is how do I get it so it just automatically goes through each sheet and links the data..

I am so close, and again thanks al you inderectly eleminate about 5 lines of code and solved a couple of other probs I had when I realized I did not need the file open.

[Al Chara]

You can also follow the following code by Jake Marx:


VBA CODE EXAMPLES

by Jake Marx

??? read the names of all Sheets in a closed workbook ???

Here's a way to do it through ADO (ActiveX Data Objects) in Excel
2000. To use this code, you must first set a reference to "Microsoft
ActiveX Data Objects 2.5 Library" and "Microsoft ADO Ext. 2.5 for DDL and
Security".

Sub ReadSheetNames(TheCompleteFilePath As String)
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

cnn.Open "Provider=MSDASQL.1;Data Source=" _
& "Excel Files;Initial Catalog=" & TheCompleteFilePath
cat.ActiveConnection = cnn
For Each tbl In cat.Tables
MsgBox Left$(tbl.Name, Len(tbl.Name) - 1)
Next tbl

Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Sub


_________________
Hope this helps.
Kind regards, Al.