Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

searching in multilple worksheets

Status
Not open for further replies.

adhhealth

Programmer
Jul 1, 2004
165
US
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??
 

Try this
Code:
With Sheets("Sheet1")
   .QueryTables.Add( _
      Connection:=sConn, _
      Destination:=.Range("A1"))
End With
Also the .Name property is the NAME of the QueryTable that you are Adding to the Sheet Object.

Skip,

[glasses] [red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [tongue]
 
Skip,
I still am getting

"Application defined or object defined error runtime error
1004"
error at

With Sheets("Sheet1").QueryTables.Add(Connection:=sConn, Destination:=Range("A1"))
End With
line..

Ok, to understand what we are doing here
1. We are first getting the file folder(f)
2. for each file in this folder,(f1)
we got the path(spath) of the first workbook in the folder and the workbook name (sworkbook)
3. then sql query is written to get the information from the file
4. a work sheet is added(so that the data from the query can be stored in it, 1 file- 1 sheet)
till here the code is working fine

the next step:

With Sheets("Sheet1").QueryTables.Add(Connection:=sConn, Destination:=Range("A1")) end with

here we are adding the query table defined by the connection sConn into sheet1
here i got ("Application defined or object defined error runtime error
1004")

we tried
With Activesheet.QueryTables.Add(Connection:=sConn, Destination:=Range("A1")) end with
same error..

so it looks like adding the querytable is the problem here..
Pl. advise.
Thanks




 
NOTE:

BOTH the QueryTable AND the RANGE need to reference the SHEET
Code:
With Activesheet
  .QueryTables.Add(Connection:=sConn, Destination:=[b][red].[/red][/b]Range("A1")) 
end with

Skip,

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


 
it gives me the following error:

Compile error: invalid or unqualified reference

at
.Range("A1"))
 
it gives me the following error:

Compile error: invalid or unqualified reference

at
.Range("A1"))

this is the code:
With ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=.Range("A1"))
end with
 
Why do you insist on CHANGING the code I've given you???
Code:
With ActiveSheet
  .QueryTables.Add(Connection:=sConn, Destination:=.Range("A1"))
end with


Skip,

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


 
I am sorry,I copied the exact code


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

it gives me compile error:syntax error at the 2nd line..

in my code there are a few spaces before .QueryTables.add
but i am not able to show that here in this window..


 
Try it without the parentheses
Code:
With ActiveSheet
    .QueryTables.Add Connection:=sConn, Destination:=.Range("A1")
End With



Skip,

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


 
at this line:

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

i am still getting
"Application defined or object defined error runtime error
1004"
 
With Sheets("Sheet1")
.QueryTables.Add Connection:=sConn, Destination:=.Range("A1")
End With

this also gives same error
 
please post your code that assigns sConn.

Skip,

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


 
this is the code for sConn:


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)"

 
try this...
Code:
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;"
and you are sure that sPath & sWorkbook has valid relevant string values?

Skip,

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


 
there is no compile error now , But no data is fetched..

when i do F8 and put the mouse over sSQL it shows

sSQL="SELECT S.STATUS, S.ACTUAL, S.SCD, S.sch FROM `C:\tst\test3`.`Sheet1... (i am unable to view complete string --pl. note i have changed the location of the file (only to get a view of this string on mouse over)
is there a way i can see what value is stored in sSQL completely?

 
this is the sql code

sSQL = "SELECT S.STATUS, S.ACTUAL, S.SCD, S.sch "
sSQL = sSQL & "FROM `" & sPath & "\" & sWorkbook & "`.`Sheet1$` S
 
in a line after you have made the substitution...
Code:
Sheets("SomeOtherSheet").[A1].Value = sSQL


Skip,

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


 

sSQL returned this value:

SELECT S.STATUS, S.ACTUAL, S.SCD, S.sch FROM
`C:\tst\test3`.`Sheet1$` S

it is not fetching any value from the file..
is the string value (format)returned correct??i am sure that the path and the file name exists..
 

you previously has Heading Fields of
[tt]
STATUS, ACTUAL, SCHEDULED, sch
[/tt]
and these are in Sheet1?.

and there is no Where clause?

You ought to be able to put your cursor in cell A1, where the query table should be returned to, and Data/Get External data/Edit Query -- and see what happens.


Skip,

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


 
as i did not know about:
Sheets("Sheet2").[A1].Value = sSQL

to be able to view the string value on mouse over sSQL while debugging, i changed the path and removed the where condition, anyway now i have changed back to the original:

the value in Sheet2 A1 is

SELECT S.STATUS, S.ACTUAL, S.SCHEDULED, S.sch FROM `C:\PRODUCTION HISTORY\ORDER TRACKING\test3`.`Sheet1$` S WHERE (S.ACTUAL>S.sch)

we have written the above sql code in VB code right,

Data/Get External data/Edit Query is disabled now.. so i created new database query to see if it returns the results(edited in microsoft query) and the data is fetched fine based on the condition..


 
Did you create this querytable WITHOUT a DSN?

If so, you can then macro record EDITING the query (with no net change).

Post that code, please.

Skip,

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


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top