Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2012
    Posts
    72

    Access to Excel - Updating an already open workbook.

    Hello again!

    I have a module containing some code that opens an excel template to a specific workbook and inserts data into specific cells from individual reports. The nature of the excel template is that certain areas get updated frequently while others get updated occasionally, thus they are never updated at the same time.

    What I'm looking for, and haven't found it yet amongst the many access>excel vba codes, is a way to keep my excel file open, and update it from different reports without having to close the excel file.

    For example. Currently if I'm on rpt_CabinTemps and click my button that says Merge Data, it will open the excel file, and the sheet, and populate the fields I want it to. But if I were to goto rpt_Airway and click the Merge Data button, it would open a new instance of the same excel sheet.

    My code:

    Code:
    Dim xlApp As Object
    Dim xlSht As Object
    Dim xlWkbk As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlWkbk = xlApp.Workbooks.Open("C:\Database\Report.xls")
    Set xlSht = xlWkbk.Sheets(1)
    
    xlApp.Sheets("FY13").Select
    
    xlApp.Visible = True
    
    If Forms!DataMerge!cboMonth = "January" And Forms!DataMerge!cboYear = "2013" And _
    Forms!DataMerge!lblAudit.Caption = "Cabin Temperatures" Then
    
    With xlSht
    
    xlApp.Range("H55").Value = Reports!rpt_CabinTemps!txtIniDocCalc
    xlApp.Range("H56").Value = Reports!rpt_CabinTemps!txtTipDocCalc
    
    End With
    End If
    
    Set xlApp = Nothing
    Set xlWkbk = Nothing
    Set xlSht = Nothing
    
    End Sub
    I've thought about having it save and close the template but it seems redundant when I need to transfer data from multiple reports.

    Any thoughts?
    Last edited by Pis7ftw; 02-03-13 at 02:01. Reason: Forgot to close my parenthesis!
    Version: Access 2010

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,446
    stop using Excel as a means of storing data
    instead use it as means of analysing data (if you must)


    if you do use Excel as an analysis tool write an Excel procedure which pulls the most recent data from your Access datastore

    you can then call that function from a dedicated button, or when the app opens or even n a timer (say it refreshes the data every n minutes), or when they do something else call that function before running a macro and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2012
    Posts
    72
    Believe me I would love to stop. It's a pretty big pain. Access stores the data, the Excel template is just a format that's required by the customer.

    As for the function you speak of, I like it, though I don't think it's feasible for this project. It's much more practical to manually merge the data at the push of a button. So back to my original question. How do I update an excel workbook that's already open?
    Version: Access 2010

  4. #4
    Join Date
    Mar 2009
    Posts
    5,267
    You can handle the Workbook as long as you keep a reference to it once it has been open, and that Excel remains open too. Use a member variable, a public variable (eek!) or any other mean to store that reference,so that it can be accessed when you need it. I would probably store the reference to the Workbook or even to the whole Excel application object in the public instance of a class module or in a public Collection object.
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,446
    [quote]It's much more practical to manually merge the data at the push of a button.[quote]


    agree wholeheartedly, but where is the best place for the button?

    How do I update an excel workbook that's already open?
    My understanding of Excel isn't as good as it should be, but providing you never close the workbooks (ie set the object references to nothing and keep those objects current then you should be able to do so.

    The problem is when your users attempt to open the workbook and consume the data and may cause lockouts where you cannot update the workbooks as its already open. hence why I'd strongly recommend that you pull the data into the Excel workbook from the the4 database as required and not push the data from Access. IE see its as code running in Excel drawing data from the Access db, not code in Access pushing data to a spreadsheet.

    why
    well you have no lockouts because the user has a workbook open.
    you code only does stuff when the data is required, its guaranteed to be the most up to date, and it only runs when the data is required.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2012
    Posts
    72
    So what you're saying, Healdem, is create a module in Excel to grab data from an Access report? I'm not familiar with Excel's inner workings but I can look it up.

    As for the button I created a simple modal form called DataMerge that contains the above code. Simply select the month and fiscal year and the data is transferred to the appropriate cells in the Excel workbook. It's a lot of information and a pretty big section of code. I've been trying to think of an easier way to go about it, and have a few ideas, but nothing solid yet.

    Like I said, the solution I have works. The issue I'm having is trying to update an open instance rather than having to save and close each time I want to update an area on the workbook.
    Version: Access 2010

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,446
    like I said
    push the process into the Excel model and it ceases to be a problem in Access.

    or maintain the Excel Objects in your Access process, but that may mean you have to use the wildly deprecated global variables

    although the details of VBA are different between Excel and Access its still VBA
    most of what you have got already in your Access process will be just as valid in an Excel context

    to me the real advantage is that you no longer hjave to worry about whether an Esxcel workbooks is open, or is oipened by soe,mone else or whatever other state it may or may not be.

    the user opens their workbook, presses a button and it grabs the latest dataset from the Access application.
    it means that there is no need for a copy of Access to be open, no need for a licenced copy of Access
    the downsides
    well you have to create your process in Excel
    there is a downside, which is if you want this process to run, say overnight, then you'd there may well be an argument to continue to use Access
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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