Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

activate macro for excel in access

Discussion in 'Information Technology' started by thread, Jul 28, 2006.

  1. thread

    thread
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Microcosm and Macrocosm Gurmat Vichaar Mar 20, 2009
    Adobe Unruffled by Macromedia Restatement (AP) Interfaith Dialogues May 4, 2005
    Macromedia Flash animation and designing. Interfaith Dialogues Oct 21, 2004

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    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

    *

    thread wrote:
    > 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
    >
     
  4. thread

    thread
    Expand Collapse
    Guest

    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 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
    >
    > *
    >
    > thread wrote:
    > > 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
    > >
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    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

    *

    thread wrote:
    > 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 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
    >>
    >> *
    >>
    >>thread wrote:
    >>
    >>>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
    >>>

    >
    >
     
  6. thread

    thread
    Expand Collapse
    Guest

    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
    strive4peace wrote:
    > 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
    >
    > *
    >
    > thread wrote:
    > > 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 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
    > >>
    > >> *
    > >>
    > >>thread wrote:
    > >>
    > >>>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
    > >>>

    > >
    > >
     
  7. thread

    thread
    Expand Collapse
    Guest

    do you this there is a way to base the action of the commandbutton
    depending on a function from access?

    thread wrote:
    > 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
    > strive4peace wrote:
    > > 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
    > >
    > > *
    > >
    > > thread wrote:
    > > > 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 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
    > > >>
    > > >> *
    > > >>
    > > >>thread wrote:
    > > >>
    > > >>>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
    > > >>>
    > > >
    > > >
     
  8. thread

    thread
    Expand Collapse
    Guest

    do you think maybe there is a way to direct the commandbutton in the
    excel to activate event from access?

    thread wrote:
    > do you this there is a way to base the action of the commandbutton
    > depending on a function from access?
    >
    > thread wrote:
    > > 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
    > > strive4peace wrote:
    > > > 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
    > > >
    > > > *
    > > >
    > > > thread wrote:
    > > > > 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 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
    > > > >>
    > > > >> *
    > > > >>
    > > > >>thread wrote:
    > > > >>
    > > > >>>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 filethe
    > > > >>>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
    > > > >>>
    > > > >
    > > > >
     
  9. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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
     
  10. thread

    thread
    Expand Collapse
    Guest

    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 כתב:
    > 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
     
  11. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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
     
  12. thread

    thread
    Expand Collapse
    Guest

    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 כתב:
    > 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
     
  13. strive4peace

    strive4peace
    Expand Collapse
    Guest

    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

    *

    thread wrote:
    > 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 כתב:
    >
    >>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

    >
    >
     

Share This Page