Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: Need Help Exporting Tables To Excel

    Hey guys,

    I am trying to create an auto-export for my user where they just have to click a button, and it will send the table to an excel spreadsheet.

    I got excited last week when I discovered that Access has this function built in to their macro tasks. Only one problem. I have three tables to export, and I need all the tables on the same spreadsheet. The tables will be separated by sheet tabs at the bottom.

    So I need some way where I can specifiy the spreadsheet name to create and the sheet name to create for a single table. It looks like this may be possible to code in VBA. I've seen some transfer commands. But I don't know anything about VBA, and the database is already running custom VB code for a form.

    Can anyone help me or point me to a tutorial? I'm at my wits end with this. Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use Automation to "pilot an instance of Excel from some VBA code in Access. This code could write any data you want in workseets you select. As a guideline:
    Code:
    Function OpenExcelDocument(ByVal DocName As String)
    
        ' You must have a reference to the Excel Object Library in your project
        ' (Tools --> References --> 'Microsoft Excel 11.0 Object Library' for Office 2003).
        '
        Dim appXL As Excel.Application
        
        Set appXL = New Excel.Application
        With appXL
            .Workbooks.Open Filename:=DocName
            '
            ' Your code goes here
            '
            .ActiveWorkbook.Save
            .ActiveWorkbook.Close
            .Quit
        End With
        Set appXL = Nothing
    
    End Function
    Have a nice day!

  3. #3
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by Sinndho View Post
    You can use Automation to "pilot an instance of Excel from some VBA code in Access. This code could write any data you want in workseets you select. As a guideline:
    Code:
    Function OpenExcelDocument(ByVal DocName As String)
    
        ' You must have a reference to the Excel Object Library in your project
        ' (Tools --> References --> 'Microsoft Excel 11.0 Object Library' for Office 2003).
        '
        Dim appXL As Excel.Application
        
        Set appXL = New Excel.Application
        With appXL
            .Workbooks.Open Filename:=DocName
            '
            ' Your code goes here
            '
            .ActiveWorkbook.Save
            .ActiveWorkbook.Close
            .Quit
        End With
        Set appXL = Nothing
    
    End Function

    Thanks for the reply. I never heard of Excel automation, but I'll do some research on it.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's very powerful and allows you to control an application (Access, Excel, Word, etc.) using VBA code. The only requirement is that you have to create a reference to the external application (Microsoft Excel 11.0 Object Library in this case), then to know the object model of the application you instanciate. Fortunately Intellisense (Microsoft's implementation of autocompletion) is there to assist you.

    You're welcome by the way!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •