Actually, it appears the FileSystemObject is limited to 3 special folders...
'--
Function WhereIsIt()
Dim tfolder As Object
Dim fso As Object
Dim tname As String
Const WindowsFolder = 0
Const SystemFolder = 1
Const TemporaryFolder = 2

Set fso = CreateObject("Scripting.FileSystemObject")
Set tfolder = fso.GetSpecialFolder(TemporaryFolder)
tname = tfolder.Path
MsgBox tname
End Function
--
Jim Cone
Portland, Oregon USA



"Keith Norris"
wrote in message
I worked with this more and I found that I just needed to activate the first
wook after I rerferenced the second workbook and it all worked. But I still
would like an explanation as to why it wasn't neccessary to activate the
other workbook to get values from it to check against the other workbook.
Maybe you only have to activate a workbook if you write to it but not if you
are just going to read from it. Is that right? If not, what is the real
reason? Also, can you show me the syntax for getting specialfolders using
the FileSystemObject?

Here is my working code.

Public Sub WMSUserInstallationStatus()
Dim objStartUpBook As Excel.Workbook
Dim objStartUpSheet As Excel.Worksheet
Dim objSuccessfulBook As Excel.Workbook
Dim objSuccessfulSheet As Excel.Worksheet
Dim objShell As Object
Dim strPath As String
Dim intOuterLoop As Integer
Dim intInnerLoop As Integer
Dim strInnerValue As String
Dim strOuterValue As String
Dim strRange As String

Set objSuccessfulBook = ThisWorkbook
Set objSuccessfulSheet = objSuccessfulBook.Sheets("Sheet1")
Set objShell = CreateObject("wscript.shell")
strPath = objShell.specialfolders("MyDocuments") & "\Referrals for Exam
and AM\Transmittals\"
Set objStartUpBook = Excel.Workbooks.Open(strPath & "2009 EFDS StartUp
Distribution List")
Set objStartUpSheet = objStartUpBook.Sheets("TNSNames and WMS")
For intOuterLoop = 2 To 11
strOuterValue =
Left$(UCase$(objSuccessfulBook.Sheets("sheet1").Ce lls(intOuterLoop, 3)), 15)
For intInnerLoop = 8 To 55
strInnerValue = UCase$(objStartUpSheet.Cells(intInnerLoop, 1))
If strInnerValue = strOuterValue Then
strRange = "A" & intOuterLoop & ":F" & intOuterLoop
objSuccessfulBook.Activate
objSuccessfulSheet.Names.Add Name:="Data", _
RefersTo:="=" & strRange
objSuccessfulSheet.Range("Data").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
objStartUpSheet.Cells(intInnerLoop, 5) = strOuterValue
Exit For
End If
Next intInnerLoop
Next intOuterLoop
End Sub
--
Thank You,
Keith