Excel from Access problem

[babyatx13 via AccessMonster.com]

I am opening Excel form Access and putting data into the Excel spreadsheet
while formatting as well. At the same time I am creating an Access Report. I
leave spreadsheet open so I can see the results, at this point the work book
has not been saved programmically or otherwise and the end result is perfect
in both the report and the spreadsheet. This works great one time. If I leave
open Access and close Excel and run the code again none of the formatting
works in Excel but the report looks the same in Access.
First I close Excel then I close the report.
When I exit the report I set these
Set xlRng = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
How do I get the code to work again without closing Excel programmically or
shutting down Access and opening it back up to run the code again?
my code
Public xlApp As Excel.Application
Public xlWb As Excel.Workbook
Public xlWs As Excel.Worksheet
Public xlRng As Excel.Range
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Add(1)
Set xlWs = xlWb.Sheets("Sheet1")
ActiveWindow.Zoom = 60
Columns("A:A").ColumnWidth = 1
Columns("B:B").ColumnWidth = 37
Columns("C:I").ColumnWidth = 45
Set xlRng = xlWs.Range("B2")

Some programming stuff

Blablabla
End sub

 

[OssieMac]

hello Blablabla,

Without actually testing with your report etc I think that you might be
relying on the worksheet being active with the following code.
ActiveWindow.Zoom = 60
Columns("A:A").ColumnWidth = 1
Columns("B:B").ColumnWidth = 37
Columns("C:I").ColumnWidth = 45

You should also test for xl being open with the GetObject. Try the following
code and see if it works.

Sub OpenExcel()

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then 'Word isn't already running
On Error GoTo 0 'turn off as soon as possible
Set xlApp = CreateObject("Excel.Application")
End If


xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Add(1)
Set xlWs = xlWb.Sheets("Sheet1")

xlApp.ActiveWindow.Zoom = 60 'specify the application to zoom

'Specify the sheet to format and the sheet to assign to xlRng
With xlWs
.Columns("A:A").ColumnWidth = 1
.Columns("B:B").ColumnWidth = 37
.Columns("C:I").ColumnWidth = 45
Set xlRng = .Range("B2")
End With

End Sub

 

[babyatx13 via AccessMonster.com]

Everything works correctly the first time through. code as is, I want to know
why it does not work any time after that.
i think it may be a 2007 issue because similar code works fine in 2003 every
time.

K Board

 

[babyatx13 via AccessMonster.com]

thank you
xlApp.ActiveWindow.Zoom = 60
With xlWs

.Columns("A:A").ColumnWidth = 1
.Columns("B:B").ColumnWidth = 37
.Columns("C:I").ColumnWidth = 45
Set xlRng = .Range("B2")
End With

works fine
sub routines not formatting though.

K Board

 

[babyatx13 via AccessMonster.com]

sorry Sub routines
Set xlRng = xlRng.Offset(1, 0)
xlRng.Value = "NAME"
xlRng.Select
B16Sans
Range(xlRng, xlRng.Offset(-1, 0)).Select
xlRng.Activate
Med4SelDay

Public Sub B16Sans()
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Interior.Pattern = xlSolid
.Font.Name = "MS Sans Serif"
.Font.Size = 16
.Font.Bold = True
End With
End Sub

Public Sub Med4SelDay()
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
MedEdgeLeft
MedEdgeTop
MedEdgeBottom
MedEdgeRight
End Sub

Public Sub MedEdgeLeft()
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
End Sub
and so on

thank you
xlApp.ActiveWindow.Zoom = 60
With xlWs

.Columns("A:A").ColumnWidth = 1
.Columns("B:B").ColumnWidth = 37
.Columns("C:I").ColumnWidth = 45
Set xlRng = .Range("B2")
End With

works fine
sub routines not formatting though.

K Board

hello Blablabla,

[quoted text clipped - 33 lines]

End Sub

 

[babyatx13 via AccessMonster.com]

got it.
Thanks for the nudge OssieMac.

K Board

sorry Sub routines
Set xlRng = xlRng.Offset(1, 0)
xlRng.Value = "NAME"
xlRng.Select
B16Sans
Range(xlRng, xlRng.Offset(-1, 0)).Select
xlRng.Activate
Med4SelDay

Public Sub B16Sans()
xlRng.HorizontalAlignment = xlCenter
xlRng.VerticalAlignment = xlBottom
xlRng.Interior.Pattern = xlSolid
xlRng.Font.Name = "MS Sans Serif"
xlRng.Font.Size = 16
xlRng.Font.Bold = True

End Sub

Public Sub Med4SelDay()
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
MedEdgeLeft
MedEdgeTop
MedEdgeBottom
MedEdgeRight
End Sub

Public Sub MedEdgeLeft()
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
End Sub
and so on

thank you
xlApp.ActiveWindow.Zoom = 60

[quoted text clipped - 15 lines]

 

[OssieMac]

Pleased for you that you have got it sorted. I have been away and hense no
replies to your earlier posts.

--
Regards,

OssieMac

got it.
Thanks for the nudge OssieMac.

K Board

sorry Sub routines
Set xlRng = xlRng.Offset(1, 0)
xlRng.Value = "NAME"
xlRng.Select
B16Sans
Range(xlRng, xlRng.Offset(-1, 0)).Select
xlRng.Activate
Med4SelDay

Public Sub B16Sans()
xlRng.HorizontalAlignment = xlCenter
xlRng.VerticalAlignment = xlBottom
xlRng.Interior.Pattern = xlSolid
xlRng.Font.Name = "MS Sans Serif"
xlRng.Font.Size = 16
xlRng.Font.Bold = True

End Sub

Public Sub Med4SelDay()
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
MedEdgeLeft
MedEdgeTop
MedEdgeBottom
MedEdgeRight
End Sub

Public Sub MedEdgeLeft()
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
End Sub
and so on

thank you
xlApp.ActiveWindow.Zoom = 60

[quoted text clipped - 15 lines]

End Sub