Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sql results in excel
Sub Refresh()
'Declare variables 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 'dtentryTO = Format(dd / mm / yyyy) strdtfrom = InputBox("From Date:") strdtto = InputBox("To Date:") userentry = InputBox("Please give User") Set OraSession = CreateObject ("OracleInProcServer.XOraSession") Set OraDatabase = OraSession.OpenDatabase ("mydb", "username/pass", 0&) Set EmpDynaset = OraDatabase.CreateDynaset("SELECT ROWNUM, tbl1, tbl2 FROM 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&) 'Set objSht = objWkb.Worksheets(class) 'Worksheets(class). Range("A2:C2000").Select 'Range("A2:C2000").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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sql results in excel
Apart from the subject being SQL and the content being
Oracle, what's the question here? In SQL a char variable needs quotes so maybe AND user = " + userentry + " ORDER should nbe AND user = '" + userentry + "' ORDER Patrick Molloy Microsoft Excel MVP -----Original Message----- Sub Refresh() 'Declare variables 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 'dtentryTO = Format(dd / mm / yyyy) strdtfrom = InputBox("From Date:") strdtto = InputBox("To Date:") userentry = InputBox("Please give User") Set OraSession = CreateObject ("OracleInProcServer.XOraSession") Set OraDatabase = OraSession.OpenDatabase ("mydb", "username/pass", 0&) Set EmpDynaset = OraDatabase.CreateDynaset("SELECT ROWNUM, tbl1, tbl2 FROM 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&) 'Set objSht = objWkb.Worksheets(class) 'Worksheets(class). Range("A2:C2000").Select 'Range("A2:C2000").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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sql results in excel
the select works fine .... anything about the subject??
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is this in Excel? © This is the results when I enter (c). | Excel Discussion (Misc queries) | |||
Conditional Sum Argument results do not equal cell results Excel | Excel Worksheet Functions | |||
Excel TREND results | Charts and Charting in Excel | |||
When running a SAP report Excel will only display results if Excel isn't already open | Excel Discussion (Misc queries) | |||
how do i set results to appear in status bar of excel | Excel Discussion (Misc queries) |