Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    43

    Unanswered: Exporting MS Access to Ms Excel with Excel formatting

    Iíve read a lot of different article on the internet about being able to export a table from MS Access to Ms Excel.

    Problem Iím having is that I donít know which one would work best for me, so I thought since Iíve gotten such great advice from this form I will ask.

    I have a query that updates a table in access.
    What I would like to have happen in the export from access to excel

    Currently I have a number of queries that updates number of table daily.
    Then I manual export the tables to a excel files
    Then I spend 15 -20 minutes formatting the excel fine. ďI format all the table to excel files that same way.
    Once this is done I email it out in excel format to different managers that need to make commits and then email the spread sheet back to me then I import the spread sheets and record there comments.

    If at all possible I would like all the files to import into one spread sheet just into different tabs. I have a bout 4 different group.

    Below are some of the articles Iíve read about exporting from access to excel.

    Looking for any advice example ect

    Export Data To Excel — DatabaseJournal.com
    http://www.vb123.com/toolshed/98docs/excelbe.htm
    Microsoft: Access Modules (VBA Coding) - Access to Excel export with proper formatting
    http://zmey.1977****/Access_To_Excel.htm
    Sending MS Access Data to Excel - Part 2

    Thanks for all your help and advice
    TCB

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the TransferSpreadsheet method does not fit your need you can use Automation to instanciate Excel and do whatever you need. As a guideline:
    Code:
    Function OpenExcelDocument(DocName As String)
    
        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
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Don't the manager have msaccess on there machine

    Cheapskates option would be

    turn on iis build a web page for the managers to login and full out there comments,

    What I did for a hr systems I wrote the hr manages had msaccess on there computer and the other manager just open explorer and the iis new who it was and only showed there jobs to do

    Or
    You could setup a Obdc and link that to the sheet one for each job down side is you have to setup the odbc on each machine not a good idea been there done that

    Or
    Do what you are doing but record a excel macro, then open a new file and play the macro and see what happens
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Nov 2006
    Posts
    43
    Thanks myle, Can you please provide more information on the iis build, web page. This might be a possibility

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    IIS is a Internet

    ie www only inside you company

    if you have windows server then ask you IT guys to turn it on

    or even if you have a spare computer (NOT BEEN USED) windows XP has IIS in it
    it a great way to test you master Picese or *****up

    that what do I have a test computer for test my IIS stuff I right then put them live on the main server.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    an alternative

    If you make a "front end" database, which links to your data (in a separate file) and has a form or two in Access to display the data, you can set that form as "display on Startup", save as an MDE file and ask the managers to install Access Runtime, which is still available for versions up thru 2010.

    Then, you update the data and they can go look.

    oh, and if you do the ODBC route, you can setup the ODBC on your machine, then export the registry key that holds the definition (makes for setting up ODBC on multiple machines really quick and painless). Me personally, I'd use the MDE route.

    Have fun!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

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