search facility
 

Hi Everyone.
Ive got some code below from some helpful chap on this
forum which i use to search through a whole range of
files in a folder, the problem is that it takes rather a
long time with it opening everysingle file.

Is there any way for it to do the same thing but with out
opening each file?

Simple instructions would be preferable, even better if
it can be a quick ammendment to the code below.

Thankyou for your help.


Set basebook = ThisWorkbook
rnum = 1
Do While FNames < ""
If Left(FNames, 1) = "0" Then
Set mybook = Workbooks.Open(FNames)
Range("A65000").End(xlUp).Select
n = ActiveCell.Row
Set sourceRange = mybook.Worksheets(1).Range
_ ("a1", "p" & n)
a = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells
_ (rnum, 1)
sourceRange.Copy destrange
rnum = rnum + a
mybook.Close False
End If
FNames = Dir()
Loop

search facility
 

I think that as your examine the contents dynamically, you won't improve it
much. If the cell you were retrieving was fixed, you could query each
workbook with ADO, but it would need to retrieve everything and then work
through.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"darren" wrote in message
...
Hi Everyone.
Ive got some code below from some helpful chap on this
forum which i use to search through a whole range of
files in a folder, the problem is that it takes rather a
long time with it opening everysingle file.

Is there any way for it to do the same thing but with out
opening each file?

Simple instructions would be preferable, even better if
it can be a quick ammendment to the code below.

Thankyou for your help.


Set basebook = ThisWorkbook
rnum = 1
Do While FNames < ""
If Left(FNames, 1) = "0" Then
Set mybook = Workbooks.Open(FNames)
Range("A65000").End(xlUp).Select
n = ActiveCell.Row
Set sourceRange = mybook.Worksheets(1).Range
_ ("a1", "p" & n)
a = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells
_ (rnum, 1)
sourceRange.Copy destrange
rnum = rnum + a
mybook.Close False
End If
FNames = Dir()
Loop

search facility
 

"Bob Phillips" wrote ...

If the cell you were retrieving was fixed, you could query each
workbook with ADO, but it would need to retrieve everything and then work
through.

Bob,
Rather than the cells being the problem, I see the worksheet name
being the problem. If mybook.Worksheets(1) always had the same name,
say Sheet1, the OP could use the following query:

SELECT * FROM [Sheet1$A1:P65536];

Or rather

INSERT INTO
[MyDestRange]
SELECT * FROM
[Excel 8.0;Database=C:\MySearchFolder\MyBook1.xls;].[Sheet1$A1:P65536];

for each source workbook. And if there weren't too many workbooks it
could be done in one hit:

INSERT INTO
[MyDestRange]
SELECT * FROM
[Excel 8.0;Database=C:\MySearchFolder\MyBook1.xls;].[Sheet1$A1:P65536]
UNION ALL
SELECT * FROM
[Excel 8.0;Database=C:\MySearchFolder\MyBook2.xls;].[Sheet1$A1:P65536]
UNION ALL
SELECT * FROM
[Excel 8.0;Database=C:\MySearchFolder\MyBook3.xls;].[Sheet1$A1:P65536]
ORDER BY
1 ASC, 2 DESC;

ADO would discard any blank rows below the used range i.e. wouldn't
get all 65536 rows unless they had been populated.

Without the consistent sheet names, I know of no reliable way of
getting the name of the first worksheet using ADO. Both ADOX's Tables
collection and ADODB OpenSchema (adSchemaTables) method's recordset
appear in order of table name and, not surprisingly, have no 'table
ordinal position' property.

Jamie.

--