Edit Data Query Programmically?
 

I have a workbook with 25 worksheets. Each worksheet
represents a different general ledger bucket. I have an
ODBC read-only link to my accounting software. Is it
possible to create code to alter the microsoft query
design. In this case I am selecting 4 fields and would
like to run some code to alter the date criteria the query
is using.

TIA

Mike

Edit Data Query Programmically?
 

What is the query code like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Squid" wrote in message
...
I have a workbook with 25 worksheets. Each worksheet
represents a different general ledger bucket. I have an
ODBC read-only link to my accounting software. Is it
possible to create code to alter the microsoft query
design. In this case I am selecting 4 fields and would
like to run some code to alter the date criteria the query
is using.

TIA

Mike

Edit Data Query Programmically?
 

The SQL from Microsoft query is the following:
SELECT GLPJD.ACCTID, GLPJD.JRNLDATE, GLPJD.JNLDTLDESC,
GLPJD.JNLDTLREF, GLPJD.TRANSAMT
FROM GLPJD GLPJD
WHERE (GLPJD.ACCTID='5105') AND (GLPJD.JRNLDATE20031231
And GLPJD.JRNLDATE<=20041231)
ORDER BY GLPJD.JRNLDATE

I have a separate worksheet for each ACCTID. The
worksheet is named as the ACCTID. So in the above example
the worksheet is named 5105.


-----Original Message-----
What is the query code like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Squid" wrote in
message
...
I have a workbook with 25 worksheets. Each worksheet
represents a different general ledger bucket. I have an
ODBC read-only link to my accounting software. Is it
possible to create code to alter the microsoft query
design. In this case I am selecting 4 fields and would
like to run some code to alter the date criteria the
query
is using.

TIA

Mike


.

Edit Data Query Programmically?
 

So can you not just plug variables into the SQL string like so

varAcct = "5105"
varDate1 = "20031231"
varDate2 = "20041231"
sSQL= "SELECT GLPJD.ACCTID, GLPJD.JRNLDATE, " & _
"GLPJD.JNLDTLDESC, GLPJD.JNLDTLREF, " & _
"GLPJD.TRANSAMT" & _
"FROM GLPJD GLPJD" & _
"WHERE (GLPJD.ACCTID='" & varAcct & "') AND " & _
" (GLPJD.JRNLDATE" & varDate1 & " AND " & _
" GLPJD.JRNLDATE<=" & varDate2 & ")" & _
"ORDER BY GLPJD.JRNLDATE"

and then pass sSQL to the query?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Squid" wrote in message
...
The SQL from Microsoft query is the following:
SELECT GLPJD.ACCTID, GLPJD.JRNLDATE, GLPJD.JNLDTLDESC,
GLPJD.JNLDTLREF, GLPJD.TRANSAMT
FROM GLPJD GLPJD
WHERE (GLPJD.ACCTID='5105') AND (GLPJD.JRNLDATE20031231
And GLPJD.JRNLDATE<=20041231)
ORDER BY GLPJD.JRNLDATE

I have a separate worksheet for each ACCTID. The
worksheet is named as the ACCTID. So in the above example
the worksheet is named 5105.


-----Original Message-----
What is the query code like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Squid" wrote in
message
...
I have a workbook with 25 worksheets. Each worksheet
represents a different general ledger bucket. I have an
ODBC read-only link to my accounting software. Is it
possible to create code to alter the microsoft query
design. In this case I am selecting 4 fields and would
like to run some code to alter the date criteria the
query
is using.

TIA

Mike


.

Edit Data Query Programmically?
 

Yes, I could do that (wow this was actually pretty easy).

But what is the best way to cycle through the 25
worksheets? I get a run-time error 91 using the following:

For i = 1 to 25

varAcct = ActiveSheet.Name

<- Code -

ActiveSheet.Next.Select
Next i


-----Original Message-----
So can you not just plug variables into the SQL string
like so

varAcct = "5105"
varDate1 = "20031231"
varDate2 = "20041231"
sSQL= "SELECT GLPJD.ACCTID, GLPJD.JRNLDATE, " & _
"GLPJD.JNLDTLDESC, GLPJD.JNLDTLREF, " & _
"GLPJD.TRANSAMT" & _
"FROM GLPJD GLPJD" & _
"WHERE (GLPJD.ACCTID='" & varAcct & "')
AND " & _
" (GLPJD.JRNLDATE" & varDate1
& " AND " & _
" GLPJD.JRNLDATE<=" & varDate2
& ")" & _
"ORDER BY GLPJD.JRNLDATE"

and then pass sSQL to the query?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Squid" wrote in
message
...
The SQL from Microsoft query is the following:
SELECT GLPJD.ACCTID, GLPJD.JRNLDATE, GLPJD.JNLDTLDESC,
GLPJD.JNLDTLREF, GLPJD.TRANSAMT
FROM GLPJD GLPJD
WHERE (GLPJD.ACCTID='5105') AND (GLPJD.JRNLDATE20031231
And GLPJD.JRNLDATE<=20041231)
ORDER BY GLPJD.JRNLDATE

I have a separate worksheet for each ACCTID. The
worksheet is named as the ACCTID. So in the above
example
the worksheet is named 5105.


-----Original Message-----
What is the query code like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Squid" wrote in
message
...
I have a workbook with 25 worksheets. Each worksheet
represents a different general ledger bucket. I
have an
ODBC read-only link to my accounting software. Is it
possible to create code to alter the microsoft query
design. In this case I am selecting 4 fields and
would
like to run some code to alter the date criteria the
query
is using.

TIA

Mike


.



.

Edit Data Query Programmically?
 

Squid,

If 25 is all the worksheets, you could use

For Each sh in Activeworkbook.Worksheets
varAcct = sh.Name
...
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Squid" wrote in message
...
Yes, I could do that (wow this was actually pretty easy).

But what is the best way to cycle through the 25
worksheets? I get a run-time error 91 using the following:

For i = 1 to 25

varAcct = ActiveSheet.Name

<- Code -

ActiveSheet.Next.Select
Next i


-----Original Message-----
So can you not just plug variables into the SQL string
like so

varAcct = "5105"
varDate1 = "20031231"
varDate2 = "20041231"
sSQL= "SELECT GLPJD.ACCTID, GLPJD.JRNLDATE, " & _
"GLPJD.JNLDTLDESC, GLPJD.JNLDTLREF, " & _
"GLPJD.TRANSAMT" & _
"FROM GLPJD GLPJD" & _
"WHERE (GLPJD.ACCTID='" & varAcct & "')
AND " & _
" (GLPJD.JRNLDATE" & varDate1
& " AND " & _
" GLPJD.JRNLDATE<=" & varDate2
& ")" & _
"ORDER BY GLPJD.JRNLDATE"

and then pass sSQL to the query?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Squid" wrote in
message
...
The SQL from Microsoft query is the following:
SELECT GLPJD.ACCTID, GLPJD.JRNLDATE, GLPJD.JNLDTLDESC,
GLPJD.JNLDTLREF, GLPJD.TRANSAMT
FROM GLPJD GLPJD
WHERE (GLPJD.ACCTID='5105') AND (GLPJD.JRNLDATE20031231
And GLPJD.JRNLDATE<=20041231)
ORDER BY GLPJD.JRNLDATE

I have a separate worksheet for each ACCTID. The
worksheet is named as the ACCTID. So in the above
example
the worksheet is named 5105.


-----Original Message-----
What is the query code like?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Squid" wrote in
message
...
I have a workbook with 25 worksheets. Each worksheet
represents a different general ledger bucket. I
have an
ODBC read-only link to my accounting software. Is it
possible to create code to alter the microsoft query
design. In this case I am selecting 4 fields and
would
like to run some code to alter the date criteria the
query
is using.

TIA

Mike


.



.