Run Excel Function From Access VBA

Question

I have an excel command button which simply copies the contents of one sheet to another. I am trying to run this command from within access. I do not get an error, however when I run the code from access, the information is not copied in excel. The code is below. Any ideas?

'

Private Sub Command92_Click()
 Dim x2 As Object
 Dim GetDBPath As String
 GetDBPath = CurrentProject.Path & "\" & "Reports.xlsm"

Set x2 = CreateObject("Excel.Application")

x2.Workbooks.Open (GetDBPath)

x2.Visible = True

x2.Run CommandButton1_Click
x2.ActiveWorkbook.Close (True)

x2.Quit

Set x2 = Nothing


End Sub'

Rory · Accepted Answer · 2014-11-18 15:18:10Z

1

Run takes the name of a routine to run, as a String. You will also need to include the code name of the worksheet in the call:

x2.AutomationSecurity = msoAutomationSecurityLow
x2.Run "Sheet1.CommandButton1_Click"

for example.

edited Nov 18, 2014 at 15:18

answered Nov 18, 2014 at 14:36

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Navron Over a year ago

After adding the quotes, a Run-time error appears saying the macro cannot be ran. Is that x2.run will only run macros and not functions/subs?

Navron Over a year ago

I did. I however se where 'enable all macros is disabled' and greyed out, so Im working on fixing that. Any ideas though? Excel 2013

Navron Over a year ago

Thanks. The following error is reported: Run-time error '9' Subscript out of range

Navron Over a year ago

I wouldn't say its a macro, its a sub I created that is ran from a command button. However, it runs ok in excel itself

Rory Over a year ago

Well I can't see how the code above could produce an error 9, so it must be the macro in Excel.

Collectives™ on Stack Overflow

Run Excel Function From Access VBA

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related