Edit Data Query Programmically?

[Squid]

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

 

[Bob Phillips]

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]

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.JRNLDATE>20031231
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.

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)