Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Access VBA functions from Excel
I have a database in Access with some programmed calculation routines in VBA.
Now I would like to use these routines in Excel - but I dont want to write them again. I thought I would just reference the .mdb file from Excel VBA (tools-references- browse for .mdb files) - but I just get the message that I cant add a reference to the specified file. Is there a way to use routines from .mdb in Excel-VBA? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Access VBA functions from Excel
You must refer to Access' library, not to Access' files. It is the library
the one that contains the object model. Proceed the same as you did, but instead of adding *.mdb files add Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library and so on. You should know what objects are you using in Access in order to get the right library. I would personally avoid this method, since it often leads to inconsistencies that require you to rewrite the code anyway and would use OLE automation instead. Example of inconsistency 1. As you may already know, recordsets are defined for both the DAO and the ADODB object models. How would Excel know which one you are talking about? Are you willing to rely on VBA's judgement? You would have to make explicit statements like: Dim obj01 As DAO.Recordset Dim obj02 As ADODB.Recordset *** Example of inconsistency 2. Both Word and Excel have an object called range. If you make a reference in Excel to Word's library, then it is not clear what object do you want to use and would lead you to the same problem as befo Dim wdRng As Word.Range Dim xlRng As Excel.Range *** As I said before, I would use OLE automation instead. The only thing you would need to change is the way your variables and arguments are declared. I currently do not have to much examples with Access, but I hope you find my examples from Excel and Word useful. Example of declaration 1. This is a macro declared in Word. Public Sub WordMacro(doc As Document) [code] End Sub If you are calling Word from Excel then the same macro would be: Public Sub WordMacroFromExcel(doc As Object) [code] End Sub *** You have to create instances of the objects you are using though. Example of OLE automation 1. Suppose you are in Excel and want to create a Word document. Option Explicit Public Sub CreateWordDocument Dim wdApp As Object Dim doc As Object 'Create a Word instance and make it visible 'Note: always make visible other applications whenever your code is prone 'to errors, like when you are still learning or experimenting, because if something 'goes wrong (an error) the instance will still be active, you won't see it and 'will only be able to shut it down using the task manager, which is cumbersome. Set wdApp = CreateObject("Word.Application") 'Add a Word document Set doc = wdApp.Documents.Add End Sub -- Carlos Mallen "RSunday" wrote: I have a database in Access with some programmed calculation routines in VBA. Now I would like to use these routines in Excel - but I dont want to write them again. I thought I would just reference the .mdb file from Excel VBA (tools-references- browse for .mdb files) - but I just get the message that I cant add a reference to the specified file. Is there a way to use routines from .mdb in Excel-VBA? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Access VBA functions from Excel
No - I do not refer to Access objects etc. I have written a function in
Access VBA. This function is the official "business logic" and I want to use the same function in Excel - but I don't want to re-write the function in Excel-VBA. If I had the function in a DLL - then I could just make a reference to it in Excel-VBA. In Access I can just refer to other .mdb files and share their functions. So my question is: How do I use the VBA-functions in my .mdb files in Excel? "Carlos" wrote: You must refer to Access' library, not to Access' files. It is the library the one that contains the object model. Proceed the same as you did, but instead of adding *.mdb files add Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library and so on. You should know what objects are you using in Access in order to get the right library. I would personally avoid this method, since it often leads to inconsistencies that require you to rewrite the code anyway and would use OLE automation instead. Example of inconsistency 1. As you may already know, recordsets are defined for both the DAO and the ADODB object models. How would Excel know which one you are talking about? Are you willing to rely on VBA's judgement? You would have to make explicit statements like: Dim obj01 As DAO.Recordset Dim obj02 As ADODB.Recordset *** Example of inconsistency 2. Both Word and Excel have an object called range. If you make a reference in Excel to Word's library, then it is not clear what object do you want to use and would lead you to the same problem as befo Dim wdRng As Word.Range Dim xlRng As Excel.Range *** As I said before, I would use OLE automation instead. The only thing you would need to change is the way your variables and arguments are declared. I currently do not have to much examples with Access, but I hope you find my examples from Excel and Word useful. Example of declaration 1. This is a macro declared in Word. Public Sub WordMacro(doc As Document) [code] End Sub If you are calling Word from Excel then the same macro would be: Public Sub WordMacroFromExcel(doc As Object) [code] End Sub *** You have to create instances of the objects you are using though. Example of OLE automation 1. Suppose you are in Excel and want to create a Word document. Option Explicit Public Sub CreateWordDocument Dim wdApp As Object Dim doc As Object 'Create a Word instance and make it visible 'Note: always make visible other applications whenever your code is prone 'to errors, like when you are still learning or experimenting, because if something 'goes wrong (an error) the instance will still be active, you won't see it and 'will only be able to shut it down using the task manager, which is cumbersome. Set wdApp = CreateObject("Word.Application") 'Add a Word document Set doc = wdApp.Documents.Add End Sub -- Carlos Mallen "RSunday" wrote: I have a database in Access with some programmed calculation routines in VBA. Now I would like to use these routines in Excel - but I dont want to write them again. I thought I would just reference the .mdb file from Excel VBA (tools-references- browse for .mdb files) - but I just get the message that I cant add a reference to the specified file. Is there a way to use routines from .mdb in Excel-VBA? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Access VBA functions from Excel
Maybe post an example to see what kind of functions we are talking about
here. RBS "RSunday" wrote in message ... No - I do not refer to Access objects etc. I have written a function in Access VBA. This function is the official "business logic" and I want to use the same function in Excel - but I don't want to re-write the function in Excel-VBA. If I had the function in a DLL - then I could just make a reference to it in Excel-VBA. In Access I can just refer to other .mdb files and share their functions. So my question is: How do I use the VBA-functions in my .mdb files in Excel? "Carlos" wrote: You must refer to Access' library, not to Access' files. It is the library the one that contains the object model. Proceed the same as you did, but instead of adding *.mdb files add Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library and so on. You should know what objects are you using in Access in order to get the right library. I would personally avoid this method, since it often leads to inconsistencies that require you to rewrite the code anyway and would use OLE automation instead. Example of inconsistency 1. As you may already know, recordsets are defined for both the DAO and the ADODB object models. How would Excel know which one you are talking about? Are you willing to rely on VBA's judgement? You would have to make explicit statements like: Dim obj01 As DAO.Recordset Dim obj02 As ADODB.Recordset *** Example of inconsistency 2. Both Word and Excel have an object called range. If you make a reference in Excel to Word's library, then it is not clear what object do you want to use and would lead you to the same problem as befo Dim wdRng As Word.Range Dim xlRng As Excel.Range *** As I said before, I would use OLE automation instead. The only thing you would need to change is the way your variables and arguments are declared. I currently do not have to much examples with Access, but I hope you find my examples from Excel and Word useful. Example of declaration 1. This is a macro declared in Word. Public Sub WordMacro(doc As Document) [code] End Sub If you are calling Word from Excel then the same macro would be: Public Sub WordMacroFromExcel(doc As Object) [code] End Sub *** You have to create instances of the objects you are using though. Example of OLE automation 1. Suppose you are in Excel and want to create a Word document. Option Explicit Public Sub CreateWordDocument Dim wdApp As Object Dim doc As Object 'Create a Word instance and make it visible 'Note: always make visible other applications whenever your code is prone 'to errors, like when you are still learning or experimenting, because if something 'goes wrong (an error) the instance will still be active, you won't see it and 'will only be able to shut it down using the task manager, which is cumbersome. Set wdApp = CreateObject("Word.Application") 'Add a Word document Set doc = wdApp.Documents.Add End Sub -- Carlos Mallen "RSunday" wrote: I have a database in Access with some programmed calculation routines in VBA. Now I would like to use these routines in Excel - but I dont want to write them again. I thought I would just reference the .mdb file from Excel VBA (tools-references- browse for .mdb files) - but I just get the message that I cant add a reference to the specified file. Is there a way to use routines from .mdb in Excel-VBA? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Access VBA functions from Excel
It can be any kind of function. The point is that I use it in Access2003 and
now I want to use the exact same function in Excel - but would like only to maintain it in one place. I guess a key info here is: The function does not access any tables - but is strictly a calculation routine. So it is a function like: Public function BusinessLogic1(x as double, y as double, z as double) as double code BusinessLogic1 = ...... the result of the code in the function End Function "RB Smissaert" wrote: Maybe post an example to see what kind of functions we are talking about here. RBS "RSunday" wrote in message ... No - I do not refer to Access objects etc. I have written a function in Access VBA. This function is the official "business logic" and I want to use the same function in Excel - but I don't want to re-write the function in Excel-VBA. If I had the function in a DLL - then I could just make a reference to it in Excel-VBA. In Access I can just refer to other .mdb files and share their functions. So my question is: How do I use the VBA-functions in my .mdb files in Excel? "Carlos" wrote: You must refer to Access' library, not to Access' files. It is the library the one that contains the object model. Proceed the same as you did, but instead of adding *.mdb files add Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library and so on. You should know what objects are you using in Access in order to get the right library. I would personally avoid this method, since it often leads to inconsistencies that require you to rewrite the code anyway and would use OLE automation instead. Example of inconsistency 1. As you may already know, recordsets are defined for both the DAO and the ADODB object models. How would Excel know which one you are talking about? Are you willing to rely on VBA's judgement? You would have to make explicit statements like: Dim obj01 As DAO.Recordset Dim obj02 As ADODB.Recordset *** Example of inconsistency 2. Both Word and Excel have an object called range. If you make a reference in Excel to Word's library, then it is not clear what object do you want to use and would lead you to the same problem as befo Dim wdRng As Word.Range Dim xlRng As Excel.Range *** As I said before, I would use OLE automation instead. The only thing you would need to change is the way your variables and arguments are declared. I currently do not have to much examples with Access, but I hope you find my examples from Excel and Word useful. Example of declaration 1. This is a macro declared in Word. Public Sub WordMacro(doc As Document) [code] End Sub If you are calling Word from Excel then the same macro would be: Public Sub WordMacroFromExcel(doc As Object) [code] End Sub *** You have to create instances of the objects you are using though. Example of OLE automation 1. Suppose you are in Excel and want to create a Word document. Option Explicit Public Sub CreateWordDocument Dim wdApp As Object Dim doc As Object 'Create a Word instance and make it visible 'Note: always make visible other applications whenever your code is prone 'to errors, like when you are still learning or experimenting, because if something 'goes wrong (an error) the instance will still be active, you won't see it and 'will only be able to shut it down using the task manager, which is cumbersome. Set wdApp = CreateObject("Word.Application") 'Add a Word document Set doc = wdApp.Documents.Add End Sub -- Carlos Mallen "RSunday" wrote: I have a database in Access with some programmed calculation routines in VBA. Now I would like to use these routines in Excel - but I dont want to write them again. I thought I would just reference the .mdb file from Excel VBA (tools-references- browse for .mdb files) - but I just get the message that I cant add a reference to the specified file. Is there a way to use routines from .mdb in Excel-VBA? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Access VBA functions from Excel
I think you will have to use Application.Run, something like this:
Sub test() Dim oAccess As Access.Application Dim strFile As String Dim lResult As Long Set oAccess = New Access.Application strFile = "C:\db1.mdb" oAccess.OpenCurrentDatabase strFile lResult = oAccess.Run("TimesTwo", 10) MsgBox lResult End Sub RBS "RSunday" wrote in message ... It can be any kind of function. The point is that I use it in Access2003 and now I want to use the exact same function in Excel - but would like only to maintain it in one place. I guess a key info here is: The function does not access any tables - but is strictly a calculation routine. So it is a function like: Public function BusinessLogic1(x as double, y as double, z as double) as double code BusinessLogic1 = ...... the result of the code in the function End Function "RB Smissaert" wrote: Maybe post an example to see what kind of functions we are talking about here. RBS "RSunday" wrote in message ... No - I do not refer to Access objects etc. I have written a function in Access VBA. This function is the official "business logic" and I want to use the same function in Excel - but I don't want to re-write the function in Excel-VBA. If I had the function in a DLL - then I could just make a reference to it in Excel-VBA. In Access I can just refer to other .mdb files and share their functions. So my question is: How do I use the VBA-functions in my .mdb files in Excel? "Carlos" wrote: You must refer to Access' library, not to Access' files. It is the library the one that contains the object model. Proceed the same as you did, but instead of adding *.mdb files add Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library and so on. You should know what objects are you using in Access in order to get the right library. I would personally avoid this method, since it often leads to inconsistencies that require you to rewrite the code anyway and would use OLE automation instead. Example of inconsistency 1. As you may already know, recordsets are defined for both the DAO and the ADODB object models. How would Excel know which one you are talking about? Are you willing to rely on VBA's judgement? You would have to make explicit statements like: Dim obj01 As DAO.Recordset Dim obj02 As ADODB.Recordset *** Example of inconsistency 2. Both Word and Excel have an object called range. If you make a reference in Excel to Word's library, then it is not clear what object do you want to use and would lead you to the same problem as befo Dim wdRng As Word.Range Dim xlRng As Excel.Range *** As I said before, I would use OLE automation instead. The only thing you would need to change is the way your variables and arguments are declared. I currently do not have to much examples with Access, but I hope you find my examples from Excel and Word useful. Example of declaration 1. This is a macro declared in Word. Public Sub WordMacro(doc As Document) [code] End Sub If you are calling Word from Excel then the same macro would be: Public Sub WordMacroFromExcel(doc As Object) [code] End Sub *** You have to create instances of the objects you are using though. Example of OLE automation 1. Suppose you are in Excel and want to create a Word document. Option Explicit Public Sub CreateWordDocument Dim wdApp As Object Dim doc As Object 'Create a Word instance and make it visible 'Note: always make visible other applications whenever your code is prone 'to errors, like when you are still learning or experimenting, because if something 'goes wrong (an error) the instance will still be active, you won't see it and 'will only be able to shut it down using the task manager, which is cumbersome. Set wdApp = CreateObject("Word.Application") 'Add a Word document Set doc = wdApp.Documents.Add End Sub -- Carlos Mallen "RSunday" wrote: I have a database in Access with some programmed calculation routines in VBA. Now I would like to use these routines in Excel - but I dont want to write them again. I thought I would just reference the .mdb file from Excel VBA (tools-references- browse for .mdb files) - but I just get the message that I cant add a reference to the specified file. Is there a way to use routines from .mdb in Excel-VBA? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about limiting access to functions in Excel | Excel Discussion (Misc queries) | |||
Access add in functions to VBA | Excel Worksheet Functions | |||
Not able to access certain Excel functions | Excel Worksheet Functions | |||
How do I access XLA functions? | Excel Programming | |||
Can't access .xla functions from .xls doc... | Excel Programming |