473,687 Members | 2,961 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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


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.
Nov 13 '05 #1
11 4046
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!
Nov 13 '05 #2
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.


Nov 13 '05 #3
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!
Nov 13 '05 #4
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

Nov 13 '05 #5
"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
Nov 13 '05 #6
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.
Nov 13 '05 #7
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

Nov 13 '05 #8
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!
Nov 13 '05 #9
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)


Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2310
by: Jim S | last post by:
To my surprise and chagrin, newer versions of Access have disabled the functionality that lets users change the data in linked tables that point to a range in an Excel workbook. This is "because of legal issues..." according to Office Support. I have other issues, specifically a couple of applications that do things such as calculate price updates in Access and read them back by means of update queries into the spreadsheets our marketing...
15
2428
by: sparks | last post by:
We get more and more data done in excel and then they want it imported into access. The data is just stupid....values of 1 to 5 we get a lot of 0's ok that alright but 1-jan ? we get colums that are formatted for number and then half way down they are changed to text. OR the famous ok now everything in red is ---- and everything in blue is---------. WTF are these people thinking?
19
12755
by: wreckingcru | last post by:
I'm trying to output a SQL query that is constructed thru my VB.net GUI into an excel file. Here is the code I'm using: 'Sqlstmt is the SQL query statement 'Conn is the SQL Connection object cmd = New SqlCommand(Sqlstmt, Conn) datareader = cmd.ExecuteReader()
1
4612
by: Mitch | last post by:
I am using Access to create an Excel spreadsheets with graphs related to rows on the sheet1 to the graph on sheet2. I am using the same data but different subsets of the data to make different spreadsheets for different groups. The spreadsheet formats are the same for each of the different groups. So I am using a loop to requery the data for the different groups and create a new spreadsheet for each group. The three graphs on sheet 2...
5
17678
by: joshua.nicholes | last post by:
I have an access database that consists of two tables.A data collection table and a species list table. The data collection table has about 1500 records in it and the species list has about 600. The species list has 7 fields the first is a four digit unique identifier (species) it is set as the primary key. I have created a relationship to the data collection table which also has a species field (4 digit id). In my form I have the species...
4
4394
by: Suzette | last post by:
I have an excel file that automatically updates when opened, pulling information from an Access database into into various pivot tables. I created a macro in Access to perform various necessary functions with the data. I want to be able to open an Access form that I created, and then hit a button that will run the macros and that will open the excel database. The only problem that I am having is that when the excel file opens and tries...
0
767
by: DrewYK | last post by:
I have the project that may never end in front of me. I am creating a routine that will take SpreadSheets from Excel and bring them into Access. I am not using any "DoCmd"s because the goal is for the import code to be moved to a stand alone VB app which will use the Access DB as a workspace to process the data from the spreadsheets. Quite honestly, done right this may not even require Access or Excel to be on the users machine. ...
1
5157
by: thadson | last post by:
Hi, I'm trying to import specific cells from MS Excel 2000 spreadsheets to MS Access 2000 tables then move the spreadsheets to a different directory. I'm very new to this and I'm having trouble to implement this. I have worked out so far the code to import certain cells into 1 table, but I do not know how to import some other cells into another tables so the data would be connected and remain together. So lets say that I have 2 tables...
1
1691
by: bardwell | last post by:
We have created 4 complex Excel spreadsheets with computations (no data) that we don't want to reprogram in Access. I am hoping we can open the Excel spreadsheets, and use them in the Access program as calculators: write to a few input cells, and read back the result from the output cell. These spreadsheets need to be packaged with a runtime Access program, and the exchange between Access and Excel has to happen in the background without the...
0
8590
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8527
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9069
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8947
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8781
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7618
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4321
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2214
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1947
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.