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_Cli ck()

DELCLARATIONS:

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

GETFILE:

DoCmd.SetWarnin gs False
DoCmd.TransferS preadsheet acExport, 8, "Huawei Pricing", "c:\Caten_McGui re\Export\3G_Su pplier_Pricing_ Export"
DoCmd.SetWarnin gs True

EXCELFORMAT:

Set xlApp = New Excel.Applicati on
xlApp.Visible = True
Set xlWB = xlApp.Workbooks .Open("c:\Caten _McGuire\Export \3G_Supplier_Pr icing_Export")
Set xlWS = xlWB.Worksheets ("Huawei_Pricin g")

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


With Selection.Inter ior **FAILS HERE**
.Pattern = xlSolid
.ThemeColor = xlThemeColorLig ht2
.TintAndShade = -0.4999847407452 62
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[INDENT].Range("A1:AI1").Select
With Selection.Interior 
.Pattern = xlSolid
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.499984740745262
End With[/INDENT]
End With