Error on second run of code

[stickers]

Hi all,

I posted this in the VBA forum yesterday and got some good help there, but the problem is not fixed. I have an Access query which, on a button click, is being exported to Excel. However I wanted the Excel sheet to be formatted in a certain way. I've got the code so that it works OK once, but if you try to run it again, I get one of a number of errors, but usually 1004: Method 'cells' of object '_Global' failed.

I've looked really hard at my code and I think it's fine - well it must be, to work once, but there is something wierd going on where it will only work again if I shut the db down and start it up again, or compact it.

Has anyone got any general ideas on what might be happening? I'm posting the code below. I don't think it's a specific Excel VBA coding issue, which is why I'm trying in this forum too....

Option Compare Database
Option Explicit
Dim xlProjectCosts As Excel.Application

Public Sub CreateLifeCycleReport(ProjectName As String, QueryName As String)

Dim cnCurrent As ADODB.Connection
Dim rsMatrix As ADODB.Recordset
Dim R As Long
Dim C As Integer
Dim i As Integer
Dim xlProjectCosts As Excel.Application
'Dim mWB As Excel.Workbook
On Error GoTo ErrHandler

Set cnCurrent = CurrentProject.Connection
Set rsMatrix = New ADODB.Recordset

rsMatrix.Open "SELECT * FROM " & QueryName, cnCurrent, adOpenDynamic, adLockPessimistic

Set xlProjectCosts = New Excel.Application
xlProjectCosts.Visible = True
xlProjectCosts.Workbooks.Add
'xlProjectCosts.ActiveWindow.DisplayGridlines = False
'Set mWB = ActiveWorkbook
'Enter details of project, autofit cells and put into bold type
With xlProjectCosts
With .ActiveWorkbook
.ActiveSheet.Cells(1, 1).Value = "Prepared:"
.ActiveSheet.Cells(1, 2).Value = Now()
.ActiveSheet.Columns(2).AutoFit
.ActiveSheet.Cells(3, 1).Value = "Project:"
.ActiveSheet.Cells(3, 2).Value = ProjectName
.ActiveSheet.Columns(2).AutoFit

.ActiveSheet.Range("A1:B3&quot.Font.Bold = True

R = 5

'Enter Field names into row 5 (this is a crosstab query)

For i = 1 To rsMatrix.Fields.Count
.ActiveSheet.Cells(R, i + 2).Value = rsMatrix.Fields(i).Name
.ActiveSheet.Cells(R, i + 2).Font.Bold = True
.ActiveSheet.Columns(i + 2).ColumnWidth = 11.14
Next

'I want a bit of space between headings and the rest of the data so set current row to 7
R = 7
C = rsMatrix.Fields.Count + 2

'copy data from recordset - the queries I'm putting in all work fine

.ActiveSheet.Range("B7&quot.CopyFromRecordset rsMatrix

'the first column has text in so I'd like to leave it out of the formatting
R = .ActiveSheet.Range("C65536&quot.End(xlUp).Row

'select the range with numbers in and format to currency
.ActiveSheet.Range(ActiveSheet.Cells(7, 3), ActiveSheet.Cells(R, C)).NumberFormat = "$#,##0.00"

.ActiveSheet.Columns(3).AutoFit

.ActiveSheet.Range(ActiveSheet.Cells(R, 2), ActiveSheet.Cells(R, C)).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
.ActiveSheet.Cells(R, 2).Font.Bold = True

R = R + 2
For i = 1 To rsMatrix.Fields.Count
.ActiveSheet.Cells(R, i + 2).Value = rsMatrix.Fields(i).Name
.ActiveSheet.Cells(R, i + 2).Font.Bold = True
.ActiveSheet.Columns(i + 2).ColumnWidth = 11.14
Next

rsMatrix.Close
cnCurrent.Close
MsgBox "Remember to save this Excel file", , "Jarvis FFE Database"

Set rsMatrix = Nothing
Set cnCurrent = Nothing
Set xlProjectCosts = Nothing

End With
End With
Exit Sub

ErrHandler:

If Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Number & Err.Description, , "Jarvis FFE Database"
Exit Sub
End If

End Sub

 

[Steve101]

Well, I don't seem to be able to solve all of your problem, but I have re-created the problem and solved it for a simple situation:

[tt]
Public Sub CreateLifeCycleReport()
Dim xlProjectCosts As Excel.Application
Set xlProjectCosts = New Excel.Application

xlProjectCosts.Visible = True
xlProjectCosts.Workbooks.Add
With xlProjectCosts
With .ActiveWorkbook
.ActiveSheet.Cells(1, 1).Value = "Prepared:"
.ActiveSheet.Range("A1:B3&quot.Font.Bold = True

R = .ActiveSheet.Range("C65536&quot.End(xlUp).Row
C = 1

'---------------------------------------------------------------
'An "Offending" line follows (I've commented it out for testing)
'---------------------------------------------------------------
'.ActiveSheet.Range(ActiveSheet.Cells(7, 3), ActiveSheet.Cells(R, C)).NumberFormat = "$#,##0.00"

'----------------------------------------------------------
'Recode it by constructing a string variable with the range
'----------------------------------------------------------
MyCellRange = "A1:G7"
.ActiveSheet.Range(MyCellRange).NumberFormat = "$#,##0.00"

.ActiveSheet.Columns(3).AutoFit
MsgBox "Remember to save this Excel file", , "Jarvis FFE Database"
xlProjectCosts.Workbooks.Close
Set xlProjectCosts = Nothing
End With
End With
End Sub
[/tt]

Note: to keep the above snippet short; I've removed the explicit declaration requirement - this has nothing to do with the solution.

There may still be a problem with the

.ActiveSheet.Range(ActiveSheet.Cells(R, 2), ActiveSheet.Cells(R, C)).Select
With Selection.Borders(xlEdgeTop)

code, which you may have to handle in a similar way.

By the way, I've run into this problem before, a while back; I think its to do with the opening and closing of automation objects; its very parculiar with it; I find that you need to find ways to keep the objects well declared and defined; use them simply, and close them properly.

Help this helps,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[MichaelRed]

Being a big NOT fan of excel in general, I am obviously un-qualified to really give (positive) advice re the use / manipulation of Excel, But I did notice that the declatation:

[tab]Dim xlProjectCosts As Excel.Application

is repeated in the module level declarations. Generally this is considered (at best) poor practice, and should actually give an error?

On the other hand, it is intreresting to understand why you would want to 'publish' a Report from Ms. A. in Excel. I have generally been quite un-happy when asked to do this, as users will (have) altered the report (data) for their own purposes (occassionally with errors in the alterations) and then complained (loudly) that the problem is in the Source (ME!!!). I MUCH prefer to either provide JUST the raw data or an un-alterable version (snapshot or paper), so I am quite curious as to WHY you would provide the elaborate set up / formatting of the data in this manner.

The other "suggestion" I have re the investigation of hte woes would be to (at least temporarily) disable the error trap and just see WHERE the error(s) occur.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[stickers]

Thanks both for your replies. I'm getting there slowly. I've taken out all the error handling and am basically testing it/tweaking it line by line now!!!

MichaelRed - I quite agree with you about Excel vs Access reports, but the application I'm working on is aimed at providing a solution to a very small part of a larger problem. The part that is the larger problem is ALL done in excel - often by people who are not particularly computer literate, and much as I'd love to take it all on and provide a system for the whole thing, given that I only started with Access about 6 months ago, I don't think I'm yet up to it! So whilst my immediate users will be using Access hopefully exclusively, outputs from my system have to be dealt with in various ways by others, who will not be able to cope with an access report.

Believe me it's pretty frustrating for me too!!!