Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Embedded Excel or Export Question (Access 2K)

    Is there a way to export a query to an embedded excel spreadsheet which has been placed on a form. I would like the ability to have the form open and update the embedded excel spreadsheet based on a query that will likely be filtered from a prior form.

    Basically, the information for the company needs to be exported to an excel spreadsheet, but I want the user to view the spreadsheet within the database and not have excel open separately. The problem I have had with excel opening separately is the cells are not formatted in the way I want.

    I am not proficient in VBA within excel and am not sure if or how I can accomplish this task.

    I am assuming that I can create this as a datasheet as a subform instead of an embedded excel spreadsheet. Is there a way to format the export so that the resulting excel spreadsheet is formatted in the way that I want, or can I accomplish this using an embedded excel spreadsheet, or am I just way off base?

    Any assistance will be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Embedded Excel or Export Question (Access 2K)

    I'm confused. How is embedding an Excel spreadsheet in a form going to ensure that the cells are formatted right? Embedding doesn't magically produce correct formatting, as far as I am aware.

    Since you only state that "the cells are not formatted in the way I want" without providing any details, it is impossible to give advice.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Embedded Excel or Export Question (Access 2K)

    <hr>Basically, the information for the company needs to be exported to an excel spreadsheet . . .<hr>
    Is the reason it needs to be in Excel because most users don't have Access? If so, embedding an Excel object as an OLE object wouldn't work anyhow. Even if they do have Access, they would still be opening a separate instance of Excel to view or edit the data. Give a bit more of the circumstances you are trying to deal with, and we may be able to suggest alternatives.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Embedded Excel or Export Question (Access 2K)

    Sorry, this is not clear. There is a particular query that I will need to enable users to export into Excel, which when exported will be formatted (cell width, cell height, bold type for headings, etc.) to mirror a prior excel spreadsheet that is being used. This is necessary so this data can then be pasted into a specific spreadsheet that already exists.

    I did not wish to imply that using an embedded excel spreadsheet would magically format it, but have had problems with simply exporting a table or query to excel which ended up with cells that were not of the height and width that I want, and was wondering what if any advantage there was to an embedded spreadsheet.

    After pondering this further, would creating a subdatasheet with my query results, and then exporting to excel provide better results? I have not had time to work with this just yet, but wondered if anyone had any experience with exporting data to excel.

    It is not my choice to use excel and would prefer not to, but very few in the company work with access and most of the cmopany data is shipped around in one form or another in excel.

    Thanks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Embedded Excel or Export Question (Access 2K)

    If very few people work with Access, you should definitely NOT use a spreadsheet embedded in an Access form. I would do one of two things:
    <UL><LI>Export the data from Access, then use Automation to open Excel from Access, and use Excel VBA to format the exported worksheet the way you want. Search for Excel.Application in this forum for examples of automating Excel from Access.
    <LI>Import the data in Excel using code. The CopyFromRecordset method retrieves data from an ADO or DAO recordset; you can then format the result. Look up CopyFromRecordset in the Excel VBA help for more info and an example.[/list]HTH

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Embedded Excel or Export Question (Access 2K)

    Unfortunately, when you export data from Access to Excel, no real formatting goes with it. As Hans suggests, Automation with Access driving Excel, or extracting the Access data from Excel are about the only good options. If you embedded the Excel workbook as an OLE object in Access, you would still need Access to get at it, and you wouldn't be able to manipulate the data inside the workbook in Access either. Also, I wouldn't expect subdatasheets to help at all, and they can be a serious performance drag in Access. As long as people need to be able to manipulate data in Excel, I think your alternatives are limited. If they didn't need to manipulate data, just look at it, you might consider using a snapshot report - there is a free viewer that can be downloaded and installed by anyone with Office.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Embedded Excel or Export Question (Access 2K)

    Thanks, will start researching option #1.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Embedded Excel or Export Question (Access 2K)

    If Automation is a new subject for you, you might find our Automation tutorial useful - it doesn't deal directly with Excel, but has links to some MSKB articles on how to automate Excel from Access.
    Wendell

Posting Permissions

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