A AEROTJK Oct 29, 2009 #1 are there any VBA functions that would allow me to execute an excel module from access??
D Daniel Pineault Oct 29, 2009 #2 Take a look at: http://www.programmersheaven.com/mb/vba/234583/234583/access-vba-calling-procedures-in-excel-module/ Hopefully it will help you get started. If you google your subject you will find lots of other examples to pull from. -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful.
Take a look at: http://www.programmersheaven.com/mb/vba/234583/234583/access-vba-calling-procedures-in-excel-module/ Hopefully it will help you get started. If you google your subject you will find lots of other examples to pull from. -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful.
R ryguy7272 Oct 30, 2009 #3 This is how I do it: 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:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls" ‘Of course, this is just an example; put the actual path to your actual file here… ' Opens Excel and makes it Visible Set objExcel = New Excel.Application objExcel.Visible = True ' Opens up a 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("Sheet1") With xlWS ' You are now working with the Named file and the named worksheet ' Your Excel code begins here…you can record a macro and make the process super easy!! End With ' Close and Cleanup xlWB.SaveAs xlSaveFile xlWB.Close xlapp.Quit Set xlapp = Nothing End Sub Send me an email if you have any questions about controlling Excel from Access!! HTH, Ryan--- (e-mail address removed)
This is how I do it: 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:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls" ‘Of course, this is just an example; put the actual path to your actual file here… ' Opens Excel and makes it Visible Set objExcel = New Excel.Application objExcel.Visible = True ' Opens up a 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("Sheet1") With xlWS ' You are now working with the Named file and the named worksheet ' Your Excel code begins here…you can record a macro and make the process super easy!! End With ' Close and Cleanup xlWB.SaveAs xlSaveFile xlWB.Close xlapp.Quit Set xlapp = Nothing End Sub Send me an email if you have any questions about controlling Excel from Access!! HTH, Ryan--- (e-mail address removed)
