How to use Access VBA to refresh a link to an Excel worksheet

I have an Access (2007) table that I update with new parameters using SQL DoCmd.  This table is linked to an Excel(2007) worksheet from which a pivot table (separate worksheet) and graph (separate worksheet) are created.  The Excel graph has been linked back to an Access OLEUnbound object frame.  I have been unable to automatically update the Excel worksheet link from Access VBA, after trying several approaches gleaned from the internet.  I can open Excel and update manually, but all Access VBA attempts have failed.  I consider myself a novice+ in the world of VBA.

Thanks in advance,

jaustinr
jaustinrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pdd1lanCommented:
have you tried to re-link to the excel file?  my suggest is that you might need to delete the link, and re-link to excel file.
jaustinrAuthor Commented:
I'll give that a try, but since I can do a refresh manually and successfully from the worksheet, I feel the link is OK.  Just can't get it to update/refresh from any Access VBA code.
pdd1lanCommented:
possible to attach your code/database? might easy to debug.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

jaustinrAuthor Commented:
The overall code is several pages and would be difficult to include all of it.  I will include some of the VBA code I've tried at the end of this comment, however.  When I look at the Access table created by the SQL, the Excel sheet automatically updated from that table, the associated Excel Pivot Table connected to the Excel sheet, and the resulting Excel graph they all represent the correct data.  When I load the Access form for the first time, the Access control frame on that form properly updates to the linked Excel graph.  This did not work until I set the OLEUnbound Access form control to automatic update.  However, when I select a new set of paratmenters that go through the entire process noted above, the linkded graph does not update (the graph is correct in Excel).  I was hoping I could force that Excel graph link to the Access from control to update through VBA.  I know the link works because of the correct update occurs on an initial load of the form.  I have tried closing the form and re-loading, but that does not do the trick either.  I need VBA code that does whatever the automatic form load does for updating links.  Following are some of the VBA code lines I've tried (individually and in combination) and have not provided this capability:

Forms!Page_04_Form.Requery
Forms!Page_04_Form!OLEUnbound139.Requery
DoCmd.Close acForm,"Page_04_Form"
DoCmd.OpenForm "Page_04_Form"
Forms!Page_04_Form.Repaint
Forms!Page_04_Form!OLEUnbound139.Repaint
DoCmd.RepaintObject acForm, "Page_04_Form"
DoCmd.Requery "Forms!Page_04_Form"
  For Each tdf In db.TabldDefs
        If Len(tdf.connect) > 0 Then
            tdf.RefreshLink
        End If
   Next

Thanks for any suggestions

jaustinr
pdd1lanCommented:
does you try to delete excel link first before you re-link it?  

'delete the excel link
Currentdb.Execute "Drop table excel_link_table_name "

're-link
 DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "excel_link_table_name", "d:\temp\exel_file.xls", , "excel_link_table_name"




jaustinrAuthor Commented:
Thank you for your response. I haven't tried to delete or re-establish the link with the commands you provided.   How do I find the Excel link table name? When I look at Workbook Connections, it does show the original Access table link but don't see any listing for the Access form (or the OLEUnbound control). Also, in your path name, where would I find the .exel_.xls file path on my computer?
pdd1lanCommented:
I attach an example.
test1.mdb
pdd1lanCommented:
Option Compare Database


Private Sub Drop_Table(DB As Database, Table_Name As String)
    'drop table
   
    On Error Resume Next
   
    DB.Execute "Drop Table [" & Table_Name & "];"

End Sub


Private Sub cmdRelink_Click()
   
    'assume Data is a table that link to excel called test.xls.
    ' test.xls saves in D:\Temp\ location.
   
     'assume that you have only one excel link.
     'if you have more than one file, you might need to loop through to find all of them
     
     'just make yourself of copy of your database before you try it out,
     'so you don't loose any data just in case.
     
     
     'remove table that link to excel
     Drop_Table CurrentDb, "Data"
     
     're-link the excel file
     DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "data", "D:\temp\test.xls"
     
End Sub
jaustinrAuthor Commented:
How does this work when the link is to an Excel graph on a separate Excel sheet? My links between Access tables and Excel sheets are working and I've been able to update automatically with VBA.  It is the linked Excel Graph to an Excel Form/OLEUnbound control that won't link through the established Excel/Access link using VBA.

Thanks for helping me work this issue.
pdd1lanCommented:
jaustinr, I haven't worked much OLEUnbound control very much.  sorry for too much help.  however, I found some articles might relating to your subject and hope it helps some.

http://www.ozgrid.com/forum/showthread.php?t=57688&page=1
http://thedailyreviewer.com/office/view/excel-chart-data-source-from-an-access-query-11168178

http://www.officekb.com/Uwe/Forum.aspx/excel-chart/11237/excel-chart-with-data-source-from-an-access-query

jaustinrAuthor Commented:
I reviewed these links and still do do not have an answer to my issue.  I probablyl made the question to complex.  Basically, I need Access VBA code to force a refresh to a previously established link between an Excel chart and an access OLEUnbound frame on an Access form.  The link updates successfully the first time I run the form with code in the load sub.  However, I cannot get the link to refresh after that.
Vadim RappCommented:
According to http://support.microsoft.com/kb/904953 , you can't edit linked Excel data from Access, Microsoft has disabled this functionality "because of the legal issues".
jaustinrAuthor Commented:
Well I'll be.  I'll look at the link to see what it says.  I was wondering if I was going crazy as I tried just about every method I could find.  However,  I still don't understand how the Access form updates correctly from the Excel/Access link when I load the Access form using VBA.  It is after this initial load that I can't get it to update.  

Thanks for the link.

jaustinr
puppydogbuddyCommented:

Many times the problem is because the commands used are correct, but they are executed in the wrong sequence or against the wrong object.
 
have you tried setting focus on the OleUnbound control (the chart object) after doing a requery or update?

Me.OLEUnbound139.Requery
Me.OLEUnbound139.SetFocus

if the requery does not work,try the update command
Me.OLEUnbound139.Update
Me.OLEUnbound139.SetFocus
Robberbaron (robr)Commented:
if you set macro recoreder on and then do the refresh manually, is there any commands actually recorded.  This can give an insight into what objects XL is trying to update.   ie is it a tabledef ?
Vadim RappCommented:
> I still don't understand how the Access form updates correctly from the Excel/Access link

Access refreshes the data that you see from the source; i.e. it does re-read the data that is "originally" stored in Excel; this is possible in VBA as well as manually, no problem. I.e. if you go to Excel, update the spreadsheet, then you can go back to Access, requery the linked table (manually, or by VBA code), and you will see new data. What you can't do is change the data in Access and have Access send the update to Excel spreadsheet.
jaustinrAuthor Commented:
Thanks for the various inputs.  I will be sure to try some of the suggestions in the next couple of days.  However, in response to vadimrapp1, as I stated in my original post.  I use an Access db that I query using SQL to create a new dataset.  Excel picks this dataset up correctly, correctly established a new Pivot Table, and correctly updates a graph generated from the Pivot Table.  The graph in Excel correctly links to the Access OLEUnbound frame on a form when I load the form is loaded the first time I open Access (all of these steps are automatic/Access VBA).  When I try to requery the link with a new data set it goes through all the same steps noted above correctly, including the Excel chart representing the new dataset.  But back to my Access VBA code, I have been unable to force a new requery of the chart link.  The link worked the on the first preload of the Access form.  The various requery approaches I used are shown in an earlier post.  All steps/results in the process update with a second dataset except the Excel/Access chart link.  I tried deleting and relinking as well as other approaches.

I don't believe I've received any error messages with any of the approaches I've tried.  Just no update.

Thanks for the inputs.

jaustinr
Vadim RappCommented:
I'm still missing where the original data is, in Access, or in Excel. Anyways, that's how you manually update unbound OLE frame in Access:

MyOLEUnboundControl.Action = acOLEUpdate


The control must be Enabled and not Locked; you can set those properties in code before Action, or in Design mode.

Other notes:
1. You can also set property Update Options to Automatic, then the frame will be updated automatically, with the interval set in Options/Advanced, with no need in any code.

2. you can view pivot chart of tables and other objects directly in Access, it's one of the items under View menu.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jaustinrAuthor Commented:
Unbelievable!  vadimrapp1 - you have solved the problem I've been working on for over 4 months.  In all the references and potential solutions, I never saw that solution.  For sure, my subscription to EE has paid off.  

Thanks again - I was almost ready to give up and scrap the entire program.

FYI - your question regarding where the location of the original data - Access.  I use Excel because of its superior (over Access) Pivot Table and the ability to automatically use the same Excel graph for a new set of Pivot Table results.  It is pretty complicated (and probably could be done simplier) but the overall set of MS tools permits me to do some data analysis and presentation the is unique for my application area.
jaustinrAuthor Commented:
I've never done this before.  How do I award points.  I would like to award something to vadimrapp1 if I can.

jaustinr
Vadim RappCommented:
Thanks for the kind words; you can "accept" the answer that resolved your problem. More information at http://www.experts-exchange.com/help.jsp#hs=29&hi=407
Vadim RappCommented:
jaustinr , you can accept the solution yourself, if this is all you want to do, there's no need to request attention from moderator. Somewhere under my comment 34922029 that resolved the problem, there must be a link "Accept this as solution", it will do the job.
jaustinrAuthor Commented:
I was looking for a single line of code to force a link update between an Excel chart and an OLEUnboud control on an Access form (using Access VBA).  This was the exact solution I required.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.