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" wrote:
> 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
|
Similar Threads
