G
Guest
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
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