Auto writing Excel spreadsheets from Access (97 & 2K) fails to releaseExcel

[Mr. Smith]

Hello all,

My code can successfully open, write to, format and save several
worksheets in a workbook then save it by a given name, close and quit excel.

My problem is that if I try and do it again, Excel hangs. OR if I open
Excel again (say from a desktop icon) before I close Access, Excel
hangs. (this has happened for both 97 & 2000 for me)

I of course thought that I mustn't be unloading a variable properly.
but I'll show my closing code:

Set wbknew = Nothing
Set wksnew = Nothing

ActiveWorkbook. SaveAs Filename:=strDe stination, _
FileFormat:=xlN ormal, Password:="", WriteResPasswor d:="",
ReadOnlyRecomme nded:=False, CreateBackup:=F alse

ActiveWorkbook. Close
appExcel.Quit

Set appExcel = Nothing
And the following is how I dim these variables:

Dim wbknew As Excel.Workbook, wksnew As Excel.Worksheet
Dim appExcel As New Excel.Applicati on
I can't see what might be causing Access to hold on to Excel in such a
way as to make Excel hang.

Another clue: When I run the code for a second time, it specifically
hangs when the code attempts to format the second work sheet. When the
code strikes this line of code:

Columns(1).Colu mnWidth = 1

I get this message:

Run-time error '1004'
Method 'Columns' of the object '_Global' failed

I have also noticed that Excel still appears in the 'Processes' tab in
Task Manager after my codes execution ends. Even killing the process
doesn't fix the problem.

I really hope someone knows the way around this. Its been bugging me
for a few years know because it means that every time I generate a
Spreadsheet automatically, I have to restart Access to run another.

TIA
Mr. Smith.

[Rich P]

In Task Manager under the Process tab, if you click on the Image Name
tab this will list all the processes in alphabetical order (asc or
desc). You will probably see that even though you close your Excel exe,
there are probably multiple Excel exe's running. You need to end all of
them (been there).

Next, if xlObj.Quit is not ending the Excel process (it should, you
probably missed a Quit somewhere for one of your Excel Application
objects) you can use API code to determine if any Excel processes are
running and then End them. Here are 3 links to API code for doing this:

'Get Class names of running applications
http://www.mvps.org/access/api/api0013.htm

'Find out if an application is running
http://www.mvps.org/access/api/api0007.htm

'Closing an Application from another application
http://www.mvps.org/access/api/api0025.htm
Rich

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Jorgensen]

Try not using the New keyword in your variable declarations. As New can be
convenient for some operations, but it's very dangerous for others, and you
should -never- use it for Automation objects because it can lead to the
problems you are having now.

The problem with the way VBA any As New variable, is that each time you refer
to the variable in any way, VBA checks to see if it contains Nothing, and
creates a new instance of the variable's type if not. It's possible that this
is causing your code to re-open Excel. Instead of As New, try ...

Dim appExcel As Excel.Applicati on
Set appExcel = New Excel.Applicati on
On Mon, 21 Jun 2004 20:38:34 +1000, "Mr. Smith" <Mr.Smith@Large > wrote:

Hello all,

My code can successfully open, write to, format and save several
worksheets in a workbook then save it by a given name, close and quit excel.

My problem is that if I try and do it again, Excel hangs. OR if I open
Excel again (say from a desktop icon) before I close Access, Excel
hangs. (this has happened for both 97 & 2000 for me)

I of course thought that I mustn't be unloading a variable properly.
but I'll show my closing code:

Set wbknew = Nothing
Set wksnew = Nothing

ActiveWorkbook. SaveAs Filename:=strDe stination, _
FileFormat:=xlN ormal, Password:="", WriteResPasswor d:="",
ReadOnlyRecomme nded:=False, CreateBackup:=F alse

ActiveWorkbook. Close
appExcel.Quit

Set appExcel = Nothing
And the following is how I dim these variables:

Dim wbknew As Excel.Workbook, wksnew As Excel.Worksheet
Dim appExcel As New Excel.Applicati on
I can't see what might be causing Access to hold on to Excel in such a
way as to make Excel hang.

Another clue: When I run the code for a second time, it specifically
hangs when the code attempts to format the second work sheet. When the
code strikes this line of code:

Columns(1).Colu mnWidth = 1

I get this message:

Run-time error '1004'
Method 'Columns' of the object '_Global' failed

I have also noticed that Excel still appears in the 'Processes' tab in
Task Manager after my codes execution ends. Even killing the process
doesn't fix the problem.

I really hope someone knows the way around this. Its been bugging me
for a few years know because it means that every time I generate a
Spreadsheet automatically, I have to restart Access to run another.

TIA
Mr. Smith.

[Rich P]

Going along with Steve's line of reasoning I thought I would share how I
set up Excel objects from Access (since this is a significant portion of
my daily routine). I declare an Excel object variable (with a reference
to the respective Excel Object Library, Excel 8.0, 9.0, 10...)

Dim xlObj As Excel.Applicati on, wkbk As Excel.WorkBook
Dim sht As Excel.WorkSheet , rng As Excel.Range
...
Set xlObj = CreateObject("E xcel.Applicaion ")

Steve is correct about the delicateness of the New Keyword in VBA. I
didn't think about that because I have been steering away from using New
in VBA for Office Applications (although New works quite well in a
managed environment like VB.Net or C# - where the compiler will slap
your hand right away if you declare something incorrectly), but I
reserve using New for instantiating custom objects within Access
(objects created with a class module) because here you don't have to
worry about running an external process. It is internal to Access.

Rich

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mr. Smith]

Rich P wrote:

Going along with Steve's line of reasoning I thought I would share how I
set up Excel objects from Access (since this is a significant portion of
my daily routine). I declare an Excel object variable (with a reference
to the respective Excel Object Library, Excel 8.0, 9.0, 10...)

Dim xlObj As Excel.Applicati on, wkbk As Excel.WorkBook
Dim sht As Excel.WorkSheet , rng As Excel.Range
..
Set xlObj = CreateObject("E xcel.Applicaion ")

Steve is correct about the delicateness of the New Keyword in VBA. I
didn't think about that because I have been steering away from using New
in VBA for Office Applications (although New works quite well in a
managed environment like VB.Net or C# - where the compiler will slap
your hand right away if you declare something incorrectly), but I
reserve using New for instantiating custom objects within Access
(objects created with a class module) because here you don't have to
worry about running an external process. It is internal to Access.

Rich

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks very much for your input.

I actually only open one workbook per execution of this procedure and as
such I only need to call Quit once at the end of the process. I have
followed your advice above and unfortunately this has not woked for me.

Also, in your previous post your advised that I check Task Manager for
ALL possible instances of Excel running. Since including your above
recommendation I have found there to NO instances of Excel post code
execution.

I'm quite confused :)

Here is all of the code I'm using. Please excuse it, as its not very
modular at all. Its just something I've hacked up for free for brownie
points with for client. Would you mind checking it to see if there is
any glaring issue? I'd really apperciate it.
Option Compare Database
Option Explicit
Function ExportAmenityRa tingReport(strS QL As String) As Boolean
On Error GoTo eh_ExportAmenit yRatingReport

Dim dbLocal As Database
Dim rst As Recordset
Dim wbknew As Excel.Workbook, wksnew As Excel.Worksheet
Dim appExcel As Excel.Applicati on
Dim strCurrConditio n As String
Dim strBuilding As String
Dim intRow As Integer
Dim intStartRow As Integer
Dim intElementCount As Integer
Dim intRatingElemen tCount
Dim intTotalElement Count As Integer
Dim intBuildingCoun t As Integer
Dim strDestination As String

ExportAmenityRa tingReport = True

strDestination = API_FileSave("M S Excel Spreadsheet", "*.xls",
"C:\", "Save As...")

If strDestination = "C:\" Then Exit Function
If Right(strDestin ation, 4) <> ".xls" Then strDestination =
strDestination & ".xls"

If strSQL <> "" Then
strSQL = "SELECT * FROM qryBuildingAmen ityElementRatin gs WHERE
" & strSQL
Else
strSQL = "qryBuildingAme nityElementRati ngs"
End If

'open data table
Set dbLocal = CurrentDb()
Set rst = dbLocal.OpenRec ordset(strSQL, dbOpenSnapshot)

'set up Workbook
Set appExcel = CreateObject("E xcel.Applicatio n")
Set wbknew = appExcel.Workbo oks.Add
Set wksnew = wbknew.Workshee ts.Add
wksnew.Name = "Raw Data"
appExcel.Visibl e = True

WriteRawData wksnew, rst

Set wksnew = Nothing
Set wksnew = wbknew.Workshee ts.Add
wksnew.Name = "Rating Summary"
appExcel.Visibl e = True

WriteAmenityEle mentSummary wksnew

Set wksnew = Nothing
Set wksnew = wbknew.Workshee ts.Add
wksnew.Name = "Amenity Ratings"
appExcel.Visibl e = True

WriteHeader wksnew
rst.MoveFirst
intRow = 4
intTotalElement Count = 0

Do While Not rst.EOF

wksnew.Range(Ce lls(intRow, 1), Cells(intRow, 14)).select
Call SetEdgeBottomBo rder

intRow = intRow + 1
wksnew.Cells(in tRow, 2) = "Amenity Elements With Condition
Rating: " & rst!BuildingAme nityElementCond ition
wksnew.Cells(in tRow, 2).select
Call SetFont(True, True, 14)

strCurrConditio n = rst!BuildingAme nityElementCond ition
intBuildingCoun t = 0
intRatingElemen tCount = 0

Do While rst!BuildingAme nityElementCond ition = strCurrConditio n

intRow = intRow + 2
wksnew.Cells(in tRow, 3) = "Building Name: " & rst![Building
Name] & " Category: " & rst![Building Category]
wksnew.Cells(in tRow, 3).select
Call SetFont(True, False, 12)
intRow = intRow + 1
wksnew.Cells(in tRow, 4) = "Amenity Element Category:"
wksnew.Cells(in tRow, 7) = "Amenity Element Sub-Category:"
wksnew.Cells(in tRow, 11) = "Est Life:"
wksnew.Range(Ce lls(intRow, 2), Cells(intRow, 11)).select
Call SetFont(True, False, 9)

strBuilding = rst![Building Name]
intBuildingCoun t = intBuildingCoun t + 1
intElementCount = 0

Do While rst![Building Name] = strBuilding

intRow = intRow + 1

wksnew.Cells(in tRow, 4) = rst!AmenityElem entCategory
wksnew.Cells(in tRow, 7) = rst!AmenityElem entCategorySub
wksnew.Cells(in tRow, 11) =
rst!BuildingAme nityElementEstL ife
wksnew.Range(Ce lls(intRow, 4), Cells(intRow, 11)).select
Call SetFont(False, False, 9)
wksnew.Cells(in tRow, 11).select
Selection.Horiz ontalAlignment = xlCenter

intElementCount = intElementCount + 1
intRatingElemen tCount = intRatingElemen tCount + 1

rst.MoveNext
If rst.EOF Then Exit Do

Loop

intRow = intRow + 1
wksnew.Cells(in tRow, 3) = "Element Total: " &
intElementCount & " elements rated."
wksnew.Cells(in tRow, 3).select
Call SetFont(True, False, 12)
wksnew.Range(Ce lls(intRow, 3), Cells(intRow, 7)).select
Call SetEdgeTopBorde r
Call SetEdgeBottomBo rder

intRow = intRow + 2
If rst.EOF Then Exit Do

Loop

intTotalElement Count = intTotalElement Count + intRatingElemen tCount

wksnew.Cells(in tRow, 2) = "Rating Total: " & intElementCount &
" elements rated."
wksnew.Cells(in tRow, 2).select
Call SetFont(True, False, 9)
wksnew.Range(Ce lls(intRow, 1), Cells(intRow, 14)).select
Call SetEdgeTopBorde r
Call SetEdgeBottomBo rder
intRow = intRow + 2

If rst.EOF Then Exit Do

Loop

intRow = intRow + 1
wksnew.Range(Ce lls(intRow, 1), Cells(intRow, 14)).select
Call SetEdgeTopBorde r
Call SetEdgeBottomBo rder

intRow = intRow + 2
wksnew.Cells(in tRow, 2) = "Report Total: " & intBuildingCoun t & "
Buildings included in this report with " & intTotalElement Count & "
amenity elements rated."
wksnew.Cells(in tRow, 2).select
Call SetFont(True, False, 9)
wksnew.Cells(1, 1).select

With ActiveWindow
.Zoom = 100
.DisplayGridlin es = False
.DisplayHeading s = False
End With

wksnew.PageSetu p.Orientation = xlLandscape

Set wbknew = Nothing
Set wksnew = Nothing

ActiveWorkbook. SaveAs Filename:=strDe stination, _
FileFormat:=xlN ormal, Password:="", WriteResPasswor d:="",
ReadOnlyRecomme nded:=False, CreateBackup:=F alse

ActiveWorkbook. Close
appExcel.Quit

Set appExcel = Nothing
rst.Close
dbLocal.Close

Exit Function

eh_ExportAmenit yRatingReport:
ExportAmenityRa tingReport = False
End Function
Function ExportCondition ReportData() As Boolean
On Error Resume Next

Dim dbLocal As Database
Dim rst As Recordset
Dim wbknew As Excel.Workbook, wksnew As Excel.Worksheet
Dim appExcel As New Excel.Applicati on
Dim intRow As Integer
Dim i As Integer
Dim strDestination As String
Dim fld As Field

ExportCondition ReportData = True

strDestination = API_FileSave("M S Excel Spreadsheet", "*.xls",
"C:\", "Save As...")

If strDestination = "C:\" Then Exit Function
If Right(strDestin ation, 4) <> ".xls" Then strDestination =
strDestination & ".xls"

DoCmd.SetWarnin gs False
DoCmd.RunSQL "DELETE * FROM tempConditionEx port"
DoCmd.OpenQuery "qryConditionEx port"

'open data table
Set dbLocal = CurrentDb()
Set rst = dbLocal.OpenRec ordset("tempCon ditionExport", dbOpenSnapshot)

MsgBox "Depending on the number of records being written to Excel
this may take a few minutes.", vbInformation, "Informatio n."

'set up Workbook
Set wbknew = appExcel.Workbo oks.Add
Set wksnew = wbknew.Workshee ts.Add
wksnew.Name = "Condition Report Raw Data"
appExcel.Visibl e = True

rst.MoveFirst
intRow = 1
i = 1

For Each fld In rst.Fields

wksnew.Cells(in tRow, i) = fld.Name
i = i + 1

Next fld

intRow = 2

With rst

.MoveFirst

Do While Not .EOF

i = 1

For Each fld In rst.Fields
wksnew.Cells(in tRow, i) = fld.Value
i = i + 1
Next fld

.MoveNext
intRow = intRow + 1

Loop

End With

Set wbknew = Nothing
Set wksnew = Nothing

ActiveWorkbook. SaveAs Filename:=strDe stination, _
FileFormat:=xlN ormal, Password:="", WriteResPasswor d:="",
ReadOnlyRecomme nded:=False, CreateBackup:=F alse

'ActiveWorkbook .Close
'appExcel.Quit

Set appExcel = Nothing
Set wbknew = Nothing
Set wksnew = Nothing

rst.Close
dbLocal.Close

Exit Function

eh_ExportCondit ionReportData:
ExportCondition ReportData = False
End Function

Sub SetFont(booBold As Boolean, booItal As Boolean, intSize As Integer,
Optional strFont As String)

With Selection.Font
.Bold = booBold
.Italic = booItal
.Name = IIf(IsMissing(s trFont) Or strFont = "", "Times New
Roman", strFont)
.Size = intSize
End With

End Sub

Sub SetInteriorBord er()
With Selection.Borde rs(xlInsideVert ical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeLeftBord er()
With Selection.Borde rs(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeRightBor der()
With Selection.Borde rs(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeTopBorde r()
With Selection.Borde rs(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeBottomBo rder()
With Selection.Borde rs(xlEdgeBottom )
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeLeftBord erHeavy()
With Selection.Borde rs(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeRightBor derHeavy()
With Selection.Borde rs(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeTopBorde rHeavy()
With Selection.Borde rs(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeBottomBo rderHeavy()
With Selection.Borde rs(xlEdgeBottom )
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetSurroundHeav y()
Call SetEdgeLeftBord erHeavy
Call SetEdgeRightBor derHeavy
Call SetEdgeTopBorde rHeavy
Call SetEdgeBottomBo rderHeavy
End Sub

Sub SetSurround()
Call SetEdgeLeftBord er
Call SetEdgeRightBor der
Call SetEdgeTopBorde r
Call SetEdgeBottomBo rder
End Sub
Function WriteHeader(wks new As Worksheet) As Integer

Columns(1).Colu mnWidth = 1
Rows(1).RowHeig ht = 7.57

'creates the header for each Channel
wksnew.Cells(2, 2) = "Building Amenity Element Conditions (By Rating)"
wksnew.Cells(2, 2).select
Call SetFont(True, False, 24)

wksnew.Range(Ce lls(1, 1), Cells(1, 14)).select
Call SetEdgeBottomBo rder

End Function
Sub WriteFooter(int Row As Integer, wksnew As Worksheet, intStartRow As
Integer)

'writes and formats the subtotals for each Channel
intRow = intRow + 1
wksnew.Cells(in tRow, 5) = "Sub Total"
wksnew.Cells(in tRow, 5).select
Call SetFont(True, False, 10)
wksnew.Cells(in tRow, 6).Value = "=Sum(F" & intStartRow & ":F" &
intRow - 2 & ")"
wksnew.Cells(in tRow, 5).select
Call SetFont(True, False, 10)
Selection.Numbe rFormat = "$##,##0"

End Sub
Sub SetColumnWidths ()

Columns("A:A"). ColumnWidth = 7.57
Columns("B:B"). ColumnWidth = 4.29
Columns("C:C"). ColumnWidth = 7.43
Columns("D:D"). ColumnWidth = 18
Columns("E:E"). ColumnWidth = 26.86
Columns("F:F"). ColumnWidth = 12.71
Columns("G:G"). ColumnWidth = 19.57
Columns("H:H"). ColumnWidth = 10.86
Columns("I:I"). ColumnWidth = 11.14
Columns("J:J"). ColumnWidth = 18.57
Columns("A:J"). select
Selection.Horiz ontalAlignment = xlCenter

End Sub
Function WriteRawData(Wk s As Worksheet, rst As Recordset)

Dim intRow As Integer

intRow = 1

Wks.Cells(intRo w, 1) = "Condition Rating"
Wks.Cells(intRo w, 2) = "Building Name"
Wks.Cells(intRo w, 3) = "Amenity Element Category"
Wks.Cells(intRo w, 4) = "Amenity Element Category Sub"
Wks.Cells(intRo w, 5) = "Est. Life"

intRow = 2

With rst

.MoveFirst

Do While Not .EOF

Wks.Cells(intRo w, 1) = !BuildingAmenit yElementConditi on
Wks.Cells(intRo w, 2) = ![Building Name]
Wks.Cells(intRo w, 3) = !AmenityElement Category
Wks.Cells(intRo w, 4) = !AmenityElement CategorySub
Wks.Cells(intRo w, 5) = !BuildingAmenit yElementEstLife

.MoveNext
intRow = intRow + 1

Loop

End With

End Function
Function WriteAmenityEle mentSummary(Wks As Worksheet)

Dim intRow As Integer
Dim Db As Database
Dim rst As Recordset
Dim intNextSet As Integer

Columns(1).Colu mnWidth = 1
Rows(1).RowHeig ht = 7.57

intRow = 2

Wks.Cells(intRo w, 2) = "Rating Summary for Amenity Elements"
Wks.Cells(intRo w, 2).select
Call SetFont(True, False, 14)

intRow = 4

Wks.Cells(intRo w, 2) = "Amenity Element Category"
Wks.Cells(intRo w, 3) = "Rating 1"
Wks.Cells(intRo w, 4) = "Rating 2"
Wks.Cells(intRo w, 5) = "Rating 3"
Wks.Cells(intRo w, 6) = "Rating 4"
Wks.Cells(intRo w, 7) = "Rating 5"
Wks.Cells(intRo w, 8) = "Rating 6"

Wks.Range(Cells (intRow, 2), Cells(intRow, 8)).select
Call SetFont(True, False, 9)
Call SetSurroundHeav y

Set Db = CurrentDb()
Set rst = Db.OpenRecordse t("qryAmenityEl ementRatingSumm ary",
dbOpenSnapshot)

intRow = intRow + 1

With rst

.MoveFirst

Do While Not .EOF

Wks.Cells(intRo w, 2) = !AmenityElement Category
Wks.Cells(intRo w, 3) = !SumOfRating1
Wks.Cells(intRo w, 4) = !SumOfRating2
Wks.Cells(intRo w, 5) = !SumOfRating3
Wks.Cells(intRo w, 6) = !SumOfRating4
Wks.Cells(intRo w, 7) = !SumOfRating5
Wks.Cells(intRo w, 8) = !SumOfRating0

.MoveNext
intRow = intRow + 1

Loop

.Close

End With

Wks.Range(Cells (4, 2), Cells(intRow - 1, 2)).select
Call SetFont(True, False, 9)
Call SetSurroundHeav y

Wks.Range(Cells (4, 2), Cells(intRow - 1, 8)).select
Call SetSurroundHeav y

intRow = intRow + 3

Wks.Cells(intRo w, 2) = "Rating Summary for Sub Elements"
Wks.Cells(intRo w, 2).select
Call SetFont(True, False, 14)

intRow = intRow + 2
intNextSet = intRow

Wks.Cells(intRo w, 2) = "Amenity Element Category"
Wks.Cells(intRo w, 3) = "Amenity Element Sub-Category"
Wks.Cells(intRo w, 4) = "Rating 1"
Wks.Cells(intRo w, 5) = "Rating 2"
Wks.Cells(intRo w, 6) = "Rating 3"
Wks.Cells(intRo w, 7) = "Rating 4"
Wks.Cells(intRo w, 8) = "Rating 5"
Wks.Cells(intRo w, 9) = "Rating 6"

Wks.Range(Cells (intRow, 2), Cells(intRow, 9)).select
Call SetFont(True, False, 9)
Call SetSurroundHeav y

Set Db = CurrentDb()
Set rst = Db.OpenRecordse t("qryAmenitySu bElementRatingS ummary",
dbOpenSnapshot)

intRow = intRow + 1

With rst

.MoveFirst

Do While Not .EOF

Wks.Cells(intRo w, 2) = !AmenityElement Category
Wks.Cells(intRo w, 3) = !AmenityElement CategorySub
Wks.Cells(intRo w, 4) = !Rating1
Wks.Cells(intRo w, 5) = !Rating2
Wks.Cells(intRo w, 6) = !Rating3
Wks.Cells(intRo w, 7) = !Rating4
Wks.Cells(intRo w, 8) = !Rating5
Wks.Cells(intRo w, 9) = !Rating0

.MoveNext
intRow = intRow + 1

Loop

.Close

End With

intRow = intRow - 1

Wks.Range(Cells (intNextSet, 2), Cells(intRow, 2)).select
Call SetFont(True, False, 9)
Call SetSurroundHeav y
Wks.Range(Cells (intNextSet, 3), Cells(intRow, 3)).select
Call SetFont(True, False, 9)
Call SetSurroundHeav y

Wks.Range(Cells (intNextSet, 2), Cells(intRow, 9)).select
Call SetSurroundHeav y

Wks.Cells(1, 1).select

End Function

[Doug Hutcheson]

"Mr. Smith" <Mr.Smith@Large > wrote in message
news:40******** *************** @news.syd.swift dsl.com.au...

Rich P wrote:

Going along with Steve's line of reasoning I thought I would share how I
set up Excel objects from Access (since this is a significant portion of
my daily routine). I declare an Excel object variable (with a reference
to the respective Excel Object Library, Excel 8.0, 9.0, 10...)

Dim xlObj As Excel.Applicati on, wkbk As Excel.WorkBook
Dim sht As Excel.WorkSheet , rng As Excel.Range
..
Set xlObj = CreateObject("E xcel.Applicaion ")

Steve is correct about the delicateness of the New Keyword in VBA. I
didn't think about that because I have been steering away from using New
in VBA for Office Applications (although New works quite well in a
managed environment like VB.Net or C# - where the compiler will slap
your hand right away if you declare something incorrectly), but I
reserve using New for instantiating custom objects within Access
(objects created with a class module) because here you don't have to
worry about running an external process. It is internal to Access.

Rich

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks very much for your input.

I actually only open one workbook per execution of this procedure and as
such I only need to call Quit once at the end of the process. I have
followed your advice above and unfortunately this has not woked for me.

Also, in your previous post your advised that I check Task Manager for
ALL possible instances of Excel running. Since including your above
recommendation I have found there to NO instances of Excel post code
execution.

I'm quite confused :)

<snipped code>

FWIW, I had similar problems automating an Excel instance from A97 and AFAIR
it related to parts of the worksheet being protected. I _think_ the code
worked but the repaints were blocked by sheet or cell protection being on,
so the object was never able to complete processing on its event queue, but
it was a loooong time ago and I may misremember. I know I had to test for
protection and clear/reset it as I wandered through the sheet.

HTH
Doug

--
Remove the blots from my address to reply

[Mr. Smith]

Doug Hutcheson wrote:

"Mr. Smith" <Mr.Smith@Large > wrote in message
news:40******** *************** @news.syd.swift dsl.com.au...

Rich P wrote:

Going along with Steve's line of reasoning I thought I would share how I
set up Excel objects from Access (since this is a significant portion of
my daily routine). I declare an Excel object variable (with a reference
to the respective Excel Object Library, Excel 8.0, 9.0, 10...)

Dim xlObj As Excel.Applicati on, wkbk As Excel.WorkBook
Dim sht As Excel.WorkSheet , rng As Excel.Range
..
Set xlObj = CreateObject("E xcel.Applicaion ")

Steve is correct about the delicateness of the New Keyword in VBA. I
didn't think about that because I have been steering away from using New
in VBA for Office Applications (although New works quite well in a
managed environment like VB.Net or C# - where the compiler will slap
your hand right away if you declare something incorrectly), but I
reserve using New for instantiating custom objects within Access
(objects created with a class module) because here you don't have to
worry about running an external process. It is internal to Access.

Rich

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks very much for your input.

I actually only open one workbook per execution of this procedure and as
such I only need to call Quit once at the end of the process. I have
followed your advice above and unfortunately this has not woked for me.

Also, in your previous post your advised that I check Task Manager for
ALL possible instances of Excel running. Since including your above
recommendatio n I have found there to NO instances of Excel post code
execution.

I'm quite confused :)

<snipped code>

FWIW, I had similar problems automating an Excel instance from A97 and AFAIR
it related to parts of the worksheet being protected. I _think_ the code
worked but the repaints were blocked by sheet or cell protection being on,
so the object was never able to complete processing on its event queue, but
it was a loooong time ago and I may misremember. I know I had to test for
protection and clear/reset it as I wandered through the sheet.

HTH
Doug

--
Remove the blots from my address to reply

Yeah thanks for that, I've in fact had the same issue a long time ago
too when I was opening a preformatted template workbook and saving to a
new file but in this instance I am creating workbooks/sheets from
scratch so there is no protection on anything and nothing to reset or
clear. Bugger on a problem this one.

Thanks again anyway.

Mr. Smith.

[Bri]

Mr. Smith,

I don't know if this is your problem, but Access (VBA?) can be a little
sloppy with releasing variables when they go out of scope so its a good
idea to both obj.CLOSE and SET obj = Nothing all object variables. If a
variable doesn't get released then it can cause the ap to not be able to
close. A brief look at your code comes up with the following:

Function ExportAmenityRa tingReport
- wbknew (Workbook variable) set to Nothing then you have
ActiveWorkbook. Close. How about instead:
wbknew.Close
Set wbknew = Nothing
- Recordset variable declared but not set to Nothing
- Database variable declared but not set to Nothing

Function ExportCondition ReportData
- Recordset variable declared but not set to Nothing
- Database variable declared but not set to Nothing

Function WriteAmenityEle mentSummary
- Recordset variable declared but not closed or set to Nothing
- Database variable declared but not closed or set to Nothing
Hope this helps.

Bri

[Rich P]

Hello again,

As for my previous suggestion, I am using CreateObject kind of like
Shell to open up a workbook but I use CreateObject because then I can
use Excel object to perform various Excel Functions. I pass data to
Excel first using ADO and then open up the workbook (Excel is not
running while I am initially passing the data). To use ADO you need to
have a pre-existing workbook. Here is a sample:

Dim DB As Database, RS1 As Recordset
Dim cn As New ADODB.Connectio n, RS As New ADODB.Recordset
Dim i As Integer, j As Integer, k As Integer, m As Integer
RS.CursorLocati on = adUseClient
cn.Mode = adModeReadWrite 'this mode very important
cn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & strSourcePath & _
"Extended Properties=""Ex cel 8.0;HDR=NO;"""

'the connection string has to be exactly in this format
'strSorucePath is the source workbook
'C:\somedir\tes tBook.xls; for example - note - you must
'have semicolon ; at end of source string - there must be a
'semicolon next to Extended -
'data source=C:\somed ir\testbook.xls ;Extended...

Set DB = CurrentDb
Set RS1 = DB.OpenRecordse t("AccessTable" )
Do While Not RS1.EOF
strSql = "SELECT * FROM [Sheet1$A" & j & ":G" & j & "]"
RS.Open strSql, cn, adOpenDynamic, adLockPessimist ic, adCmdText
For k = 1 To RS1.Fields.Coun t - 1
RS(k - 1) = RS1(k)
Next
RS.Update
RS.Close
j = j + 1
RS1.MoveNext
Loop

Note the format of the sql string

Select * From [Sheet1$A1:G1]

I use j because you can only write one row at a time to Excel using ADO.
You need the square brackets, don't forget the $ sign. You can start
anywhere you want:

Select * From [Sheet3$F10:AZ10]

but you can only be on one row at a time.

Here are a couple of gotcha's using ADO with Excel. 1) you can only
write one row at a time. Thus I reset each row in Excel in the Sql
statement using my counter j. This is still 1000 times faster than
writing cell by cell. Plus, Excel does not have to be running. You
could have Excel running and the workbook open, but that would slow ADO
way down. Works way faster if Excel is not running.

2) You have to prime the Excel sheet that will receive the data by
having fake data in each cell that will receive data. What I do is to
write lkj;lkj in each cell for say 500 rows. I may only fill 150 of the
rows. Then I invoke CreateObject("E xcel.Applicatio n") and the the
Range.Delete method to get rid of the extra rows (use the macro recorder
to get the Excel code for that - highlight a bunch of rows - right-click
on the edge of the selection and click on delete).

I have been using this technique for years without any problems. Hope
it works for you. Here is an article from msdn Knowledge base that may
provide some more insight:

http://support.microsoft.com/default...8973&Product=a
do

Rich
Rich

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Matthew Sullivan]

Mr Smith:

I had this problem a couple of years ago, and was similarly
frustrated. My issue turned out to be "implicit references".

To find prior threads on this topic, do a Google Groups search on
this group with the following keywords:
Excel implicit references

This brings up 22 threads discussing the problem, including the one
from 12/05/2001 where Dimitri "THE MAN" Furman originally offered up
the solution to my problem.

If those threads don't help, post a reply to this (with ALL of your
Excel automation code) and I'll take a look and see if I can help.

Good luck!

-Matt

keywords for future searches:
------------------------------
excel
automate automation automating automated
task manager
still running
implicit reference
process

On Mon, 21 Jun 2004 20:38:34 +1000, "Mr. Smith" <Mr.Smith@Large >
wrote:

Hello all,

My code can successfully open, write to, format and save several
worksheets in a workbook then save it by a given name, close and quit excel.

My problem is that if I try and do it again, Excel hangs. OR if I open
Excel again (say from a desktop icon) before I close Access, Excel
hangs. (this has happened for both 97 & 2000 for me)