I have designed a query that I want ran from within Excel but I want the user
to provide some data, such as a start date and end date, and then I want this
data inserted into my query. Does anyone know the best way to do this?

Normally, I have just been asking them for this criteria and then running
the queries myself, but now I want to develop something that my users can
enter their values and press a button and then have it run the query and pull
the data into a spreadsheet. I am new to this and I recorded a macro while I
did the manual process.

Any help will be greatly appreciated.

Thanks

Dwaine Horton

As always, copy/paste your macro here for comments.

--
Don Guillett
SalesAid Software

"Dwaine Horton" wrote in message
...
I have designed a query that I want ran from within Excel but I want the
user
to provide some data, such as a start date and end date, and then I want
this
data inserted into my query. Does anyone know the best way to do this?

Normally, I have just been asking them for this criteria and then running
the queries myself, but now I want to develop something that my users can
enter their values and press a button and then have it run the query and
pull
the data into a spreadsheet. I am new to this and I recorded a macro
while I
did the manual process.

Any help will be greatly appreciated.

Thanks

Dwaine Horton

U¿ytkownik "Dwaine Horton" napisa³
w wiadomo¶ci ...
I have designed a query that I want ran from within Excel but I want the
user
to provide some data, such as a start date and end date, and then I want
this
data inserted into my query. Does anyone know the best way to do this?

Normally, I have just been asking them for this criteria and then running
the queries myself, but now I want to develop something that my users can
enter their values and press a button and then have it run the query and
pull
the data into a spreadsheet. I am new to this and I recorded a macro
while I
did the manual process.

Any help will be greatly appreciated.

Thanks

Dwaine Horton



you can use
x=inputbox("please enter data for...")
and the use x as variable
macgru

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX;DBA=W;APA=T; EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F; FRL=F;BAM=" _
), Array("IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC =10;TLO=0;")),
Destination _
:=Range("A1"))
.CommandText = Array( _
"SELECT oe1.loc, oe1.NAME, sum(inv.ttl_invc_amt), " & Chr(13) & "" &
Chr(10) & "sr.SR_AREA, oe.INTEGRATION_ID" & Chr(13) & "" & Chr(10) & "FROM
siebel.s_invoice inv" & Chr(13) & "" & Chr(10) & " INNER JOIN
siebel.S_SRV_REQ sr" & Chr(13) & "" & Chr(10) & " ON " & Chr(13) & "" &
Chr(10) & " inv.SR_ID = sr.row_id" & Chr(13) & "" & Chr(10) & " INNER
JOIN siebel.s_org" _
, _
"_ext oe" & Chr(13) & "" & Chr(10) & " ON" & Chr(13) & "" & Chr(10)
& " sr.X_BILL_TO_ID_YORK = oe.row_id" & Chr(13) & "" & Chr(10) & "
INNER JOIN siebel.s_org_ext oe1" & Chr(13) & "" & Chr(10) & " ON" & Chr(13)
& "" & Chr(10) & " sr.CST_OU_ID = oe1.row_id" & Chr(13) & "" & Chr(10)
& " INNER JOIN SIEBEL.S_ADDR_ORG adr" & Chr(13) & "" & Chr(10) & " ON" &
Chr(13) & "" & Chr(10) & " oe1.PR_ADDR_ID = adr.row_id" & Chr(13) & ""
& Chr(10) & " INN" _
, _
"ER JOIN SIEBEL.S_CONTACT c" & Chr(13) & "" & Chr(10) & " ON " &
Chr(13) & "" & Chr(10) & " sr.CST_CON_ID = c.ROW_ID" & Chr(13) & "" &
Chr(10) & "WHERE oe.Integration_id = '100-035140740'" & Chr(13) & "" &
Chr(10) & "and inv.invc_dt = '01-JAN-05'" & Chr(13) & "" & Chr(10) & "and
inv.invc_dt <= '01-MAY-05'" & Chr(13) & "" & Chr(10) & "and
inv.X_LAWSON_INVOICE_NUMBER" _
, _
"_YORK is not null" & Chr(13) & "" & Chr(10) & "GROUP BY" & Chr(13)
& "" & Chr(10) & "oe1.loc, oe1.NAME, sr.SR_AREA, oe.INTEGRATION_ID" _
)
.Name = "Query from SIEBPROD"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

I would like to have the invcdate passed as parameters to the query above.

"Don Guillett" wrote:

As always, copy/paste your macro here for comments.

--
Don Guillett
SalesAid Software

"Dwaine Horton" wrote in message
...
I have designed a query that I want ran from within Excel but I want the
user
to provide some data, such as a start date and end date, and then I want
this
data inserted into my query. Does anyone know the best way to do this?

Normally, I have just been asking them for this criteria and then running
the queries myself, but now I want to develop something that my users can
enter their values and press a button and then have it run the query and
pull
the data into a spreadsheet. I am new to this and I recorded a macro
while I
did the manual process.

Any help will be greatly appreciated.

Thanks

Dwaine Horton

that'll teach you ;)


sql example:
"SELECT * FROM MyTable WHERE StartDate'1-Jun-04' AND EndDate <
'15-Feb-05';"


sqltext = "SELECT * FROM MyTable WHERE StartDate'%date1%' AND EndDate <
'%date2%';"
sql = replace(sqltext, "%date1%", format$(mystartdate,"dd-mmm-yy")
sql = replace(sql, "%date2%", format$(myenddate,"dd-mmm-yy")

set the two variables mystartdate and myenddate to whatever and the code
will replace the two 'tokens' accordingly


"Dwaine Horton" wrote in message
...
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX;DBA=W;APA=T; EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F; FRL=F;BAM="
_
), Array("IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC =10;TLO=0;")),
Destination _
:=Range("A1"))
.CommandText = Array( _
"SELECT oe1.loc, oe1.NAME, sum(inv.ttl_invc_amt), " & Chr(13) & ""
&
Chr(10) & "sr.SR_AREA, oe.INTEGRATION_ID" & Chr(13) & "" & Chr(10) & "FROM
siebel.s_invoice inv" & Chr(13) & "" & Chr(10) & " INNER JOIN
siebel.S_SRV_REQ sr" & Chr(13) & "" & Chr(10) & " ON " & Chr(13) & "" &
Chr(10) & " inv.SR_ID = sr.row_id" & Chr(13) & "" & Chr(10) & " INNER
JOIN siebel.s_org" _
, _
"_ext oe" & Chr(13) & "" & Chr(10) & " ON" & Chr(13) & "" &
Chr(10)
& " sr.X_BILL_TO_ID_YORK = oe.row_id" & Chr(13) & "" & Chr(10) & "
INNER JOIN siebel.s_org_ext oe1" & Chr(13) & "" & Chr(10) & " ON" &
Chr(13)
& "" & Chr(10) & " sr.CST_OU_ID = oe1.row_id" & Chr(13) & "" &
Chr(10)
& " INNER JOIN SIEBEL.S_ADDR_ORG adr" & Chr(13) & "" & Chr(10) & " ON" &
Chr(13) & "" & Chr(10) & " oe1.PR_ADDR_ID = adr.row_id" & Chr(13) &
""
& Chr(10) & " INN" _
, _
"ER JOIN SIEBEL.S_CONTACT c" & Chr(13) & "" & Chr(10) & " ON " &
Chr(13) & "" & Chr(10) & " sr.CST_CON_ID = c.ROW_ID" & Chr(13) & ""
&
Chr(10) & "WHERE oe.Integration_id = '100-035140740'" & Chr(13) & "" &
Chr(10) & "and inv.invc_dt = '01-JAN-05'" & Chr(13) & "" & Chr(10) & "and
inv.invc_dt <= '01-MAY-05'" & Chr(13) & "" & Chr(10) & "and
inv.X_LAWSON_INVOICE_NUMBER" _
, _
"_YORK is not null" & Chr(13) & "" & Chr(10) & "GROUP BY" & Chr(13)
& "" & Chr(10) & "oe1.loc, oe1.NAME, sr.SR_AREA, oe.INTEGRATION_ID" _
)
.Name = "Query from SIEBPROD"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

I would like to have the invcdate passed as parameters to the query above.

"Don Guillett" wrote:

As always, copy/paste your macro here for comments.

--
Don Guillett
SalesAid Software

"Dwaine Horton" wrote in message
...
I have designed a query that I want ran from within Excel but I want
the
user
to provide some data, such as a start date and end date, and then I
want
this
data inserted into my query. Does anyone know the best way to do this?

Normally, I have just been asking them for this criteria and then
running
the queries myself, but now I want to develop something that my users
can
enter their values and press a button and then have it run the query
and
pull
the data into a spreadsheet. I am new to this and I recorded a macro
while I
did the manual process.

Any help will be greatly appreciated.

Thanks

Dwaine Horton

Possibly you just want to set it up as a parameter query.

Also, you can assign the parameter to get its value from a cell.

See Nick Hodges site for an example:

http://nickhodge.co.uk/gui/datamenu/...taexamples.htm

--
Regards,
Tom Ogilvy


"Dwaine Horton" wrote in message
...
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _


"ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX;DBA=W;APA=T; EXC=F;FEN=T;QTO=T;FRC=10;F
DL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=" _
),
Array("IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC =10;TLO=0;")),
Destination _
:=Range("A1"))
.CommandText = Array( _
"SELECT oe1.loc, oe1.NAME, sum(inv.ttl_invc_amt), " & Chr(13) & ""
&
Chr(10) & "sr.SR_AREA, oe.INTEGRATION_ID" & Chr(13) & "" & Chr(10) & "FROM
siebel.s_invoice inv" & Chr(13) & "" & Chr(10) & " INNER JOIN
siebel.S_SRV_REQ sr" & Chr(13) & "" & Chr(10) & " ON " & Chr(13) & "" &
Chr(10) & " inv.SR_ID = sr.row_id" & Chr(13) & "" & Chr(10) & " INNER
JOIN siebel.s_org" _
, _
"_ext oe" & Chr(13) & "" & Chr(10) & " ON" & Chr(13) & "" &
Chr(10)
& " sr.X_BILL_TO_ID_YORK = oe.row_id" & Chr(13) & "" & Chr(10) & "
INNER JOIN siebel.s_org_ext oe1" & Chr(13) & "" & Chr(10) & " ON" &
Chr(13)
& "" & Chr(10) & " sr.CST_OU_ID = oe1.row_id" & Chr(13) & "" &
Chr(10)
& " INNER JOIN SIEBEL.S_ADDR_ORG adr" & Chr(13) & "" & Chr(10) & " ON" &
Chr(13) & "" & Chr(10) & " oe1.PR_ADDR_ID = adr.row_id" & Chr(13) &
""
& Chr(10) & " INN" _
, _
"ER JOIN SIEBEL.S_CONTACT c" & Chr(13) & "" & Chr(10) & " ON " &
Chr(13) & "" & Chr(10) & " sr.CST_CON_ID = c.ROW_ID" & Chr(13) & ""
&
Chr(10) & "WHERE oe.Integration_id = '100-035140740'" & Chr(13) & "" &
Chr(10) & "and inv.invc_dt = '01-JAN-05'" & Chr(13) & "" & Chr(10) & "and
inv.invc_dt <= '01-MAY-05'" & Chr(13) & "" & Chr(10) & "and
inv.X_LAWSON_INVOICE_NUMBER" _
, _
"_YORK is not null" & Chr(13) & "" & Chr(10) & "GROUP BY" &
Chr(13)
& "" & Chr(10) & "oe1.loc, oe1.NAME, sr.SR_AREA, oe.INTEGRATION_ID" _
)
.Name = "Query from SIEBPROD"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

I would like to have the invcdate passed as parameters to the query above.

"Don Guillett" wrote:

As always, copy/paste your macro here for comments.

--
Don Guillett
SalesAid Software

"Dwaine Horton" wrote in
message
...
I have designed a query that I want ran from within Excel but I want
the
user
to provide some data, such as a start date and end date, and then I
want
this
data inserted into my query. Does anyone know the best way to do
this?

Normally, I have just been asking them for this criteria and then
running
the queries myself, but now I want to develop something that my users
can
enter their values and press a button and then have it run the query
and
pull
the data into a spreadsheet. I am new to this and I recorded a macro
while I
did the manual process.

Any help will be greatly appreciated.

Thanks

Dwaine Horton

Ok, I have tried the following and get no results:

Dim sqltext As String

Function runquery()
sqltext = "SELECT WoDate FROM Natop.dbo.workmast WHERE (WoDate Between
'%start%' And '%end%')"
Sql = Replace(sqltext, "%start%", "01/01/2005")
Sql = Replace(Sql, "%end%", "04/25/2005")
sqltext = Sql
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Natop_Sql;UID=YI016695;APP=Microsoft ®
Query;WSID=4DNR331;DATABASE=Natop;Trusted_Connecti on=Yes" _
, Destination:=Range("A1"), Sql:=sqltext)
End With


End Function

"Patrick Molloy" wrote:

that'll teach you ;)


sql example:
"SELECT * FROM MyTable WHERE StartDate'1-Jun-04' AND EndDate <
'15-Feb-05';"


sqltext = "SELECT * FROM MyTable WHERE StartDate'%date1%' AND EndDate <
'%date2%';"
sql = replace(sqltext, "%date1%", format$(mystartdate,"dd-mmm-yy")
sql = replace(sql, "%date2%", format$(myenddate,"dd-mmm-yy")

set the two variables mystartdate and myenddate to whatever and the code
will replace the two 'tokens' accordingly


"Dwaine Horton" wrote in message
...
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX;DBA=W;APA=T; EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F; FRL=F;BAM="
_
), Array("IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC =10;TLO=0;")),
Destination _
:=Range("A1"))
.CommandText = Array( _
"SELECT oe1.loc, oe1.NAME, sum(inv.ttl_invc_amt), " & Chr(13) & ""
&
Chr(10) & "sr.SR_AREA, oe.INTEGRATION_ID" & Chr(13) & "" & Chr(10) & "FROM
siebel.s_invoice inv" & Chr(13) & "" & Chr(10) & " INNER JOIN
siebel.S_SRV_REQ sr" & Chr(13) & "" & Chr(10) & " ON " & Chr(13) & "" &
Chr(10) & " inv.SR_ID = sr.row_id" & Chr(13) & "" & Chr(10) & " INNER
JOIN siebel.s_org" _
, _
"_ext oe" & Chr(13) & "" & Chr(10) & " ON" & Chr(13) & "" &
Chr(10)
& " sr.X_BILL_TO_ID_YORK = oe.row_id" & Chr(13) & "" & Chr(10) & "
INNER JOIN siebel.s_org_ext oe1" & Chr(13) & "" & Chr(10) & " ON" &
Chr(13)
& "" & Chr(10) & " sr.CST_OU_ID = oe1.row_id" & Chr(13) & "" &
Chr(10)
& " INNER JOIN SIEBEL.S_ADDR_ORG adr" & Chr(13) & "" & Chr(10) & " ON" &
Chr(13) & "" & Chr(10) & " oe1.PR_ADDR_ID = adr.row_id" & Chr(13) &
""
& Chr(10) & " INN" _
, _
"ER JOIN SIEBEL.S_CONTACT c" & Chr(13) & "" & Chr(10) & " ON " &
Chr(13) & "" & Chr(10) & " sr.CST_CON_ID = c.ROW_ID" & Chr(13) & ""
&
Chr(10) & "WHERE oe.Integration_id = '100-035140740'" & Chr(13) & "" &
Chr(10) & "and inv.invc_dt = '01-JAN-05'" & Chr(13) & "" & Chr(10) & "and
inv.invc_dt <= '01-MAY-05'" & Chr(13) & "" & Chr(10) & "and
inv.X_LAWSON_INVOICE_NUMBER" _
, _
"_YORK is not null" & Chr(13) & "" & Chr(10) & "GROUP BY" & Chr(13)
& "" & Chr(10) & "oe1.loc, oe1.NAME, sr.SR_AREA, oe.INTEGRATION_ID" _
)
.Name = "Query from SIEBPROD"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

I would like to have the invcdate passed as parameters to the query above.

"Don Guillett" wrote:

As always, copy/paste your macro here for comments.

--
Don Guillett
SalesAid Software

"Dwaine Horton" wrote in message
...
I have designed a query that I want ran from within Excel but I want
the
user
to provide some data, such as a start date and end date, and then I
want
this
data inserted into my query. Does anyone know the best way to do this?

Normally, I have just been asking them for this criteria and then
running
the queries myself, but now I want to develop something that my users
can
enter their values and press a button and then have it run the query
and
pull
the data into a spreadsheet. I am new to this and I recorded a macro
while I
did the manual process.

Any help will be greatly appreciated.

Thanks

Dwaine Horton