Results 1 to 5 of 5

How to fix an error occurring within this VBA function? (ERR91: Obj/With Variable Not Set)

Threaded View

  1. #1
    Registered User
    Join Date
    10-02-2015
    Location
    Pennsylvania
    MS-Off Ver
    2007
    Posts
    33

    Question How to fix an error occurring within this VBA function? (ERR91: Obj/With Variable Not Set)

    Hi All,

    Not sure if this falls in the Word or Excel VBA bucket (I think Excel?). I've been trying to stitch together a Word VBA Script that takes all the Word comments and puts them into a new Excel spreadsheet, then formats the Excel spreadsheet.

    However the "Excel" portion of the code seems to fail after every "other" run. Specifically the error occurs when trying to Left-Align Column C. The error that occurs is "Error 91 - Object variable or With block variable not set". The error happens every 2 runs.

    Would anyone know how to fix the Error that occurs below, please? Thanks so much in advance!
    (The offending/highlighted line below is: ActiveSheet.Columns("C").HorizontalAlignment = xlHAlignLeft )

    Function CopyWordCommentstoNewExcelFile()
     'Takes MS Word comments and puts them into an Excel File
    
      Dim xlApp As Excel.Application
      Dim xlWB As Excel.Workbook
      Dim i As Integer
      Dim HeadingRow As Integer
      HeadingRow = 3
    
      Dim cmtRef As Range
    
      Set xlApp = CreateObject("Excel.Application")
      xlApp.Visible = True
      Set xlWB = xlApp.Workbooks.Add ' create a new workbook
      
      With xlWB.Worksheets(1) 'START EXCEL INTEGRATION
        'Create report info
        .Cells(1, 1).Formula = "Reviewed document:"
    
        'Create Heading
        .Cells(HeadingRow, 1).Formula = "Issue #"
        .Cells(HeadingRow, 2).Formula = "Section #"
        .Cells(HeadingRow, 3).Formula = "Step #"
        .Cells(HeadingRow, 4).Formula = "Issue abstract/comment/resolution:"
        
        Dim strStep, strComment As String
        
        For i = 1 To ActiveDocument.Comments.Count
       
          'ISSUE #
          .Cells(2, 1).Formula = ActiveDocument.Comments(i).Parent
          .Cells(i + HeadingRow, 1).Formula = ActiveDocument.Comments(i).Index 'COL A
            
          'SECTION NUMBER
          strDocSection = ReplaceAndSplit(ActiveDocument.Comments(i).Range, "(,)#")(1)
          .Cells(i + HeadingRow, 2).Formula = strDocSection 'COL B
                    
          'STEP NUMBER
          strStempNumber = GetBetween(ActiveDocument.Comments(i).Range, ", #", ")")
          .Cells(i + HeadingRow, 3).Formula = strStempNumber 'COL C
            
          'COMMENT
          strComment = GetBetween(ActiveDocument.Comments(i).Range, ") ", " (")
          .Cells(i + HeadingRow, 4).Formula = strComment 'COL D
               
        Next i
      
      
      '--BEGIN FORMATTING--  'THE PROBLEM HAPPENS WHEN RUNNING THE LEFT-ALIGN CODE BELOW
        
       'LEFT ALIGN COL C
       ActiveSheet.Columns("C").HorizontalAlignment = xlHAlignLeft '<-- THIS FAILS AFTER EVERY-OTHER RUN WITH "ERROR 91 - Object variable or With block variable not set", BUT WORKS ON THE NEXT RUN
      
       '--END FORMATTING--
      
      End With 'END EXCEL INTEGRATION
      
    End Function
    Thanks so much,
    JMC
    Last edited by jmccoughlin; 12-18-2015 at 11:56 PM. Reason: Added the offending/highlighted line to the description

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Compile Error Expected Function or Variable
    By shiser in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-22-2014, 09:18 AM
  2. Argument not optional error is occurring in goal seek automated code.
    By bk1202 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-21-2013, 08:32 AM
  3. Getting "Object variable or With block variable not set" error with Find function
    By enphynity in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2013, 05:47 PM
  4. Replies: 1
    Last Post: 09-07-2012, 12:08 AM
  5. Expected function or variable error
    By Arimpact in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-09-2011, 07:48 PM
  6. Expected Function or Variable Error !!
    By BouncingHippo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-02-2008, 07:47 PM
  7. Reference Value not Formula in cell via VBA - Err91 prt3
    By justagrunt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2006, 12:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1