searching in multilple worksheets

[adhhealth]

There are many (100 or more) excel workbooks in a folder in the same format- ex: scheduled-date actual-date description and so on.. each file will have different values under these fields.

From all these excel files i have to pickup the row where the 1. datediff(scheduled date ,actual date) >2 or
2. datediff(scheduled date ,today's date)>2
and create a new "delays" summary excel file.

is it possible to do this??
or actually we are planning to completely revamp this system,to accomplish this functionality from entering data in a way that these summary reports can be generated(using office 2000), can someone suggest any other
better way that is possible??

 

[adhhealth]

currently i have the following vb code in a macro:
and nothing else(i dont think i have created anything explicit like a query table,because i dont know what it is)

Sub Macro1()
Dim fs, f, f1, fc, s
Dim sConn, sSQL, sPath, sWorkbook, p1
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("C:\PRODUCTION HISTORY\ORDER TRACKING")
Set fc = f.Files
For Each f1 In fc

p1 = InStrRev(f1.Path, "\")
sPath = Left(f1.Path, p1 - 1)
sWorkbook = Split(Right(f1.Path, Len(f1.Path) - p1), ".")(0)

sConn = "ODBC;"
sConn = sConn & "DSN=Excel Files;"
sConn = sConn & "DBQ=" & sPath & "\" & sWorkbook & ".xls;"
sConn = sConn & "DefaultDir=" & sPath & ";"
sConn = sConn & "DriverId=790;"
sConn = sConn & "MaxBufferSize=2048;"
sConn = sConn & "PageTimeout=5;"

sSQL = "SELECT S.STATUS, S.ACTUAL, S.SCHEDULED, S.sch "
sSQL = sSQL & "FROM `" & sPath & "\" & sWorkbook & "`.`Sheet1$` S "
sSQL = sSQL & "WHERE (S.ACTUAL>S.sch)"
Sheets("Sheet2").[A1].Value = sSQL

'add a worksheet
Worksheets.Add
With ActiveSheet
.QueryTables.Add Connection:=sConn, Destination:=.Range("A1")
End With
Next
End Sub

 

[adhhealth]

i have recorded the macro to run the saved query:but the problem here is it gets the data from only 1 file (test3.xls)and i want the data from all the files in the folder. i will post the code of the query too.

code for the macro:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 3/15/2005 by adh
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\adh\Application Data\Microsoft\Queries\Queryforproduction.dqy" _
, Destination:=Range("A1"))
.Name = "Queryforproduction_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub




code for the query "Queryforproduction_1":

XLODBC
1
DSN=Excel Files;DBQ=C:\PRODUCTION HISTORY\ORDER TRACKING\test3.xls;DefaultDir=C:\PRODUCTION HISTORY\ORDER TRACKING;DriverId=790;MaxBufferSize=2048;PageTimeout=5;
SELECT `Sheet1$`.STATUS, `Sheet1$`.ACTUAL, `Sheet1$`.SCHEDULED, `Sheet1$`.sch FROM `C:\PRODUCTION HISTORY\ORDER TRACKING\test3`.`Sheet1$` `Sheet1$` WHERE (`Sheet1$`.ACTUAL>`Sheet1$`.sch)
Sheet1$
11
STATUS ACTUAL SCHEDULED sch

 

[SkipVought]

i dont think i have created anything explicit like a query table,because i dont know what it is

But you DO create a QueryTable...

    With [b]ActiveSheet.QueryTables.Add[/b](Connection:= _
        "FINDER;C:\Documents and Settings\adh\Application Data\Microsoft\Queries\Queryforproduction.dqy" _
        , Destination:=Range("A1"))
....
End With

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[SkipVought]

and assuming that sConn is correct and the sSQL is correct

Sub Macro1()
    Dim fs, f, f1, fc, s
    Dim sConn, sSQL, sPath, sWorkbook, p1
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder("C:\PRODUCTION HISTORY\ORDER TRACKING")
    Set fc = f.Files
    For Each f1 In fc
        
         p1 = InStrRev(f1.Path, "\")
        sPath = Left(f1.Path, p1 - 1)
        sWorkbook = Split(Right(f1.Path, Len(f1.Path) - p1), ".")(0)
        
        sConn = "ODBC;"
        sConn = sConn & "DSN=Excel Files;"
        sConn = sConn & "DBQ=" & sPath & "\" & sWorkbook & ".xls;"
        sConn = sConn & "DefaultDir=" & sPath & ";"
        sConn = sConn & "DriverId=790;"
        sConn = sConn & "MaxBufferSize=2048;"
        sConn = sConn & "PageTimeout=5;"

       sSQL = "SELECT S.STATUS, S.ACTUAL, S.SCHEDULED, S.sch  "
        sSQL = sSQL & "FROM `" & sPath & "\" & sWorkbook & "`.`Sheet1$` S "
        sSQL = sSQL & "WHERE (S.ACTUAL>S.sch)"
        Sheets("Sheet2").[A1].Value = sSQL
     
    'add a worksheet
       Worksheets.Add
    'add querytable
       With ActiveSheet
        .QueryTables.Add Connection:=sConn, Destination:=.Range("A1")
        .Name = "Queryforproduction_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
       End With
    Next
End Sub

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[SkipVought]

and...

maybe you ALSO want to add a criteria in the Where Clause, specific to each sheet.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[adhhealth]

1. I have the same query i want to run for all the sheets.
2. I am sure that sConn,sSQL is correct.

(value in sSQL is SELECT S.STATUS, S.ACTUAL, S.SCHEDULED, S.sch FROM `C:\PRODUCTION HISTORY\ORDER TRACKING\test3`.`Sheet1$` S WHERE (S.ACTUAL>S.sch)--this looks ok to me, unless you feel something is not right)

2. When i run the code you have given, it gives an error saying:
Object doesnot support this property or method.
at line .FieldNames = True

then i removed that line just to see, it gave same error at the next line and so on..
so finally i left the last few lines of the code as:

  With ActiveSheet
        .QueryTables.Add Connection:=sConn, Destination:=.Range("A1")
        .Name = "Queryforproduction_1"
         End With

first time i ran the code it created a sheet called

Queryforproduction_1
and also sheet3

now it gives an error:
runtime error:1004: cannot rename a sheet to the same name as another sheet,a referenced object library or a workbook referenced by visual basic.

 

[adhhealth]

actually instead of creating a new worksheet for data from each file, can we get the data satisfying the query from all the files into just 1 worksheet?

 

[SkipVought]

Absolutely! I am in favor of haveing the fewest number of objects as possible.

In that case, you do NOT want to ADD a querytable. Instead, use cade like this...

    For Each f1 In fc
        
         p1 = InStrRev(f1.Path, "\")
        sPath = Left(f1.Path, p1 - 1)
        sWorkbook = Split(Right(f1.Path, Len(f1.Path) - p1), ".")(0)
        
        sConn = "ODBC;"
        sConn = sConn & "DSN=Excel Files;"
        sConn = sConn & "DBQ=" & sPath & "\" & sWorkbook & ".xls;"
        sConn = sConn & "DefaultDir=" & sPath & ";"
        sConn = sConn & "DriverId=790;"
        sConn = sConn & "MaxBufferSize=2048;"
        sConn = sConn & "PageTimeout=5;"

       sSQL = "SELECT S.STATUS, S.ACTUAL, S.SCHEDULED, S.sch  "
        sSQL = sSQL & "FROM `" & sPath & "\" & sWorkbook & "`.`Sheet1$` S "
        sSQL = sSQL & "WHERE (S.ACTUAL>S.sch)"
 
       Sheets("Sheet2").[A1].Value = sSQL
     
    'execute querytable
       With ActiveSheet.QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
       End With
    Next

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[adhhealth]

it says subscript out of range at:

With ActiveSheet.QueryTables(1)

i have a feeling that the data is not getting populated ..
when i look in the odbc data sources, i see the excel driver in the user dsn. do u think i need to create odbc file or system dsn?

 

[SkipVought]

Then you don't have a querytable in the activesheet.

ADD ONE! Just do it manually!

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[adhhealth]

When i added the data/external data..
i am getting the data only from the last file in the folder

i am guessing it is over writing the data,
1st say the data from 1st file is got and it is put in "A1"

then from 2nd file data is put in "A1" again.. and so on..

 

[SkipVought]

Tell me what you want to happen, workbook by workbook. Be specific, for instance, if workbook 1 has 5 rows & workbook 2 has 10 rows.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[adhhealth]

when i run the macro,
i am getting the data only from the last file in the folder..

in the folder i have say 10 workbooks

each workbook will always have 20 rows, but the query might return only few lines each different number of lines from each workbook, say
from 1st file there are only 2 rows which satisfy
ACTUAL>sch condition and 4th file has 6 rows which satisfy
ACTUAL>sch condition..

it would be better if the data is got in the following way:

for 1st file say 2 rows are fetched, first 2 lines will be from 1st file, here..
then there can be a line blank and the data from the
2nd file is got in the 4th row..(say 5 rows are fetched from 2nd file) then rows from 4 to 9 will be from 2nd file and so on..

 

[SkipVought]

Here's what you do.

1. The query always returns to the "Query" sheet

2. After each query you Copy the data area and paste it into the "Results" sheet

for....
  'query here

  'now copy 'n' paste
  with [Query_From_Excel]
     range(cells(2,1), cells(.rows.count, .columns.count)).Copy _
   sheets("Results").cells(sheets("Results").[A1].CurrentRegion.Rows.Count+1, 1)
  end with
Next

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}