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
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
'____Open Target Excel file______________________________________________________________________________
Set xlWB = xlApp.Workbooks.Open(strExcelFileLocation)
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____________________________________________________________________
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
intBlankRow = intBlankRow + 1 'if blank, add to 1 blank row counter
intRow = intRow + 1 'Add to row counter and continue to next row
Loop Until intBlankRow > 20 Or intRow > 200
A BUNCH OF OTHER CODE HERE
' xlWB.Close False 'Closes document (but not Excel) and do not save Document
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___________________"