By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Error 91 - Object variable or With block variable not set

P: 20
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
Attached Files
File Type: docx Error_91.docx (12.2 KB, 364 views)
May 31 '12 #1
Share this Question
Share on Google+
1 Reply


dsatino
100+
P: 393
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'.

Expand|Select|Wrap|Line Numbers
  1.  
  2. With xlWS
    .Range("A1:AI1").Select
  3. With Selection.Interior 
  4. .Pattern = xlSolid
  5. .ThemeColor = xlThemeColorLight2
  6. .TintAndShade = -0.499984740745262
  7. End WithEnd With
  8.  
  9.  
  10.  
  11.  
  12.  
  13.  
  14.  
  15.  
  16.  
  17.  
  18.  
  19.  
  20.  
  21.  
  22.  
  23.  
  24.  
  25.  
  26.  
  27.  
  28.  
May 31 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.