Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Canberra, Australian Capital Territory, Australia
    Thanked 0 Times in 0 Posts

    Access to Excel worksheet (Access 2003)

    Hello Access Gurus,

    I am wondering whether it is possible to export data from an Access query to specific cells in an existing Excel Workbook? I am looking for some model code that can help me with this problem.

    I look forward to your response.

    Kind regards,


  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Melbourne, Australia
    Thanked 27 Times in 27 Posts

    Re: Access to Excel worksheet (Access 2003)

    Are the cells fixed?

    I did this kind of thing with a tennis club database once. See if you can make head or tail of this.

  3. #3
    Join Date
    Nov 2006
    Denver, Colorado, USA
    Thanked 0 Times in 0 Posts

    Re: Access to Excel worksheet (Access 2003)

    In VBA you can open an Access application and then open an existing Access file or create a new one. Once you have the spreadsheet open you can navigate it and modify the cell contents and properties, just as though you were manually editing the spreadsheet.

    During development, make sure you make the spreadsheet visible as soon as you open it. Otherwise, if any errors occur you will likely end up with several files open that you cannot get too. You will discover them when you finally run out of memory or when you log out of Windows. Once all your troubleshooting is complete, you can hold off on showing the spreadsheet until it's completely populated, which will reduce the amount of time your code takes to run.

    The following excerpt is from some code I wrote that creates an Excel report from Access. Line 60 creates the instance of Access. Line 70 creates the workbook and line 80 creates the sheet that the user should eventually see. Once the sheet is created you can remove the default sheets. The remaining lines in the excerpt show how to manipulate cells in the sheet. You can get a lot fancier than what's shown.

    Don't let the line numbers throw you . I use a utility to add line numbers to my projects. GetDirData is a function I wrote to return the path where I want the workbook to be saved.

    The full code actually builds a new workbook with only one worksheet, adds data and formatting to the worksheet and then saves it. After saving the worksheet it is automatically opened for the user to view, edit and print.

    'Create an Excel instance.
    60 Set appExcel = CreateObject("Excel.Application")

    'Set a reference to the workbook and worksheet.
    70 Set wrkCoversheet = appExcel.Workbooks.Add(GetDirData & "Coversheet.xlt")
    80 Set shtCover = wrkCoversheet.Sheets("Cover")

    'Fill in the cells that come from the database.
    90 shtCover.Cells(10, "C").Value = rstOrder!FullNameCustomer
    100 shtCover.Cells(10, "I").Value = Application.CurrentUser
    110 shtCover.Cells(12, "C").Value = rstOrder!Address

    Hope this helps get you started,


Posting Permissions

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