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