+ Reply to Thread
Results 1 to 4 of 4

Macro to run Ms Query and import the data into excel

  1. #1
    Registered User
    Join Date
    03-29-2008
    Posts
    1

    Macro to run Ms Query and import the data into excel

    Hello,

    I run multiple Queries in MS access and then export the output individually to seperate excel work books for further analysis.

    I need help in automating this process. Is it possible to have all this done by just clicking a botton.

    I want the process to

    1. run all the ms access queries
    2. Import all the data in excel. Output of each query is imported in a seperate worksheet in the same workbook.
    3. Save the workbook in a specified location. Since this is a periodic excercise i want the file names to be named after the date on which this process is run.

    Is it possible to write a macro which can do all the above.

    I have a very basic undertanding of macros.

    Thanks in advance.

    A.S.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello aryans1in,

    Welcome to the Forum!

    Since Access queries can be translated directly into SQL, transferring data back and forth between Access and Excel is not to difficult. Excel requires using ADO to setup the SQL server connection and retrieve records, something you don't have to do with Access. Once the data is in a workbook, the remaining programming can be done in Excel VBA, and handled locally.

    Sincerely,
    Leith Ross

  3. #3
    Registered User
    Join Date
    05-26-2006
    Location
    Ohio
    Posts
    17

    Question

    A.S.
    Yes you can have a button run each Access query one after another then call you Excel wookbook.
    You could have an auto start macro in Excel that will refresh each sheet's query data. It can then close Excel and save it with a date specific name.
    When you say you want the file to have a date specific name, do mean in Access or Excel?

    I can come up with samples later today if you want.

    ReBoot

  4. #4
    Registered User
    Join Date
    05-26-2006
    Location
    Ohio
    Posts
    17

    This may be what you are looking for.

    I was going to attach a sample database and Excel file, but they are over 100kb even zipped. Seems to be the limit here.
    If you email me at [email protected] I will send them to you.
    ReBoot

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1