Excel/Access Integration (1 Viewer)

[RexesOperator]

I have an Excel porject that uses a form for data entry. It then does some calculations. Afterward I manually enter the data into an Access database.

Is there anyway of getting Access to accept the data automatically once the Excel program is done? The data currently is not stored in a record-friendly format, but I could certainly set it up that way.

 

[shades]

Howdy. Data setup is critical in this. I would say design the Excel form so that the output is placed into a true database format. If you need more use of the data in Excel, use another worksheet to pull the data from this base worksheet.

So, Excel Form > database format on worksheet (called Data) > another worksheet for your further use.

You can then use Data worksheet to import into Access.

==================

Perhaps if you could explain more about what you are doing, and why you are using Forms in Excel, when you could do it all in Access.
________
RENNSPORT

 

[RexesOperator]

Perhaps if you could explain more about what you are doing, and why you are using Forms in Excel, when you could do it all in Access.

The spreadsheet was set up years ago, long before I inherited the work. The form is a very simple Excel data entry form.

My contribution to bring this project into the 20th century was to develop the Access part (I also use Access to talk to MapInfo - a GIS product). The database is an index to our physical files (11,000 and counting). I've also added some functionality in that I can generate letters, invoices and other information. This is certainly not a major data storage project, but it does come under the "non-trivial" category.

After I sent the message a thought occurred to me - what if I launched the spreadsheet from Access? Could I use an Access form to enter the information into the spreadsheet?

 

[shades]

If you are bringing it into the 21st century, then why not use Access for everything and skip Excel? Is there a reason to continue using Excel? (Keep in mind my specialty is Excel, but use Access when appropriate. )
________
Smoke weed every day

 

[RexesOperator]

If you are bringing it into the 21st century, then why not use Access for everything and skip Excel? Is there a reason to continue using Excel? (Keep in mind my specialty is Excel, but use Access when appropriate. )

I would prefer to bump things up a century (at least we're into the 20th), but I don't know if Access can handle some of the equations. When I get to the office tomorrow, I will try and post a few examples.

My development time is pretty limited (I'm not an IT type, which means I have to do this on my own time), so there is no way for me to attempt a change now. I suspect Excel has some big flexibility advantages over Access, in this case.

Ultimately I think the whole project will be done from a single Oracle-based environment (about which my knowledge is non-existent). When I say ultimately - long after I retire in 2 years ...

 

[boblarson]

Could I use an Access form to enter the information into the spreadsheet?

Yes, you can and it's not all that hard to do.

If you set a reference to Excel, you can do it with early binding (and get some intellisense assistance).

See the sample I created for you. Be sure to put the Excel File in the same directory as the mdb file (for this example). Then, scroll along to a record in the form and click the button.

 

[RexesOperator]

Yes, you can and it's not all that hard to do.

If you set a reference to Excel, you can do it with early binding (and get some intellisense assistance).

See the sample I created for you. Be sure to put the Excel File in the same directory as the mdb file (for this example). Then, scroll along to a record in the form and click the button.

That's exactly what I am looking for. I won't be able to follow up on this until sometime this summer but that looks great. This means I will only have to do data entry once!
You have made me a very happy camper.