Runtime error 1004 when re-execute excel VB code 1

[kihunterk]

I have an procedure that exports and formats data from Access into an Excel spreadsheet. The programs works fine the first time I execute the program. Subsequent executions yield the following error at the Range statement. Range("A1:G10").Select

Run-time error '1004'
Method Range of object _Global failed

To run it again, I have to press Reset in the toolbar. Wha can I do to get around this problem.

Public Sub ExportXLS4()
Dim oexcel As Object
Dim oBook As Object
Dim osheet As Object
Set oexcel = CreateObject("excel.application")
Set oBook = oexcel.Workbooks.Add
Set osheet = oBook.Worksheets("Sheet1")
Dim icounter As Integer
Dim aRange As Variant

Dim intloop As Integer, startrange As Integer
Dim strvar As String
Dim Loopvar As Integer
Dim tempstr As String
Dim db As Database
Dim RS As Recordset
Dim Qrystr As String
Dim strFile As String
Set db = DBEngine(0)(0)
'Set oBook = oexcel.Workbooks.Open ("c:\temp\test2.xls", ReadOnly:=False)

osheet.Application.ScreenUpdating = True
osheet.Application.Visible = True

'Set Page Setup

With osheet.PageSetup
.LeftHeader = ""
If Forms!dateselection!ReportType = 1 Then
.CenterHeader = "&""Arial,Bold""&14IT Management of Change Meeting Agenda"
Else
.CenterHeader = "&""Arial,Bold""&14IT Management of Change Meeting Minutes"
End If
'.RightHeader = ""
'.LeftFooter = ""
'.CenterFooter = ""
.RightFooter = "&P of &N"
.LeftMargin = 0.5
.RightMargin = 0.5
'.TopMargin = 1
.BottomMargin = 0.75
'.HeaderMargin = 0.5
.FooterMargin = 0.5
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 99
.PrintErrors = xlPrintErrorsDisplayed
End With

'Form Header Section
With osheet
.Cells(1, 1) = "Meeting Date:"
.Cells(1, 2) = Date
.Cells(1, 2).NumberFormat = "dd-mmm-yy"
.Cells(1, 3) = "Meeting Time:"
.Cells(1, 4) = "10:00-11:00 am"

.Cells(2, 1) = "Facilitator:"
.Cells(2, 2) = Forms!dateselection!Facilitator
.Cells(2, 3) = "Note Taker:"
.Cells(2, 4) = Forms!dateselection!NoteTaker

.Cells(3, 1) = "OHC Conference Room: 11th Floor-Room 122"
.Cells(4, 1) = "HDC Conference Room: 6th Floor-Room 248"
.Cells(4, 3) = "Instant Meeting Room:"
.Cells(4, 4) = "(877) 973-4202"

.Cells(5, 2) = "Exchange Conferencing Link:"
.Cells(5, 3) = "Participant Passcode:"
.Cells(5, 4) = "736711"

.Cells(6, 3) = "Leader Passcode"
.Cells(6, 4) = "391179"
.Cells(7, 1) = "Attendees:"
If Forms!dateselection!ReportType <> 1 Then
.Cells(7, 2) = Forms!dateselection!MeetingGuests
End If

.Cells(10, 1) = "IT MOC NUMBER"

.Cells(10, 2) = "Brief Description of Change"
.Cells(10, 3) = "ITMOC Requestor"
.Cells(10, 4) = "ITPC Analyst"
.Cells(10, 5) = "SCHEDULED DATES"

.Cells(10, 7) = "STATUS"

.Columns("A:A").ColumnWidth = 15
.Columns("B:B").ColumnWidth = 75
.Columns("C:C").ColumnWidth = 21
.Columns("D").ColumnWidth = 21
.Columns("E:E").ColumnWidth = 13
.Columns("F:F").ColumnWidth = 13
.Columns("G:G").ColumnWidth = 25
End With

Range("A1:G10").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

oBook.Close
oexcel.Quit
Set osheet = Nothing
Set oBook = Nothing

End Sub

 

[PHV]

Replace this:
Range("A1:G10").Select
Selection.Font.Bold = True
With Selection
with this:
With osheet.Range("A1:G10")
.Font.Bold = True

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

 

[kihunterk]

I changed the code as you suggested and it worked perfectly. Thanks