Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically transfering data from Acces to Excel (2000)

    Is there any way in Access to automatically transfer some data from specified cells in Access to Excel

    see screenshot for what l mean.

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

    Re: Automatically transfering data from Acces to Excel (2000)

    You can use Data | Get External Data | New Database Query in Excel to import data from Access into an Excel worksheet.
    You can select Data | Refresh Data or click the corresponding button on the External Data toolbar to update the data in Excel.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically transfering data from Acces to E

    I have a few questions:

    1. How do l select the query from the access database?

    2. how can select the data from a specified row within the query?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically transfering data from Acces to E

    Hi Justin

    The method that Hans describes takes you through a wizard. When you select the database you are querying it will take you to a step where it asks you which object you want to get the data from (table or query) once selected you can select the field and then enter the parameter you require. It is similar in look and feel to Access.
    Jerry

  5. #5
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically transfering data from Acces to E

    I tied this but cannot work out how you select the database then select the queries from the database , please can you tell me how l do this, thanks.

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

    Re: Automatically transfering data from Acces to E

    Select Data | Get External Data | New Database Query, then wait until Microsoft Query appears.
    Select MS Access Database and click OK.
    Browse to the correct drive, folder and database, then click OK.
    From there on, it should be pretty obvious.

  7. #7
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically transfering data from Acces to E

    On selecting the database l get an error, see attachment.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically transfering data from Acces to E

    Try removing the autofilters from your excel workbook
    Jerry

  9. #9
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically transfering data from Acces to E

    They need to be there for sorting purposes.

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

    Re: Automatically transfering data from Acces to E

    What does AutoFilter have to do with sorting?

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically transfering data from Acces to E

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I thought that is why you had a database, to do the sorting

    Can't you create your queries in Access and then export to Excel
    Jerry

  12. #12
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically transfering data from Acces to E

    I don't know l was told it has to be removed in order for the "Get External Data" command to work.

  13. #13
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically transfering data from Acces to E

    What l require is data to be pasted from access into Excel automatically.
    Rather than keep having to copy and paste all the time.
    The data is sorted in Access.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically transfering data from Acces to E

    OK

    I think I see.

    1) Start off with a completely new excel workbook
    2) Data | Import External Data | New database Query...

    3) Run through to get the database location etc
    4) Select the query. and finish wizard and ensure the data starts at A$1$

    This will parse all the data from that query into Excel

    5) Save workbook.

    Each time you open this workbook it will update with the data that is "sitting" on the database. In the past when I have used this method I have used the set of data as my raw dat and I have done my calculations etc on another worksheet...or simply copied and pasted the data from that sheet to another so that i can manipulate it.
    Jerry

  15. #15
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically transfering data from Acces to E

    I cannot work out how you select the query from the database, see screenshot.

Page 1 of 2 12 LastLast

Posting Permissions

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