Method 'worksheets' of object '_Global' failed 1

[Ratman11]

Hi
I'm running a piece of code that extracts a query to excel, formats & then mails it out.

The first time I run the report it works fine, but the second time i get the above error.

I have figured out why...excel is still running in the background & if i go to task manager and end the .exe then it will run again.

How can i get my code to do this automatically? The code i currently have is below...


Private Sub Command2_Click()
Dim objex As Excel.Application
Set objex = New Excel.Application

If Nz(WeekNumber, 0) = 0 Then
MsgBox "A valid week number is required"
Else
If Nz(Year, 0) = 0 Then
MsgBox "A valid year is required"
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryRWAReport01", "H:\Service Centre\Reports\Wkly Reports\" & Year & "\wk" & " " & WeekNumber & "\RWAReportwk" & WeekNumber & ".xls", True
End If
End If

'format excel spreadsheet

objex.Workbooks.Open "H:\Service Centre\Reports\Wkly Reports\" & Year & "\wk" & " " & WeekNumber & "\RWAReportwk" & WeekNumber & ".xls"
'objex.Sheets("qryRWAReport01").Activate
'objex.Visible = True
'Workbooks.Application.ScreenUpdating = False
With Worksheets("qryRWAReport01").Cells.Font
.Name = "Arial"
.Size = 9
End With
Worksheets("qryRWAReport01").Columns.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P"
.RightFooter = ""
.LeftMargin = Workbooks.Application.InchesToPoints(0.75)
.RightMargin = Workbooks.Application.InchesToPoints(0.75)
.TopMargin = Workbooks.Application.InchesToPoints(1)
.BottomMargin = Workbooks.Application.InchesToPoints(1)
.HeaderMargin = Workbooks.Application.InchesToPoints(0.5)
.FooterMargin = Workbooks.Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Workbooks.Application.ScreenUpdating = True
Workbooks("RWAReportwk" & WeekNumber & ".xls").Save
Workbooks("RWAReportwk" & WeekNumber & ".xls").Close
objex.Quit
Set objex = Nothing

....then all my mailing code....

Any help would be great, i've looked at other posts but can't seem to figure out a fix...

 

[RoyVidar]

You have a number of implicit referencing of excel objects and properties, which wuold most likely be the culprit. Have a read at Excel automation fails second time code runs

I would start with declaring and instantiating workbook and worksheet objects, and use those in the referencing:

[tt]dim wr as excel.workbook ' or object
dim ws as excel.worksheet ' or object

set wr = objex.Workbooks.Open("H:\Service Centre\Reports\Wkly Reports\"...
set ws = wr.worksheets("qryRWAReport01")[/tt]

Then ensure all references are fully qualified, ie:

[tt] Worksheets("qryRWAReport01").Columns.AutoFit
' becomes
ws.Columns.AutoFit

Range("A2").Select
' becomes
ws.Range("A2").Select

ActiveWindow.FreezePanes = True
' becomes
objex.ActiveWindow.FreezePanes = True
' don't know for sure about this last one, though[/tt]

Roy-Vidar

 

[PHV]

A lot of implicit instantiations of the EXcel.Application object.
objex.Workbooks.Open "H:\Service Centre\Reports\Wkly Reports\" & Year & "\wk" & " " & WeekNumber & "\RWAReportwk" & WeekNumber & ".xls"
'objex.Sheets("qryRWAReport01").Activate
'objex.Visible = True
'[highlight]objex.[/highlight]ScreenUpdating = False
With [highlight]objex.[/highlight]Worksheets("qryRWAReport01").Cells.Font
.Name = "Arial"
.Size = 9
End With
[highlight]objex.[/highlight]Worksheets("qryRWAReport01").Columns.AutoFit
[highlight]objex.[/highlight]Range("A2").Select
[highlight]objex.[/highlight]ActiveWindow.FreezePanes = True
With [highlight]objex.[/highlight]ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
[highlight]objex.[/highlight]ActiveSheet.PageSetup.PrintArea = ""
With [highlight]objex.[/highlight]ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P"
.RightFooter = ""
.LeftMargin = [highlight]objex.[/highlight]InchesToPoints(0.75)
.RightMargin = [highlight]objex.[/highlight]InchesToPoints(0.75)
.TopMargin = [highlight]objex.[/highlight]InchesToPoints(1)
.BottomMargin = [highlight]objex.[/highlight]InchesToPoints(1)
.HeaderMargin = [highlight]objex.[/highlight]InchesToPoints(0.5)
.FooterMargin = [highlight]objex.[/highlight]InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
[highlight]objex.[/highlight]ScreenUpdating = True
[highlight]objex.[/highlight]Workbooks("RWAReportwk" & WeekNumber & ".xls").Save
[highlight]objex.[/highlight]Workbooks("RWAReportwk" & WeekNumber & ".xls").Close
objex.Quit

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ratman11]

Hi
Thanks for the responses, tried the first code but couldn't get it to work.

Copied PH's code in place of mine & now it works everytime, like a charm!

Thanks guys!