none
Call SSRS Report from VBA RRS feed

  • Question

  • I have an SSRS Report with 1 parameter.  I also have an ERP application which supports the ability to write VBA code to call external applications.  In this case I have written some VBA code to allow users to view the SSRS Report and pass the parameter by simply pressing a button on the ERP screen.  The below code works fine for viewing:

    Dim ie As Object
    Dim URL As String

        URL = "http://dbdelta1/ReportServer/Pages/ReportViewer.aspx?" _
        & "%2fDELTA%2fField+Services%2fField_Service_Report&SVCALLNBR=" _
        & ServiceCallEntryUpdate.CallNumber

        Set ie = CreateObject("InternetExplorer.Application")
        ie.Navigate URL
        ie.Visible = True

    PROC_EXIT:
      Exit Sub

    What I would like to do now instead of viewing the report I would like to create a pdf for the report and name the file that is created with the parameter value (in this case CallNumber). In addition, I would like to specify the path where the pdf is generated but cannot figure out how to do that. Unfortunately all the code has to be done in VBA.

    Any ideas?  Any help is greatly appreciated.

    Thanks

    Friday, August 2, 2013 2:08 PM

Answers

  • Hi doddsw916.

    According to your description, this is a complicated issue. And some technologys are out of current forum support.

    However, you could try to refer to the below ways to implement this function:
    1. SSRS could export PDF file, so we can access the SSRS and the SSRS will generate a PDF file for us to download.
    Following code shows how to download a file on the server in VBA.

    Sub test()
           Dim H, S
           Set H = CreateObject("Microsoft.XMLHTTP")
           H.Open "GET", "http://www.msdn.com/test.exe", False
    H.send
    Set S = CreateObject("ADODB.Stream")
    S.Type = 1
    S.Open
    S.write H.Responsebody
    S.savetofile "E:/test.exe", 2
    S.Close
    End Sub
    

    You could access this link to get how to generate a PDF file in SSRS:
    http://www.jensbits.com/2012/01/23/generate-sql-server-reporting-services-ssrs-report-as-pdf-from-url-with-vb-net-or-c-net/
    Also, you can access ssrs forum to get help:
    http://social.technet.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlreportingservices

    2. We can use component named Windows Script host Object Model(add reference wshom.ocx in system32 folder). This component can call printer to save html as a PDF file. However, it need to add PDF printer driven.
    Following code shows how to save html as a PDF file in VBA:

    'A function that uses IE to print the contents of Google.com to a PDF document
    Sub printgoogle()
        Dim Explorer As Object
        Dim eQuery As Long 'return value type for QueryStatusWB
        Dim i As Integer
        Dim fTime As Single
    
        'See function below, to set the default printer to PDFCreator.  Note:  The user would probably be grateful if you checked to see what is the current default printer and set it back when finished printing
        SetDefaultPrinter "PDFCreator"
    
        'Connect to Internet Explorer
        Set Explorer = CreateObject("InternetExplorer.Application")
        'Open some document.  This is usually a file on your computer, but I use Google here for test purposes
        Explorer.Navigate "www.msdn.com"
    
    TryAgain:
            'Wait for 2 seconds to let IE load the document
            fTime = Timer
            Do While fTime > Timer - 2
                DoEvents
            Loop
            eQuery = Explorer.QueryStatusWB(6)  'get print command status
            If eQuery And 2 Then
                Explorer.ExecWB 6, 2, "", ""   'Ok to Print? Then execute the Print (6) command, without displaying the print dialog (2)
                'Wait for 2 seconds while IE prints
                fTime = Timer
                Do While fTime > Timer - 2
                    DoEvents
                Loop
            Else
                GoTo TryAgain
            End If
    
    End Sub
    
    'This function sets the Windows default printer to whatever printer you insert as parameter
    Public Sub SetDefaultPrinter(ByVal printerName As String)
        Dim oSH As WshNetwork
        Set oSH = New WshNetwork
        oSH.SetDefaultPrinter printerName
        Set oSH = Nothing
    End Sub
    

    Hope will help you.

    George Hua
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 6, 2013 5:42 AM
    Moderator