[AccessD] (OT) - running excel applications from access

I believe your problem may be best solved by use of the GetObject() and
CreateObject() functions. They are discussed in kb article q178510 and you
can find cut n paste code to use in Access 97 help under the example for
the GetObject function. This code has worked for me over varying versions
of Access and Excel.
HTH

Lyle Hannum



                                                                                                      
                    oleg_123 at xuppa.com                                                                
                    Sent by:                       To:     accessd at databaseadvisors.com               
                    accessd-admin at databasead       cc:                                                
                    visors.com                     Subject:     [AccessD] (OT) - running excel        
                                                    applications from access                          
                                                                                                      
                    01/30/03 12:01 PM                                                                 
                    Please respond to                                                                 
                    accessd                                                                           
                                                                                                      
                                                                                                      




I am not sure whether it's "OT" or not,
I have a form with 6 command buttons, on click of each an Excel application
is
supposed to be created and opened. When I click the first button, the excel
application executes correctly, then I close the file click second button,
second application opens not refreshed file (doesn't execute correctly, and
gives an error message) I have to cancel the message, close the
application,
click on the button again, THEN it opens it correctly, same way with all
consecutive buttons, how can I fix it ?

I assume the problem is when exit excel ?
below is the copy of the code for the first 2 buttons, they are all mostly
the
same exsept names Excel.Applications and recordset differ.


Option Compare Database
Dim xls As New Excel.Application
Dim xls2 As New Excel.Application
Dim xls3 As New Excel.Application
Dim xls4 As New Excel.Application
Dim xls5 As New Excel.Application
Dim xls6 As New Excel.Application
Dim xls7 As New Excel.Application
Dim xls8 As New Excel.Application
Dim xls9 As New Excel.Application
Dim xls10 As New Excel.Application


Dim gdbs As Database, gdbs3 As Database, gdbs2 As Database
Dim intRow As Integer, intRow3 As Integer, intRow2 As Integer, m As Integer

Dim strsql, strsql1, strsqlF, strsqlGR, strsqlG, strsqlGA
    Dim strsqlAS, strsql2, strsql3 As String, strsql4 As String
Dim strsql_IRE_F, strsql_IRE_G, strsql_IRE_GA, strsql_IRE_GC, _
strsql_IRE_ASSOCGAP, strMARFinal
Dim rstEAR, rstEAR, rstEAR1, rstEAR2, rstEAR3
Dim rst_IRE_EARFINGAP

Private Sub cmd1


Set dbDaily = CurrentDb()
Set gdbs = CurrentDb
Strsql1 = "SELECT * FROM Final WHERE Filter = 'b1'"
Set rstEAR = dbDaily.OpenRecordset(strsql1)


With xls
 .Visible = True
 .Workbooks.Open ("L:\Pco\Art\appl1.xls")
 .Worksheets("GPs").Activate    'GPs Sheet

 rstEAR.MoveFirst
 intRow = 9

 Do Until rstEAR.EOF

    Range("b" & intRow).Value = rstEAR![bucket]
    Range("e" & intRow).Value = rstEAR![assetArt]
    Range("f" & intRow).Value = rstEAR![a_rate]
    'etc.


  rstEAR.MoveNext
  intRow = intRow + 1

 Loop

End With    'xls
Set xls = Nothing
MsgBox "The report you requested was created succesfully." & vbCrLf & _
"Please close it before opening any others", , "Art"
Exit Sub


End Sub

'------------------

Private Sub cmd2


Set dbDaily = CurrentDb()
Set gdbs = CurrentDb
Strsql2 = "SELECT * FROM Final WHERE Filter = 'b2'"
Set rstEAR2 = dbDaily.OpenRecordset(strsql1)


With xls2
 .Visible = True
 .Workbooks.Open ("L:\Pco\Art\appl2.xls")
 .Worksheets("GPs").Activate    'GPs Sheet

 rstEAR2.MoveFirst
 intRow = 9

 Do Until rstEAR1.EOF

    Range("b" & intRow).Value = rstEAR![bucket]
    Range("e" & intRow).Value = rstEAR![assetArt]
    Range("f" & intRow).Value = rstEAR![a_rate]
    'etc.


  rstEAR.MoveNext
  intRow = intRow + 1

 Loop

End With    'xls
Set xls2 = Nothing
MsgBox "The report you requested was created succesfully." & vbCrLf & _
"Please close it before opening any others", , "Art"
Exit Sub


End Sub




-------------------------------------------------
Find it all at Xuppa!
http://www.xuppa.com

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com