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??

 

[SkipVought]

hi,

For a start...

Take a look at Data/Get External Data/New Database Query/Excel Files -- one of your 100+ workbooks.......

Then turn on your macro recorder and just record editing this querytable you just entered. This code would be the basis your querying all 100+ workbooks.

Post back & we can go from there.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

will get back to you soon
thanks

 

[adhhealth]

Sub datamacro()
'
' datamacro Macro
' Macro recorded 3/8/2005 by adh
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\adh\Application Data\Microsoft\Queries\Query from Excel Files3.dqy" _
, Destination:=Range("A1"))
.Name = "Query from Excel Files3"
.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

this is the code from the macro -when i am querying 1 file, how can i query all the other files in the folder and get the input into this file??(the query is the same for all files)
thanks,

 

[SkipVought]

Look at the FileSystem Object

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

Thank you Skip, i have a question-
i have recorded a macro for the above action of querying a file,
now for querying all the files(using the file system object-i am looking into this now..)- where do i enter the code ??in the same macro (datamacro()) itself??
if i am writing the code in the same macro, i want the macro to run on file open itself..where do i specify that this macro should be run on file open??

this is the first time i am working with excel or VBA programming, pl. advise me what site would be better to start
understanding the concepts??

 

[SkipVought]

set the filesystems object

set the folder filesystem object

for each file in the filesystem.folder.files
worksheets.add
with activesheet.querytables.add connection:=file.name
.....
end with
next

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

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

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

End With
Next
End Sub


When I run this macro nothing is happening..i want to run the same query for all the files in the folder and get the result into this file..

the query "Query from Excel Files3.dqy" is:

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

do you think I have to combine the query in the above code itself rather than using it as a macro separately??because here we can see that the query has the specific path to the file test2.xls..

Thanks for patiently guiding me through this..

 

[adhhealth]

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

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

End With
Next
End Sub


When I run this macro nothing is happening..i want to run the same query for all the files in the folder and get the result into this file..

the query "Query from Excel Files3.dqy" is:

XLODBC
1
DSN=Excel Files;DBQ=C:\PRODUCTION HISTORY\ORDER TRACKING\test2.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\test2`.`Sheet1$` `Sheet1$` WHERE (`Sheet1$`.ACTUAL>`Sheet1$`.sch)
Sheet1$
11
STATUS ACTUAL SCHEDULED sch

do you think I have to combine the query in the above code itself rather than using it as a macro separately??because here we can see that the query has the specific path to the file test2.xls..

Thanks for patiently guiding me through this..

 

[adhhealth]

this is the code for the macronothing is happening when i run the macro).. i have given below the contents of the query file too.. do you think I should combine the query into this macro vb code (As we see that in the query it is looking for a specific file)
I am not sure how this can be done...

thank you for patiently guiding me through this issue..


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

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

End With
Next
End Sub



and these are the contents of "Query from Excel Files3.dqy"

XLODBC
1
DSN=Excel Files;DBQ=C:\PRODUCTION HISTORY\ORDER TRACKING\test2.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\test2`.`Sheet1$` `Sheet1$` WHERE (`Sheet1$`.ACTUAL>`Sheet1$`.sch)
Sheet1$
11
STATUS ACTUAL SCHEDULED sch

 

[adhhealth]

there was some error encountered when i was posting this message..so the same message posted multiple times
sorry about that!!

 

[SkipVought]

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(fl.Name, "\")
        sPath = Left(fl.Name, p1 - 1)
        sWorkbook = Split(Right(fl.Name, Len(fl.Name) - p1), ".")(0)
       
        sConn = "XLODBC"
        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)"
    
    'add a worksheet
        Worksheets.Add
        
         With ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("A1"))
            .CommandText = sSQL
            .Name = "Query from Excel Files3"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
                
        End With
    Next
End Sub

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

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(fl.Name, "\")

at this line i am getting a runtime error: 424 object required.
Do we have to declare "f1"?

 

[SkipVought]

oops...

you have it Dimed as f1 (character one) and I used fl (character L)

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[SkipVought]

correction

        sConn = "XLODBC;"
        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;"

each parameter is terminated with semicolon.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

Skip,
thank you for the replies.

when i am debugging the macro, i see that the fs=empty
f=empty (here we have given the path of the folder..i am not sure if this should show an empty value) fc=empty ,f1 shows the path of this file"C:\PRODUCTION HISTORY\ORDER TRACKING\TEST.xls"
p1 =empty
sPath=empty
after this on F8 i get this message:
" invalid procedure call or argument-runtime error 5"
probably because p1 is empty and we are doing -1??
what am i doing wrong here?

 

[SkipVought]

Use the Path property of the f1 object

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

however, that does not answer your question.

I run the code and it works. Are you positive that the folderspec is correct?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

Yes Skip, I am sure that the folderspec is correct(coz it gave me a compile error saying that the folder path doesnot exist when it was entered wrong)
any way

p1 = InStrRev(f1.Path, "\") worked for me,
but still at

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

line I am getting
"Application defined or object defined error runtime error 1004"

also we donot need
.Name = "Query from Excel Files3" right, because we already defined the query in the vbcode.

Thanks again for the patience.. I am learning to debug now..this was a good learning experience for me..never done this before..

 

[SkipVought]

What sheet is active when this code runs?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

I was not in at work yesterday, so I could not get back to you Sorry!
File which is active when the code runs is:

C:\qrfq\testqrfq.xls
and the active sheet is Sheet1