Run time error when running an Excel Macro within MS Access

Question

This is my first post and I am really new to programming.

I am currently stuck on a line of code which is halting when the Graph is building. Basically I have a Database / Access and I have a function where I click the button and it opens a file from a specific location. It then Opens Excel and starts to run my code and then halts.

Below is a snippet, It gets stuck on the below line

ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "M³ Per Month"

Range("A2:M6").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Range("LCLSPENDGRAPH!$A$2:$M$6")
ActiveChart.SetElement (msoElementDataTableShow)
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "M³ Per Month"
Selection.Format.TextFrame2.TextRange.Characters.Text = "M³ Per Month"
With Selection.Format.TextFrame2.TextRange.Characters(1, 11).ParagraphFormat
    .TextDirection = msoTextDirectionLeftToRight
    .Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 11).Font
    .BaselineOffset = 0
    .Bold = msoTrue
    .NameComplexScript = "+mn-cs"
    .NameFarEast = "+mn-ea"
    .Fill.Visible = msoTrue
    .Fill.ForeColor.RGB = RGB(0, 0, 0)
    .Fill.Transparency = 0
    .Fill.Solid
    .Size = 10
    .Italic = msoFalse
    .Kerning = 12
    .Name = "+mn-lt"
    .UnderlineStyle = msoNoUnderline
    .Strike = msoNoStrike

`

Any suggestions would be appreciated.

Kind regards JDogg

Also forgot to mention that the Macro / code works fine just in Excel, but when I have embedded it into Access it doesn't work? — JDogg, Jul 06 '16 at 04:30
When automating excel from Access you need to get a reference to the Excel Application object, and use that with any reference to an Excel object. Also, add a reference to the excel object model in your Access VB project. — Tim Williams, Jul 06 '16 at 04:32
Thanks Tim, I have added the Excel object model in Access Vb. But I suspect something isn't quite right about the code. — JDogg, Jul 06 '16 at 04:41
There is no `Range` object in Access - that needs to be qualified with an Excel Application object reference: like `objExcel.ActiveSheet.Range(...)` for example. Same for any other references to objects "owned" by Excel and not by Access. http://stackoverflow.com/questions/5729195/how-to-refer-to-excel-objects-in-access-vba — Tim Williams, Jul 06 '16 at 05:08

score 0 · Answer 1 · answered Jul 10 '16 at 04:38

If you want to control Excel from Access, you basically have 2 options. Please try the 2 scripts below and post back with additional questions...

‘EARLY BINDING
Option Compare Database
Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to Declare them
' Here and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()

Dim strFile As String

strFile = "C:\Users\Excel\Desktop\YourExcelFile.xls"

' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True

'Opens up the Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)

'Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
'Set xlWS = xlWB("Sheet2")

With xlWS ' You are now working with the Named file and the named worksheet


End With

'Do Close and Cleanup
End Sub


 
‘LATE BINDING
Sub ControlExcelFromAccess()

' No reference to a type library is needed to use late binding.
' As long as the object supports IDispatch, the method can
' be dynamically located and invoked at run-time.

' Declare the object as a late-bound object
  Dim oExcel As Object
  Dim strFile As String

  strFile = "C:\Users\Excel\Desktop\YourExcelFile.xls"

  Set oExcel = CreateObject("Excel.Application")

' The Visible property is called via IDispatch
  oExcel.Visible = True

  Set xlWB = oExcel.Workbooks.Open(strFile)

'Call Ron's code here . . .

Set oExcel = Nothing

End Sub

Run time error when running an Excel Macro within MS Access

1 Answers1