:)Hi i need to put a case in or a statement where the user can run all teh options for exampel below the user selects either "Youth Offenders Service" or "Look After Child" and it then runs either of the selections...i would like it to run all the selections one after anither with the user having to select each option one after another??? thanks any help wouuld be grand :):)



please see my code below...


Option Compare Database
Sub Test_Reporta5() 'UniversalEarlyYearsExtendedSchools

Dim AppExcel As Object
Dim LOCReport As Recordset
Dim LOCReport2 As Recordset
Dim CurrentSheet As Variant
Dim SPos As Integer
Dim rpos As Integer
Dim cpos As Integer
Dim i As Integer
Dim j As Integer
Dim count As Integer
Dim datasheet As Variant
Dim Test As Variant
Dim RepType As Integer
Dim Desc As String
Dim StartDate1 As Date
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim EndDate As Date
Dim StartDate As Date
Dim LocC As String
Dim LocL As String
Dim Par1 As Date
Dim Par2 As Date
Dim TeamNo As String
Dim strSql As String
Dim strDateStart As Date
Dim strEndStart As Date

' Stops warnings from appearing
DoCmd.SetWarnings False
' ************************************************** ******************************************
Set AppExcel = CreateObject("excel.application")
AppExcel.Visible = True
' Opens Excel template

'Selects Specialty
Select Case [Forms]![Test]![lstSpecialty]
Case "Look After Child"
AppExcel.Workbooks.Open "S:\SpecialtyActivityReporting\Look After Child.xls", , True

Case "Youth Offenders Service"
AppExcel.Workbooks.Open "S:\SpecialtyActivityReporting\Youth Offenders Service.xls", , True


Case Else
MsgBox "No Valid Specialty selected"
Exit Sub
End Select

How many spreadsheets are going to be open at the same time? Can't quite see the logic, is there a reason for this?

David

no, after it has finished populating the first spreadsheet i would then like it to start populating the next spreadsheet...the reason for this is it will be done overnight with nobody here to keep selecting each option...as it takes each option 30-40mins to run..eventualy there will be around 40 options...

many thanks,

matthew

p/s congtratz to burnley for reaching the premier league..im originaly from queensgate!!!

What is happening that takes 30-40 mins for each spreadsheet? Seems to me that there is a big flaw in your system.

However there is a way to accomplish your objective.

You would need to call a function that opens up the list of spreadsheet names and loop though the recordset calling the sub routine that performs the spreadsheet updates for each item in the recordset. You would enter a DoEvents command ater each call so the system would wait for it to complete before moving on to the next record.

David