Error 91 - Object variable or With block variable not set

[MarkBStewart]

I am running a simple excel app within Access. I am opening a new workbook, adding data, formatting the sheet ect. It works quite well the first time I run it (click event). The next time it fails with "Error 91 - Object variable or With block variable not set". Here is the first part of the code and the location where it fails on the second try:

Private Sub CalcPercent_Click()

DELCLARATIONS:

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Set xlWB = Nothing
Set xlWS = Nothing
Set xlApp = Nothing

GETFILE:

DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, 8, "Huawei Pricing", "c:\Caten_McGuire\Export\3G_Supplier_Pricing_Expor t"
DoCmd.SetWarnings True

EXCELFORMAT:

Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\Caten_McGuire\Export\3G_S upplier_Pricing_Export")
Set xlWS = xlWB.Worksheets("Huawei_Pricing")

INITIALFORMAT:
With xlWS
.Range("A1:AI1").Select
End With


With Selection.Interior **FAILS HERE**
.Pattern = xlSolid
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.499984740745262
End With

At the end of the routine I close this way:

Set xlWB = Nothing
Set xlWS = Nothing
Set xlApp = Nothing

[dsatino]

Weird... I had the same issue yesterday pertaining to 'with' and excel from within Access.

In any case, I think your failure is simpler. I think you need to move your "with Selection...." inside the 'With xlWS'.

 
With xlWS.Range("A1:AI1").Select
With Selection.Interior 
.Pattern = xlSolid
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.499984740745262
End WithEnd With