activate macro for excel in access

  • Thread starter Thread starter thread
  • Start date Start date
T

thread

hi all,
i built apllication in access that execute excel file and making there
proccessing on the culomn.
i add a figure that implementing a commandbutton on the excel file the
problem is that i dont know how to use a function in access that will
react to the commandbutton that i created(via vba of course) in the
excel.

the executer:access
the preformer:excel
 
Hi Thread,

Rather than thinking "click the command button", you will
launch the code that the command button runs

Here is code for running a sub from another workbook -- if
the procedure is in the workbook that is open, make the
appropriate substitutions :)

'~~~~~~~~~~~~~~~~

'--------------- dimension variables
'this is for early binding
'if you need to do late binding,
'do this for developing
'but compile and run with late binding
'Dim xlApp As Excel.Application
'Dim xlWB as Excel.Workbook, xlWBprg as Excel.Workbook

'this is for late binding
Dim xlApp As Object
Dim xlWB as Object, xlWBprg as Object

Dim mExcelFile as string, mPath as string
Dim booCloseExcel as boolean

'--------------- assign variables
booCloseExcel = false
'or whatever is the path
mPath = CurrentProject.Path & "\"
mExcelFile = "Whatever.xls"

'--------------- set Excel object
'if Excel is already open, use that instance
booCloseExcel = false
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo error_handler

'What did we find?...
If TypeName(xlApp) = "Nothing" Then
'Excel was not open -- create a new instance
Set xlApp = CreateObject("Excel.Application")
booCloseExcel = true
End If

'comment out after your program is done (if you want to!)
xlApp.Visible = True

'---------------
' ...more statements

'--------------- open workbook with the code
'if applicable
set xlWBprg = xlApp.Workbooks.Open( _
mPath & "PROGRAMS.XLS")

'--------------- open workbook to run code on
set xlWB = xlApp.Workbooks.Open( _
mExcelFile)

'--------------- run Sub in Programs Workbook
xlApp.Run "PROGRAMS.XLS!ModuleName.SubName"

'--------------- save Workbook
xlWB.save

'--------------- close Workbooks
xlWb.Close False
xlWbprg.Close False

'--------------- close Excel

'xlApp.Visible = False
If booCloseExcel = true then
xlApp.quit
end if

'--------------- release object variables
set xlWB = nothing
set xlWBprg = nothing
set xlApp = nothing
'~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
there is a function called OnAction,
do you think there is an option to use it too?
i know that its easy to implement in excel but little bit more
problematic in access as an executer

strive4peace כתב:
 
Hi Halo,

the OnAction function returns or sets the name of a macro
that’s run when the specified object is clicked -- you could
use that to specify the name of the sub to Access if you
like, but it is the actual sub you need to call, not the
command button. To me, it seems easiest to just specify the
name of the sub in the Access code


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
hi crystal,
thank you for your help,
my question is as follow:
as i see from the code that you sent me the activation of this
commandbutton is comming from the excel sub function,im not sure but
correct me if i wrong.
my issue is how to active the commandbutton directly from sub function
that i'm building in access
 
do you this there is a way to base the action of the commandbutton
depending on a function from access?
 
do you think maybe there is a way to direct the commandbutton in the
excel to activate event from access?
 
here is code that runs an excel macro from within access:

Set xlApp1 = CreateObject("Excel.application")
xlApp1.Application.Visible = True
Set xlWb2 = xlApp1.Workbooks.Open("path\BOM2.xls") ' macro was
here
Set xlWb1 = xlApp1.Workbooks.Open("path\" & filename & ".xls")
'sheet to run in
Set xlWs = xlWb2.Worksheets("Sheet1") ' Which tab to sit on
xlApp1.UserControl = True
xlWs.Activate
xlApp1.Run "BOM2!macroname"
xlApp1.Save
xlApp1.Quit


You can get the idea from the above.

Ron
 
hi
i dont want to activate a macro in excel by access becaouse then i need
to build a macro to appropriate excel workbook and its something that i
dont want especially when i know that its a file that shouldnt have any
macros but i would like to build a temporary object(for ex.the
commandbutton) inside of the excel then when the person finished to do
is part in the file,the access will make its automate.
i did built a command button via access inside the workbook but from
there im stucked becaouse i dont know how to make the automation in
access based on a click procedure from the excel(macro in the excel is
not something that i would like to take into consideration of course
only function and procedure from the access will take place)

Ron2006 כתב:
 
Two considerations.

1) This one is just information that you should know. A macro that is
executed does not have to be in the spreadsheet that contains the data.
In fact the example code I showed, is executing a macro that is in the
BOM2spreadsheet and the data is in the second spreadsheet. The process
opens BOTH spreadsheets and then does its thing. So the macro could be
something that the user never sees or has real access to in that sense.
It could reside in an entirely different directory yet everything would
work.

Now for your process as I understand it.
2) If the process you have is going to interface with the SAME
spreadsheet that is open AND some process in Access, I am not sure that
you will really be able to do it. This is because the automation will
require Access to open the spreadsheet, but you are sitting in it and
so access will NOT be able to open it. This will be true IF you
complete the original process that access did, and it closes the
spreadsheet (which the user then opens in order to do their part and
press this button.) See the next though as a different approach and
possibly a way around that.

3) You could break it up into a multi step process that puts data in
the spreadsheet..... then makes the spreadsheet visible and open and
asks the user to do their part. (but in the mean time stop doing what
ever else it was going to do . After the user has finished they would
respond to a popup message box that you would have opened in access.
This would then allow access to continue with its steps. The trick here
is that the user MUST either always close the spreadsheet or always
open it and your next steps would re-open the spreadsheet (or not)
depending on the way they are instructed to go.

Ron
 
hi ron,
refer to your thrid option,
this is what did accually,there is no way to use procedure from access
implemented on built object in excel,the most i can built it but i
cannot activate it from access
so the solution of use some commandbutton from access for continue of
automation in excel is the most reasonable

Ron2006 כתב:
 
Hi Thread,

Since your Excel file will already be open, you can use the
GetObject form of automation to get to it from Access. If
you are still working on this, try it an post back with your
code and the code that your command button is running

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
Back
Top