How to create a custom excel file using VBA?

Options
Fernando Leite
Fernando Leite ✭
Tenth Anniversary First Reply
in VBA/API

hello everyone. I am creating a program where I use 2 existing queries (one constituent, one action) to come up with my desired output. Right now, everything works but I'm only able to present the information through "Debug.Print". Below, is my program:

Public Sub ConsecutiveGivingAction()

Dim oServices As REServices

oServices.Init REApplication.SessionContext

Dim oQuerySet As CQuerySet

Set oQuerySet = New CQuerySet

oQuerySet.Init REApplication.SessionContext

Dim testQuery As CQuerySet

Set testQuery = New CQuerySet

testQuery.Init REApplication.SessionContext

Dim oSearch As IBBSearchScreen

Set oSearch = oServices.CreateServiceObject(bbsoSearchScreen)

oSearch.Init REApplication.SessionContext

oSearch.AddSearchType SEARCH_QUERY

oSearch.ShowSearchForm

If Not oSearch.SelectedDataObject Is Nothing Then

oQuerySet.QueryID = oSearch.SelectedID

oQuerySet.OpenQuerySet

End If

testQuery.QueryID = 14274

testQuery.OpenQuerySet

Debug.Print oQuerySet.FieldName(2) & " " & oQuerySet.FieldName(3) & " " & oQuerySet.FieldName(4) & " " & oQuerySet.FieldName(5) & " " & testQuery.FieldName(3) & " " & testQuery.FieldName(4)

Do While Not oQuerySet.EOF

testQuery.OpenQuerySet

count = 0

Do While Not testQuery.EOF

If testQuery.fieldValue(7) = oQuerySet.fieldValue(1) Then

Debug.Print oQuerySet.fieldValue(2) & " " & oQuerySet.fieldValue(3) & " " & oQuerySet.fieldValue(4) & " " & oQuerySet.fieldValue(5) & " " & testQuery.fieldValue(3) & " " & testQuery.fieldValue(4)

testQuery.MoveNext

count = 1

Else

testQuery.MoveNext

End If

Loop

If count = 0 Then

Debug.Print oQuerySet.fieldValue(2) & " " & oQuerySet.fieldValue(3) & " " & oQuerySet.fieldValue(4) & " " & oQuerySet.fieldValue(5)

End If

oQuerySet.MoveNext

Loop

oQuerySet.Closedown

testQuery.Closedown

oSearch.Closedown

Set oSearch = Nothing

Set testQuery = Nothing

Set oQuerySet = Nothing

End Sub

I know my variables could be better named haha. I'll do that later. Anyways, I'm trying to present this information in a good way to the user. I'm not sure if there's a way I can use something within Raiser's Edge but my main goal was to create an Excel file in my program. Is there anyway I can do that?

I really wish there was more documentation about it. Sometimes the "VBA Guide" is not enough. I guess that's why this forum is for haha.

Comments

  • Fernando Leite
    Fernando Leite ✭
    Tenth Anniversary First Reply
    Fernando Leite:

    hello everyone. I am creating a program where I use 2 existing queries (one constituent, one action) to come up with my desired output. Right now, everything works but I'm only able to present the information through "Debug.Print". Below, is my program:

    Public Sub ConsecutiveGivingAction()

    Dim oServices As REServices

    oServices.Init REApplication.SessionContext

    Dim oQuerySet As CQuerySet

    Set oQuerySet = New CQuerySet

    oQuerySet.Init REApplication.SessionContext

    Dim testQuery As CQuerySet

    Set testQuery = New CQuerySet

    testQuery.Init REApplication.SessionContext

    Dim oSearch As IBBSearchScreen

    Set oSearch = oServices.CreateServiceObject(bbsoSearchScreen)

    oSearch.Init REApplication.SessionContext

    oSearch.AddSearchType SEARCH_QUERY

    oSearch.ShowSearchForm

    If Not oSearch.SelectedDataObject Is Nothing Then

    oQuerySet.QueryID = oSearch.SelectedID

    oQuerySet.OpenQuerySet

    End If

    testQuery.QueryID = 14274

    testQuery.OpenQuerySet

    Debug.Print oQuerySet.FieldName(2) & " " & oQuerySet.FieldName(3) & " " & oQuerySet.FieldName(4) & " " & oQuerySet.FieldName(5) & " " & testQuery.FieldName(3) & " " & testQuery.FieldName(4)

    Do While Not oQuerySet.EOF

    testQuery.OpenQuerySet

    count = 0

    Do While Not testQuery.EOF

    If testQuery.fieldValue(7) = oQuerySet.fieldValue(1) Then

    Debug.Print oQuerySet.fieldValue(2) & " " & oQuerySet.fieldValue(3) & " " & oQuerySet.fieldValue(4) & " " & oQuerySet.fieldValue(5) & " " & testQuery.fieldValue(3) & " " & testQuery.fieldValue(4)

    testQuery.MoveNext

    count = 1

    Else

    testQuery.MoveNext

    End If

    Loop

    If count = 0 Then

    Debug.Print oQuerySet.fieldValue(2) & " " & oQuerySet.fieldValue(3) & " " & oQuerySet.fieldValue(4) & " " & oQuerySet.fieldValue(5)

    End If

    oQuerySet.MoveNext

    Loop

    oQuerySet.Closedown

    testQuery.Closedown

    oSearch.Closedown

    Set oSearch = Nothing

    Set testQuery = Nothing

    Set oQuerySet = Nothing

    End Sub

    I know my variables could be better named haha. I'll do that later. Anyways, I'm trying to present this information in a good way to the user. I'm not sure if there's a way I can use something within Raiser's Edge but my main goal was to create an Excel file in my program. Is there anyway I can do that?

    I really wish there was more documentation about it. Sometimes the "VBA Guide" is not enough. I guess that's why this forum is for haha.

    Nevermind. I got it to work.

Categories