Results 1 to 4 of 4
  1. #1
    anavagomez is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    31

    Question Stored Procedure output to excel file on button click

    Hello,



    I am working on a public function that gets called by the click event of a button on a form.

    The function is supposed to execute a stored procedure, read the record set into an excel file, and finally open the excel file.

    I have been searching for any examples of how to do this routine, and finding bits and pieces I put together the code for the function as I understood it. Lamentably it is not working.

    I am hoping to get help in this forum, someone who has already completed a task like this one.

    Any help is very much appreciated.

    Here is my code, please if more details are needed, just let me know.

    Code:
    Public Function WriteReport()
    
    
        Dim con As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim i As Integer
        Dim fld As Object
        
        Set con = New ADODB.Connection
        With con
            .ConnectionString = GetConnectionString("DB")
            .Open
        End With
        
        Set rs = New ADODB.Recordset
        rs.ActiveConnection = con
        
        rs.Open "SELECT * FROM [dbo].[ReportView]", con
    
    
       ' column headers
        i = 0
        
        Sheets(1).Range("A1").Activate
        
        For Each fld In rs.Fields
            
            ActiveCell.Offset(0, i) = fld.Name
            
            i = i + 1
        
        Next fld
    
    
        ' data rows
        Sheets(1).Range("A2").CopyFromRecordset rs
       
        rs.Close: Set rs = Nothing
        con.Close: Set con = Nothing
       
        ThisWorkbook.SaveAs ("C:\Report.csv")
    
    
    End Function
    Last edited by anavagomez; 05-24-2017 at 07:47 PM. Reason: updated code

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It would help to know what "not working" means exactly.

    I'd open an ADO recordset on a stored procedure like:

    rs.Open "EXEC ArrangementsWithoutResellers"

    Presuming the other things like connection were already done.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    anavagomez is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    31
    If you look at my code, everything goes fine until I get to line:

    Code:
    Sheets(1).Range("A1").Activate
    


    When I go over that line, I get an error that reads:

    Method 'Sheets' of object '_Global' failed

    Can you see what might be the issue?

    Many thanks.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Your code in Post #1 doesn't look right.
    Somewhere there should be code that looks like
    Code:
    Public Sub doExcelAutomation()
    On Error GoTo doExcelAutomationErr
    
    Dim mySheet As Object
    Dim xlApp As Object
    Dim strName As String
    
        strName = "C:\MyNewExcelFile.xls"
        Set xlApp = CreateObject("Excel.Application")
        Set mySheet = xlApp.Workbooks.Open("C:\MyExistingExcelFile.xls").Sheets(1)
    
    <snip>
    See https://www.microsoftaccessexpert.co...utomation.aspx
    and
    http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 33
    Last Post: 09-25-2015, 08:39 AM
  2. Importing Excel file stored on sharepoint
    By tezza79 in forum Import/Export Data
    Replies: 0
    Last Post: 01-27-2015, 01:29 AM
  3. Replies: 11
    Last Post: 10-02-2013, 01:50 PM
  4. Replies: 4
    Last Post: 01-03-2012, 08:11 PM
  5. Replies: 0
    Last Post: 10-04-2009, 04:11 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums