Access/Excel interaction...excel.exe problem

[Ian Mac]

All,

I'm trying to run reports from Access which load an Excel.xlt and places values into a worksheet,
then creates a chart and dynamically set the SourceData.

The problem I'm getting (and this is a recurring problem for a number of people where I work) is that when trying to run the same report (sometimes even a different one) a second time the SourceData line throw up an error message.

Now here's where I get confusing, so please bare with me.

I THINK I've discovered the cause of the problem but don't know how to fix it.
at the start of the code are the lines:

Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.Workbooks.Add("downloadProgressReport.xlt ")
Set xlSheet = xlBook.Worksheets(1)

this creates a version of Excel.
The problem when running the report a second time is that even when the first one is closed down, excel.exe is still in memory.

My thoughts on this are:

1) How can I tell Access remove the link to that excel.exe and focus on the newly created object?
I think Access is getting confused halfway through the code.
(Another question is what in the code might cause this to happen?)

or

2) How do I get rid of the other version of Excel when I close it (remove it from memory)

or

3) Check to see if Excel is currently running then use that if it is, if not CreateObject
(this may cause a problem as .xlt is not always used, sometimes the template is saved as
.xls therefore it will display an alert when it tries to load another version, unlike .xlt which will name it Report1, Report2 etc.)

or

4) I seem to remember seeing a way to loading the sheet using the CreateObject command.

Of course I may well be off track, so if anyone can help with these OR has any different ideas, I'd luv em thanks.

If I can get this licked, I will be putting on my Cape and Mask and walking right up to Mr. I.T. Person shouting, "I've come to ave the day."

Many thanks,

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-08-29 05:56

 

[KniteMare]

Ian, this will check to see if a version of excel is running. It is written VB but should be modifiable to vba if you want to insert it into your code.

Excuse the formating. I call this little function from an on open event. ( It sits on my desktop as a .vbs) You could then identify the file and kill it or at least close.

Is this, as far as the question, can I detect if excel is still running. going to help?



Dim objXL
Dim strMessage

On Error Resume Next

' Try to grab a running instance of
' Excel...
Set objXL = GetObject(, "Excel.Application")

' What did we find?..
If Not TypeName(objXL) = "Empty" Then
strMessage = "Excel Running."
Else
strMessage = "Excel Not Running."
End If

' Feedback to user...
MsgBox strMessage, vbInformation, "Excel Status"

' Make the Excel instance visible
' if we found one
if strMessage = "Excel Running." then _
objXL.Visible = true
'activeworkbook.close

Yours in EXCELent Frustration

KniteMare


In my mind I am already on vacation, with an option to retire.

 

[dmckinney]

take a look at the following for more detail on automation of excel. Specifically you've got to use a combination of getobject and createobject to get a handle on Excel and secondly that you tidy up when you're finished. If it's from an access form that you're opening excel, it may be appropriate to have a form level variable to contain the Excel application, which is loaded on form_initialise and unloaded on form teminate.

Dave.

http://home.att.net/~dashish/modules/mdl0006.htm

 

[Ian Mac]

Thanks for the response,

I have the code running lovely now,
however,
if Excel wasn't open prior to running the report, when I close Excel it is still in memory.
This isn't really an issue as it work fine, BUT I'd still like to stop it as it takes up system resource.
Is it because the code is calling a template from another application and the template isn't saved?

How can a flush out this nasty memory paracite?

Thank again ya'll

 

[KniteMare]

Thanks dmckinney for the supplied link!

I added the lines at the bottom to shut off excel.

If you save this in notepad as something like XLByeBye.vbs and place it on the desktop it seems to remove Excel. At least it did for me!

Dim objXL
Dim strMessage
Dim objActiveWkb
On Error Resume Next

' Try to grab a running instance of
' Excel...
Set objXL = GetObject(, "Excel.Application")

' What did we find?..
If Not TypeName(objXL) = "Empty" Then
strMessage = "Excel Running."
Set objActiveWkb = objXL.Application.ActiveWorkBook
Else
strMessage = "Excel Not Running."
End If

' Feedback to user...
MsgBox strMessage, vbInformation, "Excel Status"

' Make the Excel instance visible
' if we found one
if strMessage = "Excel Running." then _
objXL.Visible = true
if strMessage = "Excel Running." then _
objActiveWkb.Close
objXL.Application.Quit
Set objActiveWkb = Nothing: Set objXL = Nothing


It cannot run from Excel as it just detects itself. Is this what you are looking to do?

KniteMare

 

[Ian Mac]

KniteMare,

Not entirely what I need for this BUT it will come in very handy for something else, so for that Thanks.

Now this is a puzzler, I butchered your code and striped it down.
It worked lovely every time, without a problem................Until...

I got this error:

Run-time error '942'

ActiveX component can't create object.

at line:

Set xlapp = GetObject(, "Excel.Application")

This happened out of the blue, I did nothing to the code, not that I can recall anyway.

Any ideas why this might be happening?


Here's the whole darn thing just in case:

Private Sub PerformanceAnalysis()

Dim Weeks As Double, Remainder As Boolean, i As Integer, rstemp As Recordset, StartDate, StopDate As Date, arrPerf(), qdquery As QueryDef
Dim sql, ByTM, ByAccount, ByCSP, ChartTitle, CallType As String, Monitored As Integer

Set qdquery = CurrentDb.QueryDefs("qryProgressByAccount")

sql = "SELECT Avg(Main.BodyPer) AS AvgOfBodyPer, Avg(Main.SummarisingPer) AS AvgOfSummarisingPer, Avg(Main.TechnicalPer) AS AvgOfTechnicalPer, Avg(Main.OpeningPer) AS AvgOfOpeningPer, Avg(Main.OverallPer) AS AvgOfOverallPer FROM Main WHERE (((Main.CallDate) Between GetBeginDate() And GetEndDate())"
ByAccount = " AND ((Main.Account)=GetAccount()))"
ByTM = " AND ((Main.TeamManager)=GetTM())"
ByCSP = " AND ((Main.CSP)=GetCSP())"
ChartTitle = ""
If Not IsNull(Me.cmbCSP) Then
sql = sql & ByCSP
z_CSPName = Me.cmbCSP
ChartTitle = z_CSPName
End If

If Not IsNull(Me.cmbAccount) Then
sql = sql & ByAccount
z_Account = Me.cmbAccount
If ChartTitle <> "" Then
ChartTitle = ChartTitle & ", " & z_Account
Else
ChartTitle = z_Account
End If
End If

If Me.chkQA = True Then
If Me.chkTM = True Then
Monitored = 0
Else
Monitored = 1
CallType = " AND ((Right([CallCoach],4))=" & Chr(34) & "(QA)" & Chr(34) & ")"
End If
Else
If Me.chkTM = True Then
Monitored = 2
CallType = " AND ((Right([CallCoach],4))<>" & Chr(34) & "(QA)" & Chr(34) & ")"
End If
End If

If Monitored > 0 Then
qdquery.sql = (sql & CallType)
Else
qdquery.sql = (sql)
End If

If Not IsNull(Me.cmbTM) Then
qdquery.sql = qdquery.sql & ByTM
z_TM = Me.cmbTM
ChartTitle = ChartTitle & ", (" & z_TM & "'s team)"
End If

qdquery.Close

z_BeginDate = Me.txtBeginDate
z_EndDate = DateAdd("d", 1, Me.txtEndDate)
StopDate = z_EndDate
StartDate = z_BeginDate

ChartTitle = ChartTitle & " Analysis, " & StartDate & " - " & DateAdd("d", -1, StopDate)

If Me.chkQA = True And Me.chkTM = True Then
ChartTitle = ChartTitle & " , (QA & TM Calls)"
Else
If Me.chkQA = True Then
ChartTitle = ChartTitle & " , (QA Calls only)"
Else
ChartTitle = ChartTitle & " , (TM Calls only)"
End If
End If

Weeks = (z_EndDate - z_BeginDate) / 7

If Len(Trim((Str$(Weeks)))) > 1 Then
Weeks = Int(Weeks)
Remainder = True
Else
Remainder = False
End If

z_EndDate = DateAdd("d", 7, z_BeginDate)
For i = 1 To Weeks
Set rstemp = CurrentDb.OpenRecordset("qryProgressByAccount")
If Not rstemp.EOF Then
ReDim Preserve arrPerf(6, Weeks)
rstemp.MoveFirst
arrPerf(0, i - 1) = Str$(z_BeginDate) & " - " & Str$(DateAdd("d", -1, z_EndDate))
arrPerf(1, i - 1) = rstemp!AvgOfOpeningPer
arrPerf(2, i - 1) = rstemp!AvgOfBodyPer
arrPerf(3, i - 1) = rstemp!AvgOfSummarisingPer
arrPerf(4, i - 1) = rstemp!AvgOfTechnicalPer
arrPerf(5, i - 1) = rstemp!AvgOfOverallPer
rstemp.MoveNext
z_BeginDate = DateAdd("d", 7, z_BeginDate)
z_EndDate = DateAdd("d", 7, z_EndDate)
End If
Next

If Remainder = True Then
z_EndDate = StopDate
If z_BeginDate < z_EndDate Then
Set rstemp = CurrentDb.OpenRecordset("qryProgressByAccount")
If Not rstemp.EOF Then
ReDim Preserve arrPerf(6, Weeks + 1)
arrPerf(0, i - 1) = Str$(z_BeginDate) & " - " & Str$(DateAdd("d", -1, z_EndDate))
arrPerf(1, i - 1) = rstemp!AvgOfOpeningPer
arrPerf(2, i - 1) = rstemp!AvgOfBodyPer
arrPerf(3, i - 1) = rstemp!AvgOfSummarisingPer
arrPerf(4, i - 1) = rstemp!AvgOfTechnicalPer
arrPerf(5, i - 1) = rstemp!AvgOfOverallPer
rstemp.MoveFirst
End If
End If
End If
rstemp.Close

Dim objXL
Dim xlapp, xlBook, xlSheet As Object
Dim MFILE As String

' Try to grab a running instance of
' Excel...
'On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")

' What did we find?..
If Not TypeName(xlapp) = "Empty" Then
Else
Set xlapp = CreateObject("Excel.Application")
End If


Set xlBook = xlapp.Workbooks.Add("\gbnewdials01quality$downloadProgressReport.xlt ")
Set xlSheet = xlBook.Worksheets(1)

xlapp.Visible = True

With xlapp.Application
.Sheets("Progress Report").cells(2, "A") = "Account : " & z_Account
.Sheets("Progress Report").cells(3, "A") = "Date Period : " & Form_frmReports.txtBeginDate & " to " & Form_frmReports.txtEndDate
Dim h As Integer, v As Integer
For v = 7 To ((IIf(Remainder = True, Weeks + 1, Weeks)) + 7)
For h = 0 To 5
xlapp.Application.Sheets("Progress Report").cells(v, Chr$(h + 65)) = arrPerf(h, v - 7)
Next
Next

Dim Range As String, n As Integer
Range = "A6:F"
Range = Range & Trim(Str$(IIf(Remainder = True, (7 + Weeks), (6 + Weeks))))
.Charts.Add

With .ActiveChart
.ChartType = xlLineMarkers
.Location Where:=xlLocationAsNewSheet, Name:="Graph"
.SetSourceData Source:=Sheets("Progress Report").Range(Range), PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = ChartTitle
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False

If .SeriesCollection.Count > 0 Then
For n = 1 To 5
If n = 5 Then
.SeriesCollection.Border.Weight = xlThick
End If
.SeriesCollection.Smooth = True
Next

.SeriesCollection(4).Border.ColorIndex = 5
.SeriesCollection(4).MarkerForegroundColorIndex = 5
.Axes(xlCategory).TickLabels.Orientation = 45
End If
End With
End With

Set xlapp = Nothing

End Sub


Thanks for the help.

 

[Ian Mac]

Ok, I'm getting there,

now I'm going with Dave's suggestion, as I the problem exist with all Access>other office/MS software.

BUT (Again!),

excel.exe still exists in memory until Access is closed down.
How can i tell Access to stop/sever (whatever) the link the Excel so it can shut down correctly?

Thanks for all this help,

 

[larai]

Try adding the following after you set your excel object variable to nothing.

xlapp.Application.Quit

I was having a problem where my application wouldn't close; maybe it will solve your problem as well.

Rgds,
Lillianne

 

[XL-Dennis]

Ian,

I believe that You need to set the used variables to nothing:

Set xlSheet = Nothing
Set xlBook = Nothing
set xlapp = Nothing

BTW, Idon´t see that You use the variabel xlObj.

HTH,
Dennis

 

[Ian Mac]

On 2002-08-30 11:19, XL-Dennis wrote:
Ian,

I believe that You need to set the used variables to nothing:

Set xlSheet = Nothing
Set xlBook = Nothing
set xlapp = Nothing

BTW, Idon´t see that You use the variabel xlObj.

HTH,
Dennis

I'll try on Monday, BUT hadn't thought of setting ALL the varibles to Nothing, just the xlapp. DOH!
Sounds good to me,

xlObj isn't used your right, but it was, I'll tidy up the loose ends when the bleedin thing works.

BIG Cheers (if it works)