9 Replies Latest reply on Sep 30, 2016 2:02 PM by Christian Chu

    VB Run-time error '462' with Excel

    Cody Kirsch

      Having some trouble with this macro. It basically opens an excel document grabs some info, opens the required parts, puts the info into some properties saves and closes the parts. It works really well for the most part aside from some problems working with excel.

       

      It only works every other time. That when the run-time error occurs. Excel is not open and it does not matter if there are any left over excel processes that didn't get killed off when the error occurs.

       

      I have marked were the error occurs every time.

      Also, the green text is my attempt to make sure that it pulls the info from a sacrifice sheet instead of the last active sheet when the doc. was closed but I am unable to get that to work ether.  Also also, I would like to add a bit in were it dose not interfere with an already open excel doc if one happens to be open but have not been able to get something like that to work ether

       

      Here's excel potion of the code:

       

      A Sub main runs to gather some global variables then calls this

      Sub GetData()

      Debug.Print "___________________ GetData"

          Dim xlApp As Excel.Application

          Dim xlWB As Excel.Workbook

      '    Dim xlSheet As Excel.Worksheet

      '____ Local variables _______________________________________________________________________________

          Dim strTestString As String     'Temporary string to test cell values aginst

          Dim intRow As Integer           'Excel row counter

          Dim intRevCount As Integer      'Rev Counter

          Dim intBlankRow As Integer      'Tracks the number of black rows inbetween populated cells

          Dim intNumOfDrawings As Integer 'Tracks number of drawings found

          Dim intDwgIndex As Integer      'Used to index strDwgNum to track the location of drawing numbers

      '    Dim strSheetName As String

      '    strSheetName = "Info List" 

       

              Set xlApp = CreateObject("Excel.application") 'Create a link to a NEW instance of Excel

              xlApp.Visible = False

              Err.Clear

      '____Open Target Excel file______________________________________________________________________________

              Set xlWB = xlApp.Workbooks.Open(strExcelFileLocation)

      '        ActiveWorkbood.Sheets(strSheetName).Activate

              xlApp.Visible = False     

      '____Establish first row and Test string values________________________________________________________

          intRow = 2          'Starting on 2ed row of excel to avoid collum lables

          strTestString = ""

          intBlankRow = 0

          intNumOfDrawings = 0

          intDwgIndex = 1

      '____Find Number of Drawings in Excel____________________________________________________________________

          With xlWB.Worksheets(1)

              Do

                  strTestString = Cells(intRow, 2).Value  'Puts value of int Row row and 2ed collum cell into test string                                   ERROR OCCURS HERE

       

                  If strTestString <> "" And strTestString <> "END" Then  'Test for blank cell or for end string in excel

                      intNumOfDrawings = intNumOfDrawings + 1             'if NOT black, add 1 to drawing count if not blank

                      intBlankRow = 0                                     'Reset blank row counter

                  ElseIf strTestString = "END" Then

                      Exit Do                         'if loop is at the end of the excel table, end the loop

                  Else

                      intBlankRow = intBlankRow + 1   'if blank, add to 1 blank row counter

                  End If

       

                  intRow = intRow + 1                    'Add to row counter and continue to next row

                 

              Loop Until intBlankRow > 20 Or intRow > 200

          End With

       

      A BUNCH OF OTHER CODE HERE

       

      '_____Clean up___________________________________________________________________________________________

      '    xlWB.Close False 'Closes document (but not Excel) and do not save Document

          xlApp.Quit

          Set xlApp = Nothing

          Set xlWB = Nothing

              Shell "TASKKILL /F /IM Excel.exe", vbHide 'Kill off any Excel Process that may be still running

      Debug.Print "End GetData___________________"

      End Sub