Excel VBA to Query Worksheet 1

[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

 

[shelby55]

Hi

Okay I figured out what I needed to do re: MS Query and I got the second worksheet to work out.

EXCEPT I can't get the calculation columns to update when I press the worksheet tab. Each column calculates how many days per month from assessment to treatment date.

The data looks like this:

AssessDate Treatment Date Total Days Jan-09 Feb-09
1/1/2009 2/1/2009 31 30 1
1/1/2009 1/15/2009 14 14 0

Note also that the source worksheet where the user enters info could have changes i.e. right now row 1 may not have enough data to calculate i.e. missing dates but these dates could be added in later so the rows need to "move down" which they do in Skip's work above but not in mine and I thought I did the same thing.

When I added

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

then it looked like there was automatic update but it doesn't seem to be working correctly because the dates are updated but not the calculation columns.

Thanks.

 

[PHV]

Look at the properties sheet of your QueryTable.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

HI PHV

Thanks but where/how do I do that?

Thanks.

 

[PHV]

Right click inside the data range and choose the properties menu.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

Okay I've got it! It looks like Skip created a data query but didn't save it and it had formulae from a worksheet that was then deleted because VBA replicated it.

So I'll have to do the same with the new one.

I'll write back if I still need help but I think you put me in the right direction!! Thanks.