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

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.

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.

possible to attach your code/database? might easy to debug.

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

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"

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?

I attach an example.
test1.mdb

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

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.

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

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.

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".

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

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

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 ?

> 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.

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

I've never done this before.  How do I award points.  I would like to award something to vadimrapp1 if I can.

jaustinr

Thanks for the kind words; you can "accept" the answer that resolved your problem. More information at https://www.experts-exchange.com/help.jsp#hs=29&hi=407

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.

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.