Error with Range Object

[Keith Norris]

Hi Helpers,

I'm trying to compare values from one spreadsheet to another and highligh
the cells in the original if they match. I've tried many things with the
range object but it doesn't work. I've narrowed it down to what I think the
problem is.

If I reference more than one workbook, it will fail. If I only reference
the original workbook, it works fine. Can you explain to me why this is?

Here is the narrowed down code. This code will work fine but if I uncomment
the reference to the other workbook it fails. Why is that? What can I do to
allow this? I'm using Excel 2003 but I would think 2007 can handle it the
same way.

Dim objBook As Workbook
Dim objSheet As Worksheet
Dim objOtherBook As Workbook
Dim objOtherSheet As Worksheet
Dim objShell As Object
Dim strPath As String

strRange = "A2:F2"
Set objBook = ThisWorkbook
Set objSheet = objBook.Sheets("Sheet1")
Set objShell = CreateObject("wscript.shell")
strPath = objShell.specialfolders("MyDocuments") & "\Referrals for Exam
and AM\Transmittals\"
'Set objOtherBook = Excel.Workbooks.Open(strPath & "2009 EFDS StartUp
Distribution List")
'Set objOtherSheet = objOtherBook.Sheets("TNSNames and WMS")

objSheet.Names.Add Name:="Data", RefersTo:="=" & strRange
objSheet.Range("Data").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

 

[Jim Cone]

You might try it like this...
(the MsgBox is used, while writing the code, to verify the path is legitimate )
'--
strPath = objShell.specialfolders.Item("MyDocuments") & _
"\Referrals for Exam and AM\Transmittals\"
MsgBox strPath
'--
Also, Make sure you specify the complete name of the workbook you
want to open... "TheWorkbookName.xls"
You can also use the FileSystemObject to access special folders.
--
Jim Cone
Portland, Oregon USA



"Keith Norris"
<[email protected]>
wrote in message
Hi Helpers,
I'm trying to compare values from one spreadsheet to another and highligh
the cells in the original if they match. I've tried many things with the
range object but it doesn't work. I've narrowed it down to what I think the
problem is.

If I reference more than one workbook, it will fail. If I only reference
the original workbook, it works fine. Can you explain to me why this is?

Here is the narrowed down code. This code will work fine but if I uncomment
the reference to the other workbook it fails. Why is that? What can I do to
allow this? I'm using Excel 2003 but I would think 2007 can handle it the
same way.

Dim objBook As Workbook
Dim objSheet As Worksheet
Dim objOtherBook As Workbook
Dim objOtherSheet As Worksheet
Dim objShell As Object
Dim strPath As String

strRange = "A2:F2"
Set objBook = ThisWorkbook
Set objSheet = objBook.Sheets("Sheet1")
Set objShell = CreateObject("wscript.shell")
strPath = objShell.specialfolders("MyDocuments") & "\Referrals for Exam
and AM\Transmittals\"
'Set objOtherBook = Excel.Workbooks.Open(strPath & "2009 EFDS StartUp
Distribution List")
'Set objOtherSheet = objOtherBook.Sheets("TNSNames and WMS")

objSheet.Names.Add Name:="Data", RefersTo:="=" & strRange
objSheet.Range("Data").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

 

[Keith Norris]

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").Cells(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

 

[Jim Cone]

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" <[email protected]>
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").Cells(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