Question Access Excel - Stupid I Know!

[RiskyP]

Hello,

Well - before i kick off - I am very much aware that this is both silly and non time effective, but I'll give it a go if i can.

I have a rather complicated Excel workbook - in a nutshell the user selects a costomer and enters in a tonne of purchases and variables using several different forms and worksheets which are all connected - all works perfectly and how i would like. I chose excel to do this because (again in a nutshell) there was far to many calculations and variables which i decided were much easier to program in a hidden 'formula' sheet in excel.

The only issue I have is - each customer has different tarrifs and discounts ect.. This customer part would be much better suited in an Access Customer database as i would like the user to be able to add new customers and edit existing customers easily, which so far, have been placed on an seperate sheet in excel.

Would it be possible for me to place a VBA in my database where - once the clinet has been selected - to 'secretly' open the access database, run a "SELECT * FROM" Query and place the data for that particular client into the correct zones in my excel formula sheet. If so, how would i go about writing this?

Can Excel VBA be written in a way that Access would understand? or is it the same program just written differently per application?

Thanks
P

 

[RiskyP]

Ok so after some digging I have found so far;

Set AcDb = CreateObject("Access.Application")
Dim DbName As String
DbName = ("My File Name")
AcDb.OpenCurrentDatabase DbName

From here i am planning to set rs as recordset - make rs the result of the query mentioned above. i will then use

DoCmd.TransferSpreadsheet

To transfer recordset into a new excel sheet on my workbook.
After this I plan to copy data accross (As there will only ever be one record set) straight into my formula sheet.
The transferred sheet will then be deleted - and the acces table will be closed

Heads up if anybody has a better suggestion
P

 

[Alansidman]

This may also be helpful to your situation.

http://datapigtechnologies.com/blog/index.php/running-an-access-parameter-query-from-excel/

 

[RiskyP]

Many thanks - Link very usefull

Will post my code with tweeks once completed