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:=strDestination, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

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.Application


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).ColumnWidth = 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.

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!

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.Application
Set appExcel = New Excel.Application


On Mon, 21 Jun 2004 20:38:34 +1000, "Mr. Smith" <Mr.Smith@Large> wrote:
[color=blue]
>
>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:=strDestination, _
> FileFormat:=xlNormal, Password:="", WriteResPassword:="",
> ReadOnlyRecommended:=False, CreateBackup:=False
>
> 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.Application
>
>
>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).ColumnWidth = 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.[/color]

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.Application, wkbk As Excel.WorkBook
Dim sht As Excel.WorkSheet, rng As Excel.Range
...
Set xlObj = CreateObject("Excel.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!

Rich P wrote:[color=blue]
> 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.Application, wkbk As Excel.WorkBook
> Dim sht As Excel.WorkSheet, rng As Excel.Range
> ..
> Set xlObj = CreateObject("Excel.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![/color]


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 ExportAmenityRatingReport(strSQL As String) As Boolean
On Error GoTo eh_ExportAmenityRatingReport

Dim dbLocal As Database
Dim rst As Recordset
Dim wbknew As Excel.Workbook, wksnew As Excel.Worksheet
Dim appExcel As Excel.Application
Dim strCurrCondition As String
Dim strBuilding As String
Dim intRow As Integer
Dim intStartRow As Integer
Dim intElementCount As Integer
Dim intRatingElementCount
Dim intTotalElementCount As Integer
Dim intBuildingCount As Integer
Dim strDestination As String

ExportAmenityRatingReport = True

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

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

If strSQL <> "" Then
strSQL = "SELECT * FROM qryBuildingAmenityElementRatings WHERE
" & strSQL
Else
strSQL = "qryBuildingAmenityElementRatings"
End If

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

'set up Workbook
Set appExcel = CreateObject("Excel.Application")
Set wbknew = appExcel.Workbooks.Add
Set wksnew = wbknew.Worksheets.Add
wksnew.Name = "Raw Data"
appExcel.Visible = True

WriteRawData wksnew, rst

Set wksnew = Nothing
Set wksnew = wbknew.Worksheets.Add
wksnew.Name = "Rating Summary"
appExcel.Visible = True

WriteAmenityElementSummary wksnew

Set wksnew = Nothing
Set wksnew = wbknew.Worksheets.Add
wksnew.Name = "Amenity Ratings"
appExcel.Visible = True

WriteHeader wksnew
rst.MoveFirst
intRow = 4
intTotalElementCount = 0

Do While Not rst.EOF

wksnew.Range(Cells(intRow, 1), Cells(intRow, 14)).select
Call SetEdgeBottomBorder

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

strCurrCondition = rst!BuildingAmenityElementCondition
intBuildingCount = 0
intRatingElementCount = 0

Do While rst!BuildingAmenityElementCondition = strCurrCondition

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

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

Do While rst![Building Name] = strBuilding

intRow = intRow + 1

wksnew.Cells(intRow, 4) = rst!AmenityElementCategory
wksnew.Cells(intRow, 7) = rst!AmenityElementCategorySub
wksnew.Cells(intRow, 11) =
rst!BuildingAmenityElementEstLife
wksnew.Range(Cells(intRow, 4), Cells(intRow, 11)).select
Call SetFont(False, False, 9)
wksnew.Cells(intRow, 11).select
Selection.HorizontalAlignment = xlCenter

intElementCount = intElementCount + 1
intRatingElementCount = intRatingElementCount + 1

rst.MoveNext
If rst.EOF Then Exit Do

Loop

intRow = intRow + 1
wksnew.Cells(intRow, 3) = "Element Total: " &
intElementCount & " elements rated."
wksnew.Cells(intRow, 3).select
Call SetFont(True, False, 12)
wksnew.Range(Cells(intRow, 3), Cells(intRow, 7)).select
Call SetEdgeTopBorder
Call SetEdgeBottomBorder

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

Loop

intTotalElementCount = intTotalElementCount + intRatingElementCount

wksnew.Cells(intRow, 2) = "Rating Total: " & intElementCount &
" elements rated."
wksnew.Cells(intRow, 2).select
Call SetFont(True, False, 9)
wksnew.Range(Cells(intRow, 1), Cells(intRow, 14)).select
Call SetEdgeTopBorder
Call SetEdgeBottomBorder
intRow = intRow + 2

If rst.EOF Then Exit Do

Loop

intRow = intRow + 1
wksnew.Range(Cells(intRow, 1), Cells(intRow, 14)).select
Call SetEdgeTopBorder
Call SetEdgeBottomBorder

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

With ActiveWindow
.Zoom = 100
.DisplayGridlines = False
.DisplayHeadings = False
End With

wksnew.PageSetup.Orientation = xlLandscape

Set wbknew = Nothing
Set wksnew = Nothing

ActiveWorkbook.SaveAs Filename:=strDestination, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWorkbook.Close
appExcel.Quit

Set appExcel = Nothing
rst.Close
dbLocal.Close

Exit Function

eh_ExportAmenityRatingReport:
ExportAmenityRatingReport = False
End Function


Function ExportConditionReportData() 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.Application
Dim intRow As Integer
Dim i As Integer
Dim strDestination As String
Dim fld As Field

ExportConditionReportData = True

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

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

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tempConditionExport"
DoCmd.OpenQuery "qryConditionExport"

'open data table
Set dbLocal = CurrentDb()
Set rst = dbLocal.OpenRecordset("tempConditionExport", dbOpenSnapshot)

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

'set up Workbook
Set wbknew = appExcel.Workbooks.Add
Set wksnew = wbknew.Worksheets.Add
wksnew.Name = "Condition Report Raw Data"
appExcel.Visible = True

rst.MoveFirst
intRow = 1
i = 1

For Each fld In rst.Fields

wksnew.Cells(intRow, 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(intRow, i) = fld.Value
i = i + 1
Next fld

.MoveNext
intRow = intRow + 1

Loop

End With

Set wbknew = Nothing
Set wksnew = Nothing

ActiveWorkbook.SaveAs Filename:=strDestination, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

'ActiveWorkbook.Close
'appExcel.Quit

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

rst.Close
dbLocal.Close

Exit Function

eh_ExportConditionReportData:
ExportConditionReportData = 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(strFont) Or strFont = "", "Times New
Roman", strFont)
.Size = intSize
End With

End Sub

Sub SetInteriorBorder()
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeLeftBorder()
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeRightBorder()
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeTopBorder()
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeBottomBorder()
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeLeftBorderHeavy()
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeRightBorderHeavy()
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeTopBorderHeavy()
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetEdgeBottomBorderHeavy()
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub

Sub SetSurroundHeavy()
Call SetEdgeLeftBorderHeavy
Call SetEdgeRightBorderHeavy
Call SetEdgeTopBorderHeavy
Call SetEdgeBottomBorderHeavy
End Sub

Sub SetSurround()
Call SetEdgeLeftBorder
Call SetEdgeRightBorder
Call SetEdgeTopBorder
Call SetEdgeBottomBorder
End Sub


Function WriteHeader(wksnew As Worksheet) As Integer

Columns(1).ColumnWidth = 1
Rows(1).RowHeight = 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(Cells(1, 1), Cells(1, 14)).select
Call SetEdgeBottomBorder

End Function


Sub WriteFooter(intRow As Integer, wksnew As Worksheet, intStartRow As
Integer)

'writes and formats the subtotals for each Channel
intRow = intRow + 1
wksnew.Cells(intRow, 5) = "Sub Total"
wksnew.Cells(intRow, 5).select
Call SetFont(True, False, 10)
wksnew.Cells(intRow, 6).Value = "=Sum(F" & intStartRow & ":F" &
intRow - 2 & ")"
wksnew.Cells(intRow, 5).select
Call SetFont(True, False, 10)
Selection.NumberFormat = "$##,##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.HorizontalAlignment = xlCenter

End Sub


Function WriteRawData(Wks As Worksheet, rst As Recordset)

Dim intRow As Integer

intRow = 1

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

intRow = 2

With rst

.MoveFirst

Do While Not .EOF

Wks.Cells(intRow, 1) = !BuildingAmenityElementCondition
Wks.Cells(intRow, 2) = ![Building Name]
Wks.Cells(intRow, 3) = !AmenityElementCategory
Wks.Cells(intRow, 4) = !AmenityElementCategorySub
Wks.Cells(intRow, 5) = !BuildingAmenityElementEstLife

.MoveNext
intRow = intRow + 1

Loop

End With

End Function


Function WriteAmenityElementSummary(Wks As Worksheet)

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

Columns(1).ColumnWidth = 1
Rows(1).RowHeight = 7.57

intRow = 2

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

intRow = 4

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

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

Set Db = CurrentDb()
Set rst = Db.OpenRecordset("qryAmenityElementRatingSummary",
dbOpenSnapshot)

intRow = intRow + 1

With rst

.MoveFirst

Do While Not .EOF

Wks.Cells(intRow, 2) = !AmenityElementCategory
Wks.Cells(intRow, 3) = !SumOfRating1
Wks.Cells(intRow, 4) = !SumOfRating2
Wks.Cells(intRow, 5) = !SumOfRating3
Wks.Cells(intRow, 6) = !SumOfRating4
Wks.Cells(intRow, 7) = !SumOfRating5
Wks.Cells(intRow, 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 SetSurroundHeavy

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

intRow = intRow + 3

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

intRow = intRow + 2
intNextSet = intRow

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

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

Set Db = CurrentDb()
Set rst = Db.OpenRecordset("qryAmenitySubElementRatingSummar y",
dbOpenSnapshot)

intRow = intRow + 1

With rst

.MoveFirst

Do While Not .EOF

Wks.Cells(intRow, 2) = !AmenityElementCategory
Wks.Cells(intRow, 3) = !AmenityElementCategorySub
Wks.Cells(intRow, 4) = !Rating1
Wks.Cells(intRow, 5) = !Rating2
Wks.Cells(intRow, 6) = !Rating3
Wks.Cells(intRow, 7) = !Rating4
Wks.Cells(intRow, 8) = !Rating5
Wks.Cells(intRow, 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 SetSurroundHeavy
Wks.Range(Cells(intNextSet, 3), Cells(intRow, 3)).select
Call SetFont(True, False, 9)
Call SetSurroundHeavy

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

Wks.Cells(1, 1).select

End Function

"Mr. Smith" <Mr.Smith@Large> wrote in message
news:40d783db$0$29888$61ce578d@news.syd.swiftdsl.c om.au...[color=blue]
> Rich P wrote:[color=green]
> > 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.Application, wkbk As Excel.WorkBook
> > Dim sht As Excel.WorkSheet, rng As Excel.Range
> > ..
> > Set xlObj = CreateObject("Excel.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![/color]
>
>
> 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 :)
>[/color]
<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

Doug Hutcheson wrote:[color=blue]
> "Mr. Smith" <Mr.Smith@Large> wrote in message
> news:40d783db$0$29888$61ce578d@news.syd.swiftdsl.c om.au...
>[color=green]
>>Rich P wrote:
>>[color=darkred]
>>>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.Application, wkbk As Excel.WorkBook
>>>Dim sht As Excel.WorkSheet, rng As Excel.Range
>>>..
>>>Set xlObj = CreateObject("Excel.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![/color]
>>
>>
>>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 :)
>>[/color]
>
> <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
>
>[/color]


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.

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 ExportAmenityRatingReport
- 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 ExportConditionReportData
- Recordset variable declared but not set to Nothing
- Database variable declared but not set to Nothing

Function WriteAmenityElementSummary
- Recordset variable declared but not closed or set to Nothing
- Database variable declared but not closed or set to Nothing


Hope this helps.

Bri

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.Connection, RS As New ADODB.Recordset
Dim i As Integer, j As Integer, k As Integer, m As Integer
RS.CursorLocation = adUseClient
cn.Mode = adModeReadWrite 'this mode very important
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strSourcePath & _
"Extended Properties=""Excel 8.0;HDR=NO;"""

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

Set DB = CurrentDb
Set RS1 = DB.OpenRecordset("AccessTable")
Do While Not RS1.EOF
strSql = "SELECT * FROM [Sheet1$A" & j & ":G" & j & "]"
RS.Open strSql, cn, adOpenDynamic, adLockPessimistic, adCmdText
For k = 1 To RS1.Fields.Count - 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("Excel.Application") 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!

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:
[color=blue]
>
>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)[/color]

G'day Matthew. Thanks for the lead. It has worked to some extent but
not completely..... Yet.

Where previously, my code was getting stuck due to the absence of
explicit references as per the following example:

Columns(1).ColumnWidth = 1
Rows(1).RowHeight = 7.57

This works

wks.Columns(1).ColumnWidth = 1
wks.Rows(1).RowHeight = 7.57

where wks is a worksheet object, as recommended.

However, the code still creates an application or object error on this
line during the second execution of the procedure

wks.Range(Cells(intRow, 2), Cells(intRow, 8)).Select

I have also tried things like

AppExcel.Worksheets(wks.Name).Range(Cells(intRow, 2), Cells(intRow,
8)).Select

without success. I have tried a number of ways to exlicitly reference
this range for selection but keep getting klobbered.

Any ideas?




Matthew Sullivan wrote:
[color=blue]
> 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:
>
>[color=green]
>>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)[/color]
>
>[/color]

Mr Smith:

That "Cells" thing looks like an implicit reference.

A quick look at the Excel help indicates it might need to be qualified
with a Worksheet reference.

-Matt



On Thu, 24 Jun 2004 09:42:10 +1000, "Mr. Smith" <Mr.Smith@Large>
wrote:[color=blue]
>However, the code still creates an application or object error on this
>line during the second execution of the procedure
>
> wks.Range(Cells(intRow, 2), Cells(intRow, 8)).Select
>
>I have also tried things like
>
> AppExcel.Worksheets(wks.Name).Range(Cells(intRow, 2), Cells(intRow,
>8)).Select
>
>without success. I have tried a number of ways to exlicitly reference
>this range for selection but keep getting klobbered.
>
>Any ideas?[/color]