Problems with referring to workbooks

[Guest]

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

 

[Tom Ogilvy]

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

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.)