Making headway on formula

kluitna

Board Regular
Joined
Mar 10, 2002
Messages
75
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top