Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List Box with lookup facility | Excel Worksheet Functions | |||
Is there a 'floating key/ legend' facility? | Excel Discussion (Misc queries) | |||
Spelling Facility | New Users to Excel | |||
how do i use LOTUS combine facility in Excel | Excel Discussion (Misc queries) | |||
Set up an Ordering facility for a Buying Dept | Excel Programming |