PDA

View Full Version : same code does not workon diffrent sheet with same type of data



pramoc
05-07-2007, 01:25 AM
i have one excel sheet report1 with 900 records generated on 1st of month .


then i have report 2 with 1200 records generated on 30 th of month

I run this query on report 2 it runs sucessfully but not on report1
.
str1 = "select * from [sheet2$] where profitctr in (Select pc1 FROM [pc$])"
If objRecordset.State = 1 Then objRecordset.Close

objRecordset.Open str1, objConnection, adOpenStatic, adLockOptimistic, adCmdText

only columns i am concerned with are
column d
column bp


plz help

Simon Lloyd
05-07-2007, 02:00 AM
The portion of code you supplied only refers to a STRING which has been given the variable str1, however i would imagine the rest of your code refers to Report 2 (or the sheet number) therefore without specifying Report1 or the sheet number for it it will not run on Report1.

Regards,
Simon

pramoc
05-07-2007, 02:08 AM
sheet2 has data on both the report.
so there should not be any changes in query right

Simon Lloyd
05-07-2007, 02:33 AM
Code will only work (run query) on the sheet that is specified in it, it does not matter to Excel that data from both reports is on Sheet2......the code will have a sheet specified to work with to gather data and it will also have a sheet specified to send the data to.

The best thing you can do is post all of your code or sample workbook(s) to get the solution you need.

Remember to remove any sensitive or personal data before posting the workbook(s)

Regards,
Simon