I'm trying to consolidate data from the first worksheet
in one Excel file (the 'source' file - Book1.xls) into
another (the 'opened' file - Book2.xls). To do so, I
need the Range of the data in the source file. I have
a 'LastCell' Function that finds the last row and the
last cell having data on a worksheet and I want to use it
on the source file.

I've opened an Excel workbook and I have the following
code under a button on a form.

Private Sub CommandButton4_Click()
Dim LastRow As Long, LastCol As Integer
Dim wb As Workbook, ws as Worksheet

Set wb = "C:\Documents and Settings\Dad\My
Documents\Book1.xls
Set ws = wb.Sheets(1)

LastRow = LastCell(ws).Row
LastCol = LastCell(ws).Column

End Sub

I can then use the LastRow,LastCol to set the Range on
the source file.

However, when I run the above code, I get a "Type
Mismatch" error before the Subroutine code fires. If I
change the 'set' statement to:

Set wb = Book1.xls

I get an "Object Required" error when the set statement
fires.

Can someone tell me the proper way to 'look into' an
unopened Excel file to get the information I want? It
may have something to do with the syntax I'm using to set
the wb variable - I'm not sure.

Thanks,
Rick

Chip -

Thanks, that worked great! I use a CommonDialog to
populate a listbox with files to be consolidated; then I
use the list to set a source array for the consolidate
method. Now, is there a way to 'peek' into the source
file and get the range of the source file without
actually opening it?

When I Consolidate data from the source file to the open
file I don't see the source file opening. Perhaps it
does, and I just don't see it? But if the source file
doesn't have to be open to get the data to consolidate
into the open file, is there a way to get the range from
the source file without it being opened?

Thanks again - your help let me get the basic
functionality working - now I'm just trying to 'tweak' it
so all the methods work 'in the background'.

Rick


-----Original Message-----
Rick,

Use code like

Dim WB As Workbook
Set WB = Workbooks("Book1.xls")

This assumes that the file is already open. If you need
to open it, use

Set WB = Workbooks.Open("C:\Full Folder Name\Book1.xls")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com



"Rick Stanford" wrote in message
...
I'm trying to consolidate data from the first worksheet
in one Excel file (the 'source' file - Book1.xls) into
another (the 'opened' file - Book2.xls). To do so, I
need the Range of the data in the source file. I have
a 'LastCell' Function that finds the last row and the
last cell having data on a worksheet and I want to use
it
on the source file.

I've opened an Excel workbook and I have the following
code under a button on a form.

Private Sub CommandButton4_Click()
Dim LastRow As Long, LastCol As Integer
Dim wb As Workbook, ws as Worksheet

Set wb = "C:\Documents and Settings\Dad\My
Documents\Book1.xls
Set ws = wb.Sheets(1)

LastRow = LastCell(ws).Row
LastCol = LastCell(ws).Column

End Sub

I can then use the LastRow,LastCol to set the Range on
the source file.

However, when I run the above code, I get a "Type
Mismatch" error before the Subroutine code fires. If I
change the 'set' statement to:

Set wb = Book1.xls

I get an "Object Required" error when the set statement
fires.

Can someone tell me the proper way to 'look into' an
unopened Excel file to get the information I want? It
may have something to do with the syntax I'm using to
set
the wb variable - I'm not sure.

Thanks,
Rick




.

Maybe just use a large range:

Although I found 16384 rows was the max it would work with for a closed
workbook. I didn't play with it much, so you may be able to overcome this.

Sub Totals()
Const MAXBOOK As Long = 5
Dim i%, SheetArg$()
Dim sPath1 As String
ReDim SheetArg(1 To MAXBOOK)


Dim sPath As String, sFile As String
ThisWorkbook.Worksheets("SumTotal") _
.Cells.ClearContents
sPath = "C:\Timelist\Data\"
i = 0
sPath1 = "C:\TimeList\Data\*.xls"
sFile = Dir(sPath1)
Do While sFile < ""
i = i + 1
SheetArg(i) = "'" & sPath & _
"[" & sFile & "]Sheet1'!R1C2:R16384C3"

sFile = Dir()
Loop
' For i = 1 To MAXBOOK
' Debug.Print i, SheetArg(i)
' Next

ThisWorkbook.Sheets("SumTotal"). _
Range("A1").Consolidate _
Sources:=Array(SheetArg), _
Function:=xlSum, _
TopRow:=False, _
LeftColumn:=False, _
CreateLinks:=False

End Sub

--
Regards,
Tom Ogilvy


Rick Stanford wrote in message
...
Chip -

Thanks, that worked great! I use a CommonDialog to
populate a listbox with files to be consolidated; then I
use the list to set a source array for the consolidate
method. Now, is there a way to 'peek' into the source
file and get the range of the source file without
actually opening it?

When I Consolidate data from the source file to the open
file I don't see the source file opening. Perhaps it
does, and I just don't see it? But if the source file
doesn't have to be open to get the data to consolidate
into the open file, is there a way to get the range from
the source file without it being opened?

Thanks again - your help let me get the basic
functionality working - now I'm just trying to 'tweak' it
so all the methods work 'in the background'.

Rick


-----Original Message-----
Rick,

Use code like

Dim WB As Workbook
Set WB = Workbooks("Book1.xls")

This assumes that the file is already open. If you need
to open it, use

Set WB = Workbooks.Open("C:\Full Folder Name\Book1.xls")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com



"Rick Stanford" wrote in message
...
I'm trying to consolidate data from the first worksheet
in one Excel file (the 'source' file - Book1.xls) into
another (the 'opened' file - Book2.xls). To do so, I
need the Range of the data in the source file. I have
a 'LastCell' Function that finds the last row and the
last cell having data on a worksheet and I want to use
it
on the source file.

I've opened an Excel workbook and I have the following
code under a button on a form.

Private Sub CommandButton4_Click()
Dim LastRow As Long, LastCol As Integer
Dim wb As Workbook, ws as Worksheet

Set wb = "C:\Documents and Settings\Dad\My
Documents\Book1.xls
Set ws = wb.Sheets(1)

LastRow = LastCell(ws).Row
LastCol = LastCell(ws).Column

End Sub

I can then use the LastRow,LastCol to set the Range on
the source file.

However, when I run the above code, I get a "Type
Mismatch" error before the Subroutine code fires. If I
change the 'set' statement to:

Set wb = Book1.xls

I get an "Object Required" error when the set statement
fires.

Can someone tell me the proper way to 'look into' an
unopened Excel file to get the information I want? It
may have something to do with the syntax I'm using to
set
the wb variable - I'm not sure.

Thanks,
Rick




.