Using VBA to Export Multi-page Report

[MrAustin]

Good afternoon all --

I have created a macro that will export fields from Access to a formatted Excel spreadsheet so that it can be printed and mailed to one of our vendors. They are VERY specific about the format of this form.

At any rate, this works when someone is entering data on a dispute form, they can click a Print Dispute button, and the following code is executed to load the info into Excel and print the form:

    Dim app As Excel.Application
    Dim wb As Excel.Workbook
    Dim sht As Excel.Worksheet
    Dim rng As Excel.Range
    Dim cht As Excel.Chart
    
    Dim contactName As String
        
    Set app = CreateObject("Excel.Application")
    app.Visible = True
    Set wb = app.Workbooks.Open("X:DisputeTemplate.xls")
    Set sht = wb.ActiveSheet
    
    If Me.disp_dateRequested.Value <> "" Then
        sht.Cells(15, 5) = Me.disp_dateRequested.Value
    End If
    
    If Me.disp_contactName.Value <> "" Then
        contactName = DLookup("user_name", "tblUsers", "user_id = " & Me.disp_contactName.Value)
        sht.Cells(18, 5) = contactName
    End If
    
    If Me.disp_contactPhone.Value <> "" Then
        sht.Cells(19, 5) = Me.disp_contactPhone.Value
    End If
    
    If Me.disp_contactFax.Value <> "" Then
        sht.Cells(20, 5) = Me.disp_contactFax.Value
    End If
    
    If Me.disp_targetDocNum.Value <> "" Then
        sht.Cells(15, 11) = Me.disp_targetDocNum.Value
    End If
    
    If Me.disp_amount <> "" Then
        sht.Cells(16, 11) = Me.disp_amount.Value
    End If
    
    If Me.disp_storeNum.Value <> "" Then
        sht.Cells(19, 9) = Me.disp_storeNum.Value
    End If
    
    If Me.disp_id.Value <> "" Then
        sht.Cells(20, 9) = Me.disp_id.Value
    End If
    
    If Me.txtTypeInfo.Value <> "" Then
        If Me.cboSelectType.Value = "Credit Rebill" Then
            sht.Cells(20, 10) = "Original SV Invoice #" & Me.txtTypeInfo.Value
        End If
    End If
    
    If Me.cboSelectType.Value <> "" Then
        If Me.cboSelectType.Value = "Credit Rebill" Then
            sht.Cells(51, 2) = "X"   'Check off Other Type
            sht.Cells(52, 5) = "___Credit___"  'First Line
            sht.Cells(53, 5) = "___Rebill___"  'Second Line
            sht.Cells(53, 8) = "  " & Me.disp_comments.Value  'Comments
        End If
        
        If Me.cboSelectType.Value = "Duplicate Credits" Then
            sht.Cells(51, 2) = "X"   'Check off Other Type
            sht.Cells(52, 5) = "___Duplicate___"  'First Line
            sht.Cells(53, 5) = "___Credits___"  'Second Line
            sht.Cells(53, 8) = "  " & Me.disp_comments.Value  'Comments
        End If
        
        If Me.cboSelectType.Value = "Unpaid Invoice(s)" Then
            sht.Cells(39, 2) = "X"    'Check off Unpaid Invoice(s) Type
            sht.Cells(42, 6) = "___" & Me.disp_typeInfo.Value & "___"
            sht.Cells(53, 8) = "  " & Me.disp_comments.Value  'Comments
        End If
        
        If Me.cboSelectType.Value = "Other" Then
            sht.Cells(51, 2) = "X"    'Check off Other Type
            sht.Cells(52, 5) = "See Below"
            sht.Cells(56, 8) = "  " & Me.disp_comments.Value  'Comments
        End If
    End If
    
    
    
    'Print Active Workbook
    app.ActiveWorkbook.PrintOut Copies:=1, Collate:=True
    app.Quit
    app.SendKeys ("{TAB} ") 'Do not save changes

Sorry for the long code. What I would like to do is setup a "print queue" of sorts, where each dispute will have a flag that tells whether or not it has been printed at least once. If not, I want this script to be adapted to where it will export the data to excel, put in a page break, then keep adding new "forms" as it goes.

Would I be better off designing the form through an access report and skipping excel altogether?

I didn't think this one out very thoroughly, I'm afraid.

Thanks for any insight!

 

[modest]

I think VBA is linear in that it doesn't continue until the previous operation is completed.