Expert: Miguel Zapico - 11/21/2006

Question
Hi again Miguel, Can I be cheeky & ask 2 questions?
1.   The morefunc addin is not installing on a few PCs, they should be identical and have the software etc?
2. I have about 500 empty/blank excel files numbered from EQ001 to EQ500, how can I copy/save or replace the contents not the names)of these files with the contents of a master test document all in one go, rather than save as 500 times?
Thanks
Dave

-------------------------------------------
The text above is a follow-up to ...

-----Question-----
It's working! It turned out to be the morefunc add-ins. Does this mean I will need to install the morefunc add-in on all pc's?
Thanks so much for your help, this will save me heaps of time.
Cheers
Dave

-------------------------------------------
The text above is a follow-up to ...

-----Question-----
Hi Miguel, Sorry to bother you again, but it still doesn't work? We must be getting close but:)
Dave P
-------------------------------------------
The text above is a follow-up to ...

-----Question-----
Hi Miguel,
Thanks for the quick reply, I've tryed it but I am still having a problem, I have used this formula =INDIRECT.EXT("C:\Student Results 2006\Class 5A\Test 1\[" & A7 & ".xls]Sheet1'!$N$119"). However there seems to be a problem with the A7 part?
I've lowered security levels, allowed macros and ticked the add-ins etc, but no luck yet. What am I doing wrong?
Regards
Dave.
-------------------------------------------
The text above is a follow-up to ...

-----Question-----
Hi Miguel, I am a specialist teacher and have about 500 different student files. In a TOTALS workbook, I would like to be able to type the name/code i.e. EQ001 of a student in a cell i.e A1 and get a total from that students file/workbook in a different cell i.e. A5.
So if I change the student code in A1 it will change the data in A5 accordingly? I hope this makes sense.
Regards
Dave
-----Answer-----
That makes sense, and it possible with the use of INDIRECT.EXT.  It works like the normal INDIRECT formula, but also with closed workbooks.
The examples on the page show static paths, you can make them dynamic with the concatenation operator (&), for example:
=INDIRECT.EXT("C:\My documents\[" & A1 & ".xls]Sheet1'!A5")

Hope this helps,
Miguel.
-----Answer-----
You are doing fine, it was my fault in the first post.  I forgot to put a single quote after the double quote, to ensure that Excel will take the input as text.
Try this version of your formula:
=INDIRECT.EXT("'C:\Student Results 2006\Class 5A\Test 1\[" & A7 & ".xls]Sheet1'!$N$119")
And sorry again, it has been a while since I used this formula.

Hope this helps,
Miguel.
-----Answer-----
We may do a step by step approach.  Let's try the following:
1.- Check if the formula works with a hard-coded reference.  For example, try the following:
=INDIRECT.EXT("'C:\Student Results 2006\Class 5A\Test 1\[EQ001.xls]Sheet1'!$N$119",TRUE)
2.- If this doesn't work, then you may need to review the morefunc installation (if you get the #NAME! error), or ensure that the EQ001.xls file exist on the path (if you get the #VALUE! error).  If it works, then we may try the following:
- Put the reference in cell A2, and use the INDIRECT.EXT formula over that cell:
A1: =INDIRECT.EXT(A2,TRUE)
A2: ="'C:\Student Results 2006\Class 5A\Test 1\[EQ001.xls]Sheet1'!$N$119"
3.- This should work if the step 1 worked.  Now you can modify the cell A2 to refer to a different cell, try something like:
A1: =INDIRECT.EXT(A2,TRUE)
A2: ="'C:\Student Results 2006\Class 5A\Test 1\[" & A7 & ".xls]Sheet1'!$N$119"
A7: EQ001
Now you can decide how you want to have it on your book, with the extra cells or directly on a single formula.

Hope this helps,
Miguel
-----Answer-----
I am happy to hear that works!  Regarding the installation of the add-in, it is needed in all the PCs that have the worksheet that uses the function, it is not needed if the PC just have the original files.
To be safe, I would install it on all the PCs of potential users of the worksheet.

Best,
Miguel.

Answer
On the first question, I don't have an answer, sorry.  I don't know what can make that application fail on the installation, that troubleshooting is outside my expertise.
But I think I can help with the second question.  The short answer is: save it 500 times, but build a code to do it for you.  Here is a code that can do that:

Sub SuperSave()
   Dim sPath, sName As String
   Dim wkbOriginal As Workbook
   
   sPath = "C:\Documents and Settings\User\My Documents\"
   Set wkbOriginal = Application.Workbooks.Open(sPath & "EQ000.xls")
       
   For i = 1 To 500
       sName = "EQ" & Format(i, "000") & ".xls"
       wkbOriginal.SaveAs sPath & sName
   Next
End Sub

This code takes opens a file called EQ000 in the My documents folder of the user "User" (you can change all that values on the rows starting with sPath= and set wkbOriginal), and saves it 500 hundred times with names from EQ001 to EQ500 (the format function takes care of the padding)
To use it, create a new Excel spreadsheet, open the Visual Basic Editor (menu Tools->Macro->Visual Basic Editor), insert a new module (menu Insert->Module) and paste the code there.  Now run the code by pressing F5 and it will make 500 copies of your file in the same directory where this resides.

Hope this helps,
Miguel.