If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Access to Excel - Updating an already open workbook.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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?
__________________
Version: Access 2010

Last edited by Pis7ftw; 02-03-13 at 02:01. Reason: Forgot to close my parenthesis!
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,212
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,176
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!
Reply With Quote
  #5 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,212
[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?

Quote:
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,212
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On