Excel from Access:"Object variable or With block variable not set" err

[cadoltt]

Hello everybody,

I am observing a strange behavior of my Access (2003) VBA code.

I have a procedure (in a form) to copy an Access table into an Excel file. The sub opens an excel application and a recordset, then loops through the recordset rows and transfers the values into excel cells.

Here is the code

Sub ExportToExcel()
  Dim oExAppl As Object, oWbk As Object, oSht As Object
  Dim rsSrc As New ADODB.Recordset
  Dim sSQL_rs As String
  Dim i_row As Integer

  'open excel
  Set oExAppl = CreateObject("Excel.Application")
  oExAppl.Application.Workbooks.Add
  Set oWbk = oExAppl.Application.ActiveWorkbook
  Set oSht = oWbk.Worksheets(1)

  'open recordset  
  sSQL_rs = "SELECT * from tbl_21 ORDER BY int_ParamNo;"

  rsSrc.Open sSQL_rs, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  
    rsSrc.MoveFirst
    
    i_row = 1

    'loop through recordset rows
    Do While rsSrc.EOF = False
        
      oSht.Cells(i_row, 1) = rsSrc!txt_ParamName

      'make font bold  
      'If Left(rsSrc!txt_ParamComment, 4) = "BOLD" Then
      '  oSht.Cells(i_Ex_row, 1).Select
      '  Selection.Font.Bold = True
      'End If
    
      oSht.Cells(i_row, 2) = rsSrc!sng_ParamValue
    
      rsSrc.MoveNext
      
      i_row = i_row + 1
      
    Loop 'end of loop
      
      
  rsSrc.Close


  'save file and close  
  oWbk.SaveAs "C:\myExcel.xls"
  oWbk.Close
  Set oSht = Nothing
  Set oWbk = Nothing
  oExAppl.Application.Quit
  Set oExAppl = Nothing

End Sub 'ExportToExcel

The sub works fine, no surprises. However there is a (commented out) section which conditionally can make font bold for some cells. Here where the trouble comes if I uncomment it. At times it works but other times it gives me an error message "Object variable or With block variable not set" stopping at line "Selection.Font.Bold = True".
One more strange thing I notice. If after the error comes, I close the form and rerun it, it seems working, doesn't give me the error, creates the excel file, but when I open it I see two workbooks, one named "myExcel.xls" contains all the table records but no bold fonts, the other named "Book 1.xls" (which prompts me to save it if I close the excel) contains one record in bold. It looks like the code somehow (and not always) opens two workbooks and splits the task between them.

Any idea what's going on?

Thank you in advance.

Alex

 

[SkipVought]

Hi,

You really ought to avoid using the select method...

      oSht.Cells(i_row, 1) = rsSrc!txt_ParamName

      'make font bold  
      If Left(rsSrc!txt_ParamComment, 4) = "BOLD" Then
        oSht.Cells(i_Ex_row, 1).Font.Bold = True
      End If

but the question is, 'does i_Ex_row have a VALID row number?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[cadoltt]

Skip,

It works now. 'i_Ex_row' was just a typo when I was simplifying the code for the post.

Thanks a lot!
Alex

 

[RoyVidar]

Also, combine the following lines

[tt] oExAppl.Application.Workbooks.Add
Set oWbk = oExAppl.Application.ActiveWorkbook[/tt]

into

[tt] Set oWbk = oExAppl.Application.Workbooks.Add[/tt]

Using .Select uses far more recourses than simple assigning as demonstrated by SkipVought - but the problem here, was the usage of an unqualified Selection object.

Vital when automating, is that you need to qualify each and every usage of objects, methods and properties of the automated object.

The point of qualifying - and the above "simplifying" of the two code lines, is to avoid what you've experienced - that unqualified referencing creates one (or more) extra instances of Excel in memory.

Here's some more reading

http://support.microsoft.com/default.aspx?kbid=178510

and

http://support.microsoft.com/?kbid=244264.

Roy-Vidar

 

[SkipVought]

or you could even do...

With oExAppl.Application.Workbooks.Add
   With .Worksheets(1)
'.......
    Do While rsSrc.EOF = False
        
      .Cells(i_row, 1) = rsSrc!txt_ParamName

      'make font bold  
      If Left(rsSrc!txt_ParamComment, 4) = "BOLD" Then
        .Cells(i_Ex_row, 1).Font.Bold = True
      End If
    
      .Cells(i_row, 2) = rsSrc!sng_ParamValue
    
      rsSrc.MoveNext
      
      i_row = i_row + 1
      
    Loop 'end of loop
   End with
'........
  'save file and close  
  .SaveAs "C:\myExcel.xls"
  .Close
'........

End with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[cadoltt]

Roy-Vidar, Skip,

Thank you for clarifying things. And what I really love is to simplify code

Alex