You need to sign in to do that
Don't have an account?
how to automatically return the data set properly on the spreadsheet after runlogin()
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
looks like you are passing lbound(xx) into Cells(), try adding a 1 and see if it works, or add an additional column to your query, before "accountNumber" and see if the result is more to your liking..
Ron, thanks for the advice but Im not sure what I need to do to modify the code.
thanks again
look up the doc on Cells()
startCol = A
For i = LBound(fieldNames) To UBound(fieldNames)
r.Worksheet.Cells(endRow, startCol + i) = fieldNames(i)
Next
so you are calling Cells(12,"A"+0)
i think Cells needs to have numbers passed into it not chars, and it does not like 0, so it starts with 1 on the second time thru this loop.
try removing "startCol +i " and use instead " i + 1 "
Message Edited by Ron Hess on 12-05-2004 02:43 PM