Help with VBA Code

[shelby55]

Hi

Skip on this website helped me with getting formulae to work but I'm not sure what he did and I need to replicate for another worksheet in the same Excel document (I'm using Excel 2002).

I've used VBA for Access but not Excel so if someone could please explain what each of the steps is doing then that would help:

Option Explicit

Private Sub Worksheet_Activate()
Dim sConn As String, sSQL As String, sPath As String, sDB As String

sPath = ThisWorkbook.Path

sDB = Split(ThisWorkbook.Name, ".")(0)

sConn = "ODBC;DSN=Excel Files;"
sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
sConn = sConn & "DefaultDir=" & sPath & ";"
sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

With ActiveSheet
With .QueryTables(1)
.Connection = sConn
Debug.Print .CommandText
.Refresh False
End With
.Calculate
End With
End Sub

In the intermediate window is the code:
WHERE (trim(`'09_10$'`.F5) Is Not Null)
SELECT `'09_10$'`.F5 AS 'referral', `'09_10$'`.F6 AS 'assessment'
FROM `'09_10$'` `'09_10$'`
WHERE (trim(`'09_10$'`.F5) Is Not Null)
9_10$'`

Where is this is the original code i.e. I wish to change but it won't change.

Thanks for any and all assistance.

Shelby

 

[HarleyQuinn]

I wish to change but it won't change.

What do you want to change? What won't change?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[SkipVought]

You must take the SQL from the Immediate Window and ASSIGN it to a string variable.

Then make the changes in the SQL string.

Finally, assign the variable to the .CommandText property of the Query.

However, your question seemed to refer to FORMULAS adjacent and immediately to the right of the query results range. You can automatically propogate formulas by CHECKING the Fiil Down checkbox in the Data Range Properties Window of the Query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}