VBA, Opening an excel file through a form/running excel macros through access

Opening an excel file through a form/running excel macros through access

Author	Message
Jonatha #1 / 9	Opening an excel file through a form/running excel macros through access I have a form with a "start excel button" but I cant figure how to then get a particular file open. I tried adding code to the module as I would in VBA in excel but didnt work. Any ideas?? Also, is there a way to run an excel macro from an access form after the excel file is opened? I have a macro which refreshes the links from access into excel.
Wed, 26 Oct 2005 03:47:02 GMT

Ken Snel #2 / 9	Opening an excel file through a form/running excel macros through access Here's some sample code for opening an EXCEL workbook and running a VBA macro that's in the workbook in a public module: Public Sub TestMacroRun() Dim xlx As Object, xlw As Object Set xlx = CreateObject("excel.application") Set xlw = xlx.workbooks.Open("C:\Filename.xls") xlx.Run "Filename.xls!MacroName()" xlw.Close False xlx.Quit Set xlw = Nothing Set xlx = Nothing End Sub -- Ken Snell <MS ACCESS MVP> Quote: > I have a form with a "start excel button" but I cant > figure how to then get a particular file open. I tried > adding code to the module as I would in VBA in excel but > didnt work. Any ideas?? > Also, is there a way to run an excel macro from an > access form after the excel file is opened? I have a > macro which refreshes the links from access into excel.
Wed, 26 Oct 2005 09:10:14 GMT

Jonatha #3 / 9	Opening an excel file through a form/running excel macros through access thans, what about an xls vs a workbook, same thing? Quote: >-----Original Message----- >Here's some sample code for opening an EXCEL workbook and running a VBA >macro that's in the workbook in a public module: >Public Sub TestMacroRun() >Dim xlx As Object, xlw As Object >Set xlx = CreateObject("excel.application") >Set xlw = xlx.workbooks.Open("C:\Filename.xls") >xlx.Run "Filename.xls!MacroName()" >xlw.Close False >xlx.Quit >Set xlw = Nothing >Set xlx = Nothing >End Sub >-- > Ken Snell ><MS ACCESS MVP> >> I have a form with a "start excel button" but I cant >> figure how to then get a particular file open. I tried >> adding code to the module as I would in VBA in excel but >> didnt work. Any ideas?? >> Also, is there a way to run an excel macro from an >> access form after the excel file is opened? I have a >> macro which refreshes the links from access into excel. >.
Wed, 26 Oct 2005 10:53:15 GMT

Ken Snel #4 / 9	Opening an excel file through a form/running excel macros through access Quote: > thans, what about an xls vs a workbook, same thing? An EXCEL workbook normally uses an file extension of ".xls", so, if I understand your question, the answer is "the same thing". -- Ken Snell <MS ACCESS MVP>
Wed, 26 Oct 2005 13:34:10 GMT

Van T. Din #5 / 9	Opening an excel file through a form/running excel macros through access Hi Ken In earlier version(s) of Excel, there were .xls for worksheet files and .xlw for workbook files when workbook was introduced. Later versions merged them both to .xls. Johnathan may be used to the old version(s) of Excel and therefore he distinguished between worksheet and workbook. -- Cheers Van T. Dinh MVP (Access) Quote: > An EXCEL workbook normally uses an file extension of ".xls", so, if I > understand your question, the answer is "the same thing". > -- > Ken Snell > <MS ACCESS MVP>
Wed, 26 Oct 2005 19:09:46 GMT

Ken Snel #6 / 9	Opening an excel file through a form/running excel macros through access Ahhhhhh...I'm showing my short-term experience here. Thanks, I wasn't aware of this EXCEL history. -- Ken Snell <MS ACCESS MVP> Quote: > Hi Ken > In earlier version(s) of Excel, there were .xls for worksheet files and .xlw > for workbook files when workbook was introduced. Later versions merged them > both to .xls. > Johnathan may be used to the old version(s) of Excel and therefore he > distinguished between worksheet and workbook. > -- > Cheers > Van T. Dinh > MVP (Access) > > An EXCEL workbook normally uses an file extension of ".xls", so, if I > > understand your question, the answer is "the same thing". > > -- > > Ken Snell > > <MS ACCESS MVP>
Thu, 27 Oct 2005 00:43:29 GMT

jon #7 / 9	Opening an excel file through a form/running excel macros through access still not clear if the code below was to open an excel book and/or to run a macaro within it. Ideally would like seperate code. Aslo, is this code in excel vba or access vba? I have a button which opens excel (but no file) the code is Private Sub OpenExcel_Click() On Error GoTo Err_OpenExcel_Click Dim oApp As Object Set oApp = CreateObject("Excel.Application") oApp.Visible = True 'Only XL 97 supports UserControl Property On Error Resume Next oApp.UserControl = True Exit_OpenExcel_Click: Exit Sub Err_OpenExcel_Click: MsgBox Err.Description Resume Exit_OpenExcel_Click End Sub Ideally I would like 3 buttons 1. to open excel (already done) 2. open a specific file 3. once the file is open have a button to run an excel macro from a access form. any help would be greatly appreciated Quote: >-----Original Message----- >Here's some sample code for opening an EXCEL workbook and running a VBA >macro that's in the workbook in a public module: >Public Sub TestMacroRun() >Dim xlx As Object, xlw As Object >Set xlx = CreateObject("excel.application") >Set xlw = xlx.workbooks.Open("C:\Filename.xls") >xlx.Run "Filename.xls!MacroName()" >xlw.Close False >xlx.Quit >Set xlw = Nothing >Set xlx = Nothing >End Sub >-- > Ken Snell ><MS ACCESS MVP> >> I have a form with a "start excel button" but I cant >> figure how to then get a particular file open. I tried >> adding code to the module as I would in VBA in excel but >> didnt work. Any ideas?? >> Also, is there a way to run an excel macro from an >> access form after the excel file is opened? I have a >> macro which refreshes the links from access into excel. >.
Thu, 27 Oct 2005 06:17:37 GMT

Tim Ferguso #8 / 9	Opening an excel file through a form/running excel macros through access Quote: > Ideally I would like 3 buttons > 1. to open excel (already done) Set oExcel = CreateObject(.... Quote: > 2. open a specific file Set wkb = oExcel.Workbooks.Open("h:\MyData\.... Quote: > 3. once the file is open have a button to run an excel > macro from a access form. Call wkb!Module1!AddAllTheColumns("c:c") (I'm a bit less confident about the last one, but from memory it's fairly close <g>) HTH Tim F
Thu, 27 Oct 2005 19:14:58 GMT

Ken Snel #9 / 9	Opening an excel file through a form/running excel macros through access As Tim replied: This opens EXCEL in hidden mode: Set xlx = CreateObject("excel.application") This opens an EXCEL workbook file (a file ending in ".xls") that can contain any number of worksheets: Set xlw = xlx.workbooks.Open("C:\Filename.xls") This runs a macro in the opened workbook: xlx.Run "Filename.xls!MacroName()" Note that xlx and xlw are object variables that use late binding to get their types. -- Ken Snell <MS ACCESS MVP> Quote: > still not clear if the code below was to open an excel > book and/or to run a macaro within it. Ideally would > like seperate code. Aslo, is this code in excel vba or > access vba? > I have a button which opens excel (but no file) the code > is > Private Sub OpenExcel_Click() > On Error GoTo Err_OpenExcel_Click > Dim oApp As Object > Set oApp = CreateObject("Excel.Application") > oApp.Visible = True > 'Only XL 97 supports UserControl Property > On Error Resume Next > oApp.UserControl = True > Exit_OpenExcel_Click: > Exit Sub > Err_OpenExcel_Click: > MsgBox Err.Description > Resume Exit_OpenExcel_Click > End Sub > Ideally I would like 3 buttons > 1. to open excel (already done) > 2. open a specific file > 3. once the file is open have a button to run an excel > macro from a access form. > any help would be greatly appreciated > >-----Original Message----- > >Here's some sample code for opening an EXCEL workbook > and running a VBA > >macro that's in the workbook in a public module: > >Public Sub TestMacroRun() > >Dim xlx As Object, xlw As Object > >Set xlx = CreateObject("excel.application") > >Set xlw = xlx.workbooks.Open("C:\Filename.xls") > >xlx.Run "Filename.xls!MacroName()" > >xlw.Close False > >xlx.Quit > >Set xlw = Nothing > >Set xlx = Nothing > >End Sub > >-- > > Ken Snell > ><MS ACCESS MVP> > >> I have a form with a "start excel button" but I cant > >> figure how to then get a particular file open. I tried > >> adding code to the module as I would in VBA in excel > but > >> didnt work. Any ideas?? > >> Also, is there a way to run an excel macro from an > >> access form after the excel file is opened? I have a > >> macro which refreshes the links from access into excel. > >.
Fri, 28 Oct 2005 02:30:03 GMT

Page 1 of 1

[ 9 post ]