hey ron,

it worked! thank you so much. I have another question

im am trying to create a macro to upload a spreadsheet to an ftp site with a login nad password. I basically recorded a macro of saving to an ftp site that prompted me with a login and password. I also want the macro to not ask me what my login and password is everytime i run the macro. I jsut want it to upload as soon as i Run it. It did do the job while i was creating the macro but when i ran the macro after i created it, it didn't upload the file and gave me an error. Here is my code and my error. Any thoughts??

Run-time error '1004:'
Method 'Saveas' of object '_Workbook' Failed

Sub FTPupload()

Application.WindowState = xlMinimized
ChDir "C:\b_formats"
[COLOR=Yellow] ActiveWorkbook.SaveAs Filename:="ftp://myftp.com/account.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub[/COLOR]

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

Hey guys,

how do you rename a field name header with soql? what is the syntax? i can't seem to find it in the api doc.

example in sql would be

select mailingstreet as [street] from contact;

thanks guys

Hello,

After running the sosql query for 9 tables, contact, account, product2, etc.. I will need to save the files as a tab delimited. How can I save this without losing any information? Is there a toolkit I can dowload for this. It seems like the only way I can save without losing any data is by saving as a .xls. 

Thank you

Hi, im trying to update records directly through the office toolkit. I am able to query records with SOQL but everytime I click "Update" i get the error foghorn_test.xls could not be found. If I need to remap the update button who do i do this?? thanks guys

BK