Results 1 to 2 of 2
  1. #1
    besuchanko is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    32

    Auto Export Access 2007 Report to Formatted Excel File Using Automation

    I've read lots of threads on this topic and cannot get anything to work for me. I understand that what I want to do involves VBA and automation. I don't know or understand VBA, so I'm hoping someone can just give me a short example that will work so I can figure things out on my own from there.



    I have created a report that I want to automatically export to Excel when it is opened. I've gotten that to work using this code in the On Activate Event:

    Code:
    Private Sub Report_Activate()
    DoCmd.OutputTo acOutputReport, "Presenter Data Report", acFormatXLS, "Presenter Data Report.xls", True
    End Sub
    Could someone provide code so that the resulting Excel file will have the top row bolded? I think from there I can figure out how to adjust other properties if I can get this to work. I do not want to create a preformatted Excel template. I want this to all work using VBA code in the On Activate Event.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Manipulating an Excel file by code in Access requires VBA to establish an Excel object that is set to the file. Must first select VBA reference to Microsoft Excel 12.0 Object Library. From the VBA editor, click Tools>References>find the reference and check it.

    Review: http://www.vbforums.com/showthread.p...ave-excel-file

    Then you want to set properties of cells.

    Dim xl As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wsht As Excel.Worksheet
    Set xl = New Excel.Application
    Set wbk = xl.Workbooks.Open("D:\Book1.xlsx")
    Set wsht = wbk.Worksheets("Sheet1")
    wsht.Rows("1:1").Font.Bold = True
    wbk.Close (True)
    xl.Quit

    One way to create new code for manipulating an Excel file is to use Excel Macro Recorder (in Excel, not Access). Macros in Excel are essentially VBA code. Sometimes need to clean up the generated code to simplify and make more efficient. That's how I discovered the .Font.Bold = True syntax.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Export formatted access report into excel
    By 555Rage in forum Import/Export Data
    Replies: 5
    Last Post: 10-10-2012, 11:18 AM
  2. Replies: 2
    Last Post: 08-05-2012, 06:32 PM
  3. Export Table in Access 2007 to Multiple Workbooks in Excel 2007
    By hutchinsm in forum Import/Export Data
    Replies: 5
    Last Post: 03-01-2012, 05:23 PM
  4. Ms Access 2007 report export to excel 2007
    By Stan2man in forum Access
    Replies: 6
    Last Post: 11-23-2011, 01:24 PM
  5. Replies: 3
    Last Post: 08-18-2011, 05:04 PM

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