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
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)
Here is the code that calls the above procedure from 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