Excel graph in Access not updating (1 Viewer)

leok31

Registered User.
Local time
Today, 06:09
Joined
Feb 25, 2014
Messages
36
I'm trying to have a linked Excel chart in Access form. What I've done so far is create a chart in Excel and Paste Special>>Linked into Access.
I also have code inside Excel that will update chart data, it works fine.
Then I have code in Access that calls the code in Excel to update the data.
The data gets updated fine and the chart in Excel gets updated but the chart in Access only gets updated if I close and open the form again.

Here is the code that will update the Excel Data

Code:
Public Sub Import_VRSS_Graph_Data(strDayType As String, strTimeBand As String, strEntrance As String, Ws As Worksheet)
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "Provider...."

'Now open the connection.
cnPubs.Open strConn

'Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset


With rsPubs
    'Assign the Connection object.
    .ActiveConnection = cnPubs
    'Extract the required records.
    .Open "SELECT ....."
   
 'Copy the records into cell A1 on Sheet1.
    Ws.Range("A2").CopyFromRecordset rsPubs
    'Tidy up
    .Close

End With

cnPubs.Close

Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub

Here is the code that calls the above procedure and passes all the variables (Still in Excel)

Public Sub Update_VRSS_Graphs(strDayType As String, strEntrance As String)

Code:
'**This macro will clear the sheet from the old data, add new data and update the graph
Dim Ws As Worksheet
Dim strTimeBand As String

'Pre Am Peak
Set Ws = Sheets("PreAm")
strTimeBand = "pre am peak"
Ws.Range("A2:c45").ClearContents
Call Import_VRSS_Graph_Data(strDayType, strTimeBand, strEntrance, Ws)

DoEvents

'Am Peak
Set Ws = Sheets("Am")
strTimeBand = "am peak"
Ws.Range("A2:c45").ClearContents
Call Import_VRSS_Graph_Data(strDayType, strTimeBand, strEntrance, Ws)


'InterPeak
Set Ws = Sheets("Inter")
strTimeBand = "interpeak"
Ws.Range("A2:c45").ClearContents
Call Import_VRSS_Graph_Data(strDayType, strTimeBand, strEntrance, Ws)

'Pm Peak
Set Ws = Sheets("Pm")
strTimeBand = "Pm Peak"
Ws.Range("A2:c45").ClearContents
Call Import_VRSS_Graph_Data(strDayType, strTimeBand, strEntrance, Ws)

'Pm Peak
Set Ws = Sheets("PmLate")
strTimeBand = "Pm Late"
Ws.Range("A2:c45").ClearContents
Call Import_VRSS_Graph_Data(strDayType, strTimeBand, strEntrance, Ws)

End Sub


Here is the code that calls the above procedure from Access

Code:
Private Sub bt_update_graphs_Click()

'**This button will open up the excel workbook, clear the data, add new data and update the graph.
'in turn, the graph in Access will also update. The user will not see the excel app open
'************************************************************************************************

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

'open the workbook
Dim xlWb As Excel.Workbook
Set xlWb = xlApp.Workbooks.Open("C:\Documents and Settings\Name\Desktop\Disaggregated Model\Disaggregated Model Excel.xlsm")

'make sure its invisible to the user
xlApp.Visible = False

'provide the Excel Macro with parameters from the Access Form
Dim strDayType As String, strEntrance As String
strDayType = Me.filter_daytype
strEntrance = Me.filter_entrance

'run the macro
xlApp.Run "Update_VRSS_Graphs", strDayType, strEntrance

'close and save changes
xlApp.DisplayAlerts = False
xlWb.Save
xlApp.DisplayAlerts = True

xlWb.Close
xlApp.Quit

Set xlWb = Nothing
Set xlApp = Nothing

End Sub
 

leok31

Registered User.
Local time
Today, 06:09
Joined
Feb 25, 2014
Messages
36
The other thing I notices just now is that without running any code if I copy the excel chart to access and have both opened at the same time, when I make changes to excel chart then access changes immediately.
But if I close Excel but keep Access opened, then I open Excel then they are not linked until I close and open Access. In other words if I open and close Excel only then the changes in Excel do not flow to Access.
Why is this happening?
 

JHB

Have been here a while
Local time
Today, 14:09
Joined
Jun 17, 2012
Messages
7,732
Why are you doing it in the way you do?
Why not only have the graph in MS-Access?
 

Users who are viewing this thread

Top Bottom