Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sql results in excel
The following code shows that if I run the micro command in the first
sheet (sheet1) of my workbook then the results of the query they will be presented in sheet1.(i got an error msg on line"Range("sheet2!A1:C1000").Select") What changes should I make in the code in order to are presented the results in an other sheet e.g. Sheet2. Private Sub OKclass_Click() Dim OraSession As Object Dim OraDatabase As Object Dim EmpDynaset As Object Dim flds() As Object Dim fldcount As Integer Dim userentry As String Dim dtentryFROM As Date Dim dtentryTO As Date Dim strdtfrom As String Dim strdtto As String Dim XL Application Dim Wkb As Workbook Dim ws As Worksheet Set ws = ActiveWorkbook.Sheets("class") 'dtentryTO = Format(dd / mm / yyyy) strdtfrom = InputBox("From Date:") strdtto = InputBox("To Date:") userentry = InputBox("Please give User_Id") Set OraSession = CreateObject("OracleInProcServer.XOraSession") Set OraDatabase = OraSession.OpenDatabase("mydb", "username/pass", 0&) Set EmpDynaset = OraDatabase.CreateDynaset("SELECT ROWNUM,tbl1,tbl2 Data WHERE date = TO_DATE ('" + strdtfrom + "', 'DD/MM/RRRR') AND date < TO_DATE ('" + strdtto + "', 'DD/MM/RRRR') AND user = " + userentry + " ORDER BY date, ROWNUM", 0&) Range("sheet2!A1:C1000").Select Selection.ClearContents 'Declare and create an object for each column. 'This will reduce objects references and speed 'up your application. fldcount = EmpDynaset.Fields.Count ReDim flds(0 To fldcount - 1) For colnum = 0 To fldcount - 1 Set flds(colnum) = EmpDynaset.Fields(colnum) Next 'Insert Column Headings 'For Colnum = 0 To EmpDynaset.Fields.Count - 1 'ActiveSheet.Cells(1, Colnum + 1) = flds(Colnum).Name 'Next 'Display Data For Rownum = 2 To EmpDynaset.RecordCount + 1 For colnum = 0 To fldcount - 1 ActiveSheet.Cells(Rownum, colnum + 1) = flds(colnum).Value Next EmpDynaset.MoveNext Next Range("A2:A2").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Sum Argument results do not equal cell results Excel | Excel Worksheet Functions | |||
Excel results template | Excel Discussion (Misc queries) | |||
Voting results in Excel | Excel Discussion (Misc queries) | |||
When running a SAP report Excel will only display results if Excel isn't already open | Excel Discussion (Misc queries) | |||
sql results in excel | Excel Programming |