Easier to read:
Sub IMPORT_ALL_INFORMATION()
REM Set variables
Dim file_in As Long
Dim strInput As Variant
Dim i As Integer
Dim j As Integer
Dim l As Integer
Dim sTmp As String
Dim sJob As String
Dim sSchedPath As String
REM end setting variables
Sheets("REPORT").Select
Range("C2").Select
sSchedPath = "C:\Temp"
Call apiCopyFile("\servername\Applications\Schedule\schedule-s\schedule, S.xls", "C:\Temp\schedule, S.xls", 0)
dteStart = Application.Sheets("Report").Range("$G$27").Value
dteEnd = Application.Sheets("Report").Range("$J$27").Value
REM First data row of schedule, S.xls
l = 4
REM First job row of Plate & Bar Spreadsheet
j = 2
Do Until CDate(GetDate(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))) >= dteStart
l = l + 1
REM Changed from 754 to 854...may be total jobs for year...went close to 800 jobs this year . BAW
If l = 854 Then
MsgBox ("Hello")
End If
sTmp = Trim$(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))
If sTmp = "0" Or Len(sTmp) = 0 Or sTmp = "HOLIDAY" Then
l = l + 1
End If
Loop
Do
sJob = ParseJob(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "B" & CStr(l)))
REM Debug.Print sJob
vJobFolders = Split(FindJobDir(strpathtofile & sJob), ",")
For i = 0 To UBound(vJobFolders)
On Error GoTo ErrorExit
Application.Sheets("report").Range("C" & CStr(j)).Value = vJobFolders(i)
j = j + 1
REM file number
file_in = FreeFile
strFileToOpen = strpathtofile & vJobFolders(i) & strFilename
If Dir(strFileToOpen) <> "" Then
Open strFileToOpen For Input As #file_in
Put_Data_In_Array (file_in)
Organize_Array_For_Print
Close #file_in REM close the file
End If
ErrorExit:
Next i
l = l + 1
sTmp = Trim$(GetValue(sSchedPath, "schedule, S.xls", "LOG (2)", "N" & CStr(l)))
If sTmp = "0" Or Len(sTmp) = 0 Or sTmp = "HOLIDAY" Then
l = l + 1
End If
Loop Until CDate(GetDate(sTmp)) >= dteEnd
Sheets("REPORT").Select
End Sub
Function GetValue(path, file, sheet, ref) As String
REM Retrieves a value from a closed workbook
Dim arg As String
Dim pos As Integer
REM Make sure the file exists
If Right(path, 1) <> "" Then
path = path & ""
End If
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
REM Create the argument
arg = "REM " & path & "[" & file & "]" & sheet & "REM !" & _
Range(ref).Range("A1").Address(, , xlR1C1)
REM Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
REM Strip Any time from beginning of Date string
pos = InStr(GetValue, ":")
If pos <> 0 Then GetValue = Mid$(GetValue, pos + 3)
End Function
