Problems with referring to workbooks
 

I need to have the user select multiple workbooks and then compare data on them

I get a subcript out of range error on the last line,

Workbooks(fName(1)).Worksheets("Sheet1").Range("A1 :C1").Value = name(1

It seems workbooks name property has to be of the form "book1.xls" not a directory like returned by getopenfilename (C:\...\book1.xls

How can I refer to the workbooks for comparison/formatting changes (ie highlighting differences) besides the index number (which will cause problems if the user has other books already open.

Thanks in advance

Matt Lawso


Dim fName(5) As Varian
Dim x As Intege
Dim name(5) As Varian

For x = 1 To
fName(x) = Application.GetOpenFilename("Excel Workbooks (*.xls),*.xls"
Nex

For x = 1 To
Set myBook = Workbooks.Open(Filename:=fName(x)
Workbooks.Open Filename:=fName(x
Nex

Workbooks(fName(1)).Worksheets("Sheet1").Range("A1 :C1").Value = name(1

Problems with referring to workbooks
 

Function ReturnName(sFname)
Dim sName As String, i As Long
sName = ""
If InStr(sFname, "\") = 0 Then
ReturnName = sFname
Exit Function
End If
For i = Len(sFname) To 1 Step -1
If Mid(sFname, i, 1) < "\" Then
sName = Mid(sFname, i, 1) & sName
Else
Exit For
End If
Next
ReturnName = sName
End Function

then
workbooks(ReturnName(fName(1)).worksheets( _
"Sheet1").Range("A1:C1").Value = name(1)

--
Regards,
Tom Ogilvy


"Matt Lawson" wrote in message
...
I need to have the user select multiple workbooks and then compare data on
them.

I get a subcript out of range error on the last line,


Workbooks(fName(1)).Worksheets("Sheet1").Range("A1 :C1").Value = name(1)

It seems workbooks name property has to be of the form "book1.xls" not a
directory like returned by getopenfilename (C:\...\book1.xls)

How can I refer to the workbooks for comparison/formatting changes (ie
highlighting differences) besides the index number (which will cause
problems if the user has other books already open.)

Thanks in advance,

Matt Lawson



Dim fName(5) As Variant
Dim x As Integer
Dim name(5) As Variant

For x = 1 To 5
fName(x) = Application.GetOpenFilename("Excel Workbooks (*.xls),*.xls")
Next

For x = 1 To 5
Set myBook = Workbooks.Open(Filename:=fName(x))
Workbooks.Open Filename:=fName(x)
Next

Workbooks(fName(1)).Worksheets("Sheet1").Range("A1 :C1").Value = name(1)