Loop through records in Access, paste results in Excel

[bo8482]

Hi all

I'm having some trouble with a bit of VBA code in Access and was wondering if you could help me.

My problem is this. I have a financial model that was built in Excel. The model has c.300 inputs in Excel. What I want to do now is run scenario analysis to see what affect changing various inputs have on my outputs.

For each scenario therefore, there will be c.300 different inputs. So if I want to run 100 scenarios, that will be 300 * 100 inputs that need to be changed. Plus, I would have to overwrite every input if I performed this anaylsis in Excel.

This is where my database comes in. I am storing all my inputs in the database, and then linking it to Excel. This way, I can store all my inputs and also associate them with various scenarios. At the moment, I have the code working perfectly for sending across one scenario to my model.

The crux of my problem though is that I need to run ALL my scenarios and compare the output given to me at the end for each scenario. E.g. I want to run Scenarios 1 through to 100, and compare the output that the model gives me for each scenario.

I'm not sure whether I have explained that well, so have attached a sample database and spreadsheet to try and illustrate what I'm trying to acheive. Note that this spreadsheet IS NOT THE ACTUAL MODEL. The actual model is a lot more complicated, but you should be able to grasp what I'm trying to acheive by my example database (I envisage the code being attached to the RUN ALL SCENARIOS button - note that the code in there at the moment doesn't work).



If anyone is able to help I would be eternally grateful!!

Thanks

Brian

 

[bo8482]

OK first up, I'm a novice coder...in fact I'm rubbish. But here is my stab from reading various bits and bobs on the internet (this is the snippet i think is causing offence and which I cannot get to work).


Set db = CurrentDb
strSQL1 = "SELECT * FROM tbl_Client1_Scenarios"
strSQL2 = "SELECT * FROM tbl_Client2_Scenarios"
Set rst1 = db.OpenRecordset(strSQL1)
Set rst2 = db.OpenRecordset(strSQL2)
Do Until rst.EOF
Sheets("INPUTS").Select
Range(A50).CopyFromRecordset (rst1)
Range(A70).CopyFromRecordset (rst2)
rst1.MoveNext
rst2.MoveNext
Loop

At the moment, i'm getting an 'Object Required' error. Could anyone point me in the right direction please?

Many thanks

 

[MarkK]

A few observations.
1) All the tables you are using seem to have identical structures. In this case there are considerable efficiencies to be gained by using a single table into which you insert typed records. Create a column called ClientNumber, for instance, and rather than create and name multiple tables as follows...

tblClient1, tblClient2, ..., tblClientN

...insert instead multiple records into a single table where a ClientNumber field distinguishes those records by client. Likewise, to make a record live, simply set a yes/no column named IsLive to True.
2) Use the reference you've set to Excel. Declare variables like...

dim xl as new excel.application
dim wb as excel.workbook
dim ws as excel.worksheet

set wb = xl.workbooks.open("C:\SomeFile.xlsx")
set ws = wb.sheets("SomeSheet")
with ws
  .activate
  .range("D4:GJ4").select
end with

... and you'll benefit from intellisense.
HTH,

 

[bo8482]

OK first off I'm kicking myself for being super dumb about the table structure. That really was stupid of me but I haven't any data in this dbase yet so can fix that still. There are only ever 5 client types but that is a fundamental point you have corrected already!

I'm not too sure on the 2nd part of your code. This will be good for selecting the range of output that i want to copy and paste, but it doesn't help me loop through the scenarios in the database...I need to be able to run each individual scenario in Excel and then compare all the outputs that I get. This is the bit which i'm having the most difficulty.

Any idea how I would perform this loop?

Many thanks

 

[MarkK]

What does it mean to "run a scenario?" Mostly your code doesn't run so it's not clear to me what you are trying to do. You've got some make table queries that seem to isolate a single record into a new table. OK.
Then your code loads an Excel file but I get a cascade of errors. What are you trying to do?
I would say that if you agree there is a table problem, solve that first. Tables are the foundation, and you can't build a good house on a lousy foundation.
HTH