Code problem working with QueryTable

B

Billy B

I am attempting to query a database and insert the data into ranges in the
worksheet. The database is of volunteers and training times. The desired
result is to show volunteers for 8:00, 9:00, etc. classes. The results would
look like this:

001 Tom Jones 8:00 A1
002 Bill Jennings 8:00 B2 etc

001 Tom Hones 9:00 C3
003 Ken Walin 9:00 etc

In the code below, I am trying to create the ranges; insert the data for the
hour starting at the range; create the next range two lines below the first
range. The number of individuals for each hour will change day by day hence
the reason I am trying to do this using ranges (for sorting etc later in the
code.)

Problem: either I can't use range names in the QueryTables argument or I
have a code problem. Help or direction would be appreciated. Thank you.

Private Sub TestingRange_Click()
Dim rngSortData8 As Range, rngSortData9 As Range
Dim wksList As Worksheet
Set wksList = ActiveWorkbook.Sheets("Sheet1")
Set rngSortData8 = wksList.Range("A3")
rngSortData8.Name = "Eight"

'Range("A20") for testing only
'Should be two rows down from end of first query result
Set rngSortData9 = wksList.Range("A20")
rngSortData9.Name = "Nine"

CreateQueryTables

End Sub
Sub CreateQueryTables()

Dim strCnn As String, strCmdTxt As String
strCnn = "ODBC;DBQ=F:\EducationPro\EducationPro-New.mdb;" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;PageTimeout=15"
strCmdTxt = Empty


strCmdTxt = "SELECT [Volunteer].IDNumber, [Volunteer].Name," & _
"[Volunteer].[8:00]FROM [Volunteer]"

rngSortData8.Select

' Create the QueryTable on the ActiveSheet at the range stated.
'Insert query results starting at range name Eight\
'*****************************************
'I get an error in the Range("Eight") but not if I use Range("A2')
With QueryTables.Add(Connection:=strCnn, Destination:=Range("Eight"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh
End With

'*****************************************
'I need to determine the last row of the data inserted from above query
'move down two rows and either create the named range "nine" to insert
new
'query results or insert results using the cell reference that would
'correspond to the need.
'*****************************************
strCmdTxt = "SELECT [Volunteer].IDNumber, [Volunteer].Name," & _
"[Volunteer].[9:00]FROM [Volunteer]"

rngSortData9.Select

' Create the QueryTable on the ActiveSheet at the range stated.
'Insert query results starting at range name Nine
With QueryTables.Add(Connection:=strCnn, Destination:=Range("Nine"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh
End With

End Sub
 
J

Joel

I got it to work with the following two changes

1) Aded active sheet to query
With ActiveSheet.QueryTables.Add(Connection:=Strcnn,
Destination:=Range("Eight"))
2) The sheet name was in the name Eight.

I think you have to make usre the sheet in the QueryTable and the name range
must match.

Billy B said:
I am attempting to query a database and insert the data into ranges in the
worksheet. The database is of volunteers and training times. The desired
result is to show volunteers for 8:00, 9:00, etc. classes. The results would
look like this:

001 Tom Jones 8:00 A1
002 Bill Jennings 8:00 B2 etc

001 Tom Hones 9:00 C3
003 Ken Walin 9:00 etc

In the code below, I am trying to create the ranges; insert the data for the
hour starting at the range; create the next range two lines below the first
range. The number of individuals for each hour will change day by day hence
the reason I am trying to do this using ranges (for sorting etc later in the
code.)

Problem: either I can't use range names in the QueryTables argument or I
have a code problem. Help or direction would be appreciated. Thank you.

Private Sub TestingRange_Click()
Dim rngSortData8 As Range, rngSortData9 As Range
Dim wksList As Worksheet
Set wksList = ActiveWorkbook.Sheets("Sheet1")
Set rngSortData8 = wksList.Range("A3")
rngSortData8.Name = "Eight"

'Range("A20") for testing only
'Should be two rows down from end of first query result
Set rngSortData9 = wksList.Range("A20")
rngSortData9.Name = "Nine"

CreateQueryTables

End Sub
Sub CreateQueryTables()

Dim strCnn As String, strCmdTxt As String
strCnn = "ODBC;DBQ=F:\EducationPro\EducationPro-New.mdb;" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;PageTimeout=15"
strCmdTxt = Empty


strCmdTxt = "SELECT [Volunteer].IDNumber, [Volunteer].Name," & _
"[Volunteer].[8:00]FROM [Volunteer]"

rngSortData8.Select

' Create the QueryTable on the ActiveSheet at the range stated.
'Insert query results starting at range name Eight\
'*****************************************
'I get an error in the Range("Eight") but not if I use Range("A2')
With QueryTables.Add(Connection:=strCnn, Destination:=Range("Eight"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh
End With

'*****************************************
'I need to determine the last row of the data inserted from above query
'move down two rows and either create the named range "nine" to insert
new
'query results or insert results using the cell reference that would
'correspond to the need.
'*****************************************
strCmdTxt = "SELECT [Volunteer].IDNumber, [Volunteer].Name," & _
"[Volunteer].[9:00]FROM [Volunteer]"

rngSortData9.Select

' Create the QueryTable on the ActiveSheet at the range stated.
'Insert query results starting at range name Nine
With QueryTables.Add(Connection:=strCnn, Destination:=Range("Nine"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top