Access + Excel = Standalone App?

[MARKYB0Y]

Hi All,

I am in the process of creating a scoring and averages application.

I have created a Access 2007 database to handle all the input of information, I also have an Excel 2007 workbook that I can import the table from access and then do tha calculations.

What I would like to know is if there is anyway I can incorporate both apps so that the user only needs to open one app to do all the work? not sure if this can be done by using excel to internally open Access somehow and then use the form or the other way around, or if the excel and access structures can be imported into something like visual basic to create a standalone application?

thoughts appreciated.

 

[DJkarl]

Hi All,

I am in the process of creating a scoring and averages application.

I have created a Access 2007 database to handle all the input of information, I also have an Excel 2007 workbook that I can import the table from access and then do tha calculations.

What I would like to know is if there is anyway I can incorporate both apps so that the user only needs to open one app to do all the work? not sure if this can be done by using excel to internally open Access somehow and then use the form or the other way around, or if the excel and access structures can be imported into something like visual basic to create a standalone application?

thoughts appreciated.

In short yes.

To elaborate on that answer, you can use VBA inside Access or Excel to manipulate one or the other programs (I prefer to use Access to manipulate Excel but that's personal preference on my part.)

For example:

Dim exObj as Excel.Application
 
Set exObj = New Excel.Application

This would create a new Excel Application object that you could work with, now this method requires you to reference the Excel object library, I recommend doing this during development because you get the intellisense this way, you may want to remove it for production though and go to the more flexable approach of

Dim exObj as Object
 
Set exObj = CreateObject("Excel.Application")

You also mentioned creating a stand alone Visual Basic Application, this is possible though I haven't used VB.NET with Office 2007 yet, I know it works with 2003 and earlier. You would create your solution in VB, though this is more work on your part as you would need to create all the interfaces from the ground up, Forms, menus, etc... but if Access and Excel aren't giving you the flexibility you need then this route would be best as you have total control over the layout of your project.

 

[MARKYB0Y]

Hi DJkarl,

thanks for the response. as I do not really know VB that well I think Access and Excel are my best route.

However what I would like is from Access to have a button on a form that would open my excel document and run a macro if that is possible?

I do nto really want to create a new excel document as it has taken me a long time to work out all of my formula's etc.

 

[DJkarl]

Hi DJkarl,

thanks for the response. as I do not really know VB that well I think Access and Excel are my best route.

However what I would like is from Access to have a button on a form that would open my excel document and run a macro if that is possible?

I do nto really want to create a new excel document as it has taken me a long time to work out all of my formula's etc.

That's very possible, you would still need to create an Excel Application object, but then you need to Open an Existing workbook instead of creating a new one. I'm pretty sure there are tons of examples on this forum of manipulating Excel from Access, or you could google it and get several examples of where to start.