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

oleg_123 at xuppa.com oleg_123 at xuppa.com
Thu Jan 30 11:02:01 CST 2003


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




More information about the AccessD mailing list