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

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

.

sql results in excel
 

the select works fine .... anything about the subject??