Furmula Question

[kluitna]

A quick question on setting up variables in a formula.

I have the current formula:

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

I am using it to link several workbooks together. That have several worksheets in them.

"strfile" is the workbook file name, This works just fine. My problem is I can't seem to get the formula on the next pass throughthe loop to look at the next worksheet in "strfile". Does anyone know the syntax to look at the same file and the next sheet in a file.?

Any ideas are much appreciated.

 

[Al Chara]

The following code should help to get the worksheet names:

Set STRFILE = ThisWorkbook
For I = 1 To ThisWorkbook.Worksheets.Count
MsgBox STRFILE.Worksheets(I).Name
Next I

If you need help to apply this to your situation, then post again.

Hope this helps.
Kind regards, Al.
This message was edited by Al Chara on 2002-03-18 10:57

 

[kluitna]

[No message]

 

[kluitna]

You have me on the right track Al. The problem is when I use the command Thisworkbook, it trys to look into the active workbook which is the one I am trying to link the data into.

ActiveCell.FormulaR1C1 = "='[Est Source Item.xls]Estimate (1)'!R21C4"

Above is the formula with the hard values in it for the given soucre file and sheet number. There is as many as 70 source files and each one has 30 estimates. I can loop though the source files with out a problem, but when but hav e not been able to successfuly right a loop inside the source file loop to go through each estimate.

Hope this is clearer, it seems very simple but for some reason it eludes my even simpler mind.

 

[Al Chara]

Are the workbooks all open when you want to run this macro?

 

[kluitna]

Al, The only workbooks that are open is the one I am getting the the data from and the one I am liking to, Just had thought, the one I am gettinf the date from does not have to be open to establish the link. Or does it?

 

[Al Chara]

Does this help:

Dim strfile As Workbook
For h = 1 To Workbooks.Count
Set strfile = Workbooks(h)
For i = 1 To strfile.Worksheets.Count
ActiveCell.Value = strfile.Worksheets(i).Range("d24")
ActiveCell.Offset(1, 0).Select
Next i
Next h

The code loops through all the open workbooks and their sheets and places cell D24 in the activecell.

Kind regards, Al.

 

[Al Chara]

Workbooks do not have to be open to read them, but remember a closed workbook can only return a value.

 

[kluitna]

Al, that codes works thanks. one question, how would i modiofy that same code if I did not want to have any workbooks open except for the one I am using. also the code you provide works well. My problem is closing all the given workbooks after the data has been extracted. I replaced your h loop with a dim called source wich is linked to a series of cells with file names. I have tried every in the help section to makethis work. I just get code errors,

Again, thanks allot , been a big help. I have almost got this thing done.

 

[Al Chara]

Did you read my response here:

http://www.mrexcel.com/board/viewtopic.php?topic=2657&forum=2&1

Question: If you don't have the workbooks open, then when and from where do you get the names of the workbooks?