hey guys,

im am currently automating the office toolkit soql query disaply results and im almost done except for one minor problem.

if you scroll to the bottom i modfied this part of the code

    startRow = 12
    startCol = A
    endRow = 12
    endCol = A

PROBLEM: When dataset populates the spreadsheet, it doesn't include accountnumber from this soql

It only starts at accountstatus__c . What values do i place in startrow,startcol,end rowm end col to get the fields everytime in same order? I can do this manually by selecting row 12. Howver, the whole point is to automate .

code:

Thank you

select AccountNumber, AccountStatus__c, Annual_Revenue__c, BillingCity, BillingCountry, BillingPostalCode, BillingState, BillingStreet, CreatedById, CreatedDate, CurrentPrimaryChallenge__c, Description, DLC_territory__c, External_Auditor__c, Fax, Id, Industry, Industry_2__c, Industry_3__c, LastModifiedById, LastModifiedDate, Name, OwnerId, Ownership, ParentId, Phone, Physical_City__c, Physical_Country__c, Physical_State__c, Physical_Street__c, Physical_Zip_Postal_Code__c, SystemModstamp, TickerSymbol, Type, Website from account

Private Sub DisplayQueryResultSet(ByVal qrs As QueryResultSet, ByVal PromptForDestination As Boolean, ByRef fieldNames() As String)

    Dim r As Range
    Dim startRow As Integer
    Dim startCol As Integer
    Dim endRow As Integer
    Dim endCol As Integer
    Dim i As Integer
    Dim sobj As sobject
    Dim fld As Field
    Dim soql As String
   
    On Error Resume Next
    If PromptForDestination Then
     '   Set r = Application.InputBox(Prompt:="Select the destination cell for your data.", Type:=8)
    'Else
        Set r = Application.ActiveCell
    End If
   
    If Err.Number <> 0 Then
        Debug.Print Err.Number & ": " & Err.Description
        Exit Sub
    End If
   
    If r.Row < 12 Then
        MsgBox "Please select a cell below row 11.", vbOKOnly + vbCritical, "Bad Destination Selection"
        Exit Sub
    End If
   
    startRow = 12
    startCol = A
    endRow = 12
    endCol = A
   
    soql = Range("soql").Value
   
    r.Worksheet.Names("query").RefersToRange.ClearContents
    FormatData r.Worksheet.Names("query").RefersToRange
   
    fieldNames = GetSOQLFieldList(soql)
   
    endCol = startCol + UBound(fieldNames)
   
    For i = LBound(fieldNames) To UBound(fieldNames)
        r.Worksheet.Cells(endRow, startCol + i) = fieldNames(i)
    Next
   
    endRow = endRow + 1
   
    For Each sobj In qrs
        For i = LBound(fieldNames) To UBound(fieldNames)
            Set fld = sobj.Item(fieldNames(i))
            r.Worksheet.Cells(endRow, startCol + i) = fld.Value
        Next
        endRow = endRow + 1
    Next
    endRow = endRow - 1
   
    AddDataRange r.Worksheet, startRow, endRow, startCol, endCol
   
    FormatHeader r.Worksheet.Range(r.Worksheet.Cells(startRow, startCol), r.Worksheet.Cells(startRow, endCol))
    r.Worksheet.Cells(startRow + 1, startCol).Select

End Sub

Message Edited by BK on 12-03-2004 10:39 AM