Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refreshing an excel sheet connected to Access (Excel 2000)

    Okay, so I may be a little out of my league...but, in any case here it goes.
    I have an Access database connected via ODBC connection to a SQL database. I created an export of one of the querys but I would like to refresh this excel spreadsheet everytime I open the sheet in Excel. But, I don't want to have to open Access and export the file over and over. Is there code that can do this in VB? <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    Any info would be helpful. <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

    Thank you in advance.

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

    Re: Refreshing an excel sheet connected to Access (Excel 2000)

    The very short answer is yes - you could create a VBA routine that would import the data directly from the SQL Server database. But you could also use the MS Query tool to actually view the live data in Excel also - it would require that you create an ODBC driver for each person viewing the workbook, but it wouldn't require writing any VBA. To do the VBA you would need to know either DAO or ADO and have a SQL Server login procedure defined in your code. Hope this gives you some alternatives.
    Wendell

  3. #3
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing an excel sheet connected to Access (Excel 2000)

    I have in the past created MS Queries by connecting via ODBC to SQL. But, I find myself creating a query in Access, then having to create the query again in MS Query. I would prefer to create the query in one centralized place, (that being in Access) then exporting or linking it elsewhere. My two questions are:

    Can I export or link an Access Query to a MS Query?, so that I am not creating it twice.
    and/or
    Can I export the Access query to excel and create a procedure that will refresh the excel spreadsheet everytime I open the sheet?

    Thank you for your help.

    <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing an excel sheet connected to Access

    One of the Access gurus will have far more experience on this than me, but I think your best bet would be to have Excel use VBA automation to open Access - which would then run the query on the SQL Server database and export the data back into Excel.

    You could have the routine run from an AutoOpen() procedure in a standard module in an Excel workbook. (I did some work on something similar some time back. The thread, however, on this is too old to be found in the current Lounge and I no longer have access to the "live model".)

    HTH
    Grüße

  5. #5
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing an excel sheet connected to Access

    Thank you for your input. I will certainly give it a try.

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

    Re: Refreshing an excel sheet connected to Access (Excel 2000)

    <!profile=unkamunka>unkamunka<!/profile> is correct in that you can use Automation to do what you want to do - BUT - it requires that you know the Access object model pretty well, and it presumes that nothing will ever be changed in the object names used in Access. In addition if you are working with a parameter query, you will have to rewrite that so you provide the value of the parameter as a part of the SQL string for the query that you execute. It's a fairly complex development! You can indeed link to an Access query from MS Query (at least in 2002), which would be an alternative to linking to SQL Server directly. However if it is a parameter query, you will probably see some complications, as you would need to respond to the Access prompt. If you do a refresh of the data from query each time you open the spreadsheet, that would make your data pretty current.

    Are you the end user, or do you have others doing this, and if so, do they have Access installed? Also what is you level of comfort with VBA and Automation?
    Wendell

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing an excel sheet connected to Access

    Wendell, for the record, I actually started into the way I proposed because of difficulties running parameter queries into SQL Server from Excel when using MS Query.
    Grüße

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Refreshing an excel sheet connected to Access (Excel 2000)

    <hr>Can I export or link an Access Query to a MS Query?, so that I am not creating it twice.<hr>
    Yes, you can. The way that I do it is to use Excel's Data | Get External Data | New Database Query; select MS Access Database; browse for your database; select the query you want to pull into Excel (all fields or only selected fields). Once the data has been pulled into Excel, you can refresh the data by selecting the first data cell in the pulled range and use Data | <font color=red>!</font color=red> Refresh Data to requery your Access database. If you turn on the Macro Recorder before you refresh the data you can turn the refresh into a simple button-run macro, or copy the code to the Workbook_Open() procedure.

    My personal preference is to not have these queries refresh automatically, except in limited situations. This way, you can open the Excel workbook and still have the results from the previous refresh (should you need them again), and the new data is only a click away. If you need a reminder that the data you are viewing is not current data, set up an area on the worksheet for "Data last updated", and have the data refresh macro insert the date. You can then use conditional formatting to compare the date in the cell to Today() and change the displayed font/background colours, or somewhere in the vicinity use =IF(LastUpdated<Today(),"Caution - Old Data",""). The day values can be modified so as to compare date and time instead of date only, so you can adjust the sensitivity of your 'Old Data' warnings.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing an excel sheet connected to Access (Excel 2000)

    AFAIK you can use msquery to execute a query built in Access. Just use the query in Access as the "Table" to use in msQuery. Not sure if this will work for all query types though.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refreshing an excel sheet connected to Access (Excel 2000)

    Thank you David! Excellent ideas <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I'll give it a try.

Posting Permissions

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