Untitled Page

2006 FastFIROF an Excel VBA project

Problem:

Though we had an automatic inventory replenishment system, several times a month techs still might need to replenish inventory that ran unexpectedly low, or to stock up for an event. In order to do this we had to email an order form, an excel sheet to the warehouse folks in Kansas, the FIROF form. ( Fast Inventory Replenishment Order Form ... I think )

The FIROF form was frequently updated by main office with a list of orderable inventory. If it was not on the list, could not be ordered.

This was one of the most time consuming, maddening tasks in the store ... time consuming, because we were front line employees and constantly interrupted by the needs of customers or staff ... maddening because what we were doing was data entry for headquarters ... feeding them back information that they already had or should have been able to get.

The problem was that for every item we might want to order, we would have to consult two separate data sources to find information that the FIROF required in order to be accepted.

One data source was what we called ASI, an old Attachmate terminal program ... we had to enter the sku of the item, the warehouse number we were inquiring about, then we had to transfer the resultant value over to the FIROF form. This we did for both the main warehouse inventory level and to get the official inventory level for our own store.
The other data source was a huge sales report workbook. Sales for all items, for all stores in the region. We needed a couple of values from this report, the 2 week history of the item, and the amount already ordered.

So, a typical work flow was as follows:

We think we need some more cases for a Sanyo Deluxe 9000.
First we figure out what SKU that item is .... 1 min
Then we look at the FIROF form to see if the SKU is on the list, so that we know whether we are even allowed to order the item. 1 min
If, by luck, the sku IS on the list, then we ALT Tab over to ASI, the Attachmate program, enter the store number, enter the desired sku, get the resultant inventory value for our store number ( which is what we officially have in stock ), ALT Tab back to the FIROF order form, enter values. 2 min
Then we switch back to ASI, enter the central warehouse number and get their inventory level for that item..... then go back again to the FIROF form, enter the data. 1 min
by now, at the very least, a couple minutes have passed to look up one item & fill in the form.
Now, we go to the excel sales report, filter it down to our store, find the desired SKU ... look at the 15th ( or so ) column on the left and find the sales history for that item. 2 min
We then ALT-Tab back to the FIROF form.
So, So, several minutes to get all the required data to order 1 item. And that is if we were lucky and were not interrupted. We were always being interrupted, because the customer in front of us had priority over any other task.

Long story short, doing one of these orders was such a pain in the arse that we avoided doing them .... to actually get one done, one of more than just a few items, ended up taking several hours ... mainly due to interruptions.

The Solution:

work flow diagram

FastFIROF was born when I realized that a macro language existed out there for the Attachmate terminal. The language is EXTRA! Basic & it let me control Attachmate and transfer data between Excel & the Attachmate window.

I already had some Excel VB skills, so tying in the other data source, the Excel sales report, was a piece of cake.

What is it? FastFIROF is (was) a stand alone excel sheet with some VB powered buttons. FastFIROF interfaced with all the data sources, gathering the required data and building the needed order form. All in about 10 minutes.

screenshot

The New WorkFlow:

Load up FastFIROF, load up a brand new FIROF order form, from HQ intranet. Make sure that all the other data sources are open. FastFIROF won't run until everything is open. It checks & indicates if anything is missing.
Click Load Skus. FastFIROF pulls the Skus of orderable items from the new clean FIROF form.
Click ASI:Warehouse Available. FastFIROF, using the EXTRA!Basic code, takes each Sku in the list, sends it over to the ASI window, gets the resultant value, for the warehouse number. Repeats all the way down the list of skus. 1 minute or so, for typical lists.
Click ASI:Store On Hand. FastFIROF does the above, just using the stores inventory warehouse number. 1 minute or so, for typical lists.
Click Get 2 Week Data. FastFIROF steps through each Sku, finds that Sku in the Sales History Report for your store number. 1/2 a minute maybe.
Click Delete Unavailable in Warehouse ... any items that are unorderable becauase main warehouse is out are removed from the list.
Click Highlight Violations, if you want .... this will highlight any items on the list that are violations of business rules, ( ratio of on hand to sales history ) thus unorderable ... however, sometimes you need to force it through, so you can leave them on the list.
Now, you can examine the Skus in FastFIROF - what you have is a list of the things you could order if you were so inclined. With all the necessary data for making that decision. For each item you decide to order, enter a quantity to order.
Final step is click Build FIROF ... this transfers all the items to be ordered, with their requisite data, over to the clean FIROF form. You can then save & email off to the warehouse.

Total Time: you could finish one of these off in 10 minutes, regardless of how many items you wanted to or, regardless of how many items you wanted to order.

I am posting the FastFIROF.xls sheet, as a code demonstration. It is NOT functional - since it needs both the sales report and the terminal window open to work - both of which are not accessible.

    
'===================================================================================
'===================================================================================
' Global Variables
'===================================================================================
  
    Public Sessions As Object
    Public System As Object
    Public Sess0 As Object
    '
    '
    '
    

       
'===================================================================================
'===================================================================================
' See What's Open
'===================================================================================
Sub SeeWhatsOpen()

    Dim FastFirof As Workbook
    Dim FastFirofMain As Worksheet
    Dim FastFirofName As String
    
    Set FastFirof = ActiveWorkbook
          
    FastFirofName = FastFirof.Name
    Set FastFirofMain = Workbooks(FastFirofName).Worksheets("FrontPage")

    Module3.findfirof
    Module3.Find2Week
    Module3.FindASI
    
End Sub

'=================================================================================
'=================================================================================
' Set Indicators
'=================================================================================
Sub SetIndicator(FastFirof As Workbook, Target As String, Present As Boolean)
        '=========================================================================
        '   Called after Find subs are called ... formats items on main page
        '   to indicate whether the Find was successful
        '=========================================================================
        
        Dim IndicatorTitle As Range
        Dim IndicatorText As Range
        Dim FastFirofMain As Worksheet
        Dim FastFirofName As String
        
        'MsgBox "Sub Set Indicator sees: " + FastFirof.Name
                
        FastFirofName = FastFirof.Name
        Set FastFirofMain = Workbooks(FastFirofName).Worksheets("FrontPage")
        FastFirofMain.Activate
                        
        If Target = "FIROF" Then
            Set IndicatorTitle = Range("A2")
            Set IndicatorText = Range("B2")

        ElseIf Target = "2Week" Then
            Set IndicatorTitle = Range("A3")
            Set IndicatorText = Range("B3")

        ElseIf Target = "ASI" Then
            Set IndicatorTitle = Range("A4")
            Set IndicatorText = Range("B4")
        End If

        If Present = True Then
            IndicatorTitle.Select
            With Selection.Interior
                .ColorIndex = 4
                .Pattern = xlSolid
                .PatternColorIndex = 4
            End With
            If Target = "FIROF" Then
                IndicatorText.Value = "Open"
                IndicatorText.Select
                    With Selection.Interior
                        .ColorIndex = 0
                        .Pattern = xlGray25
                        .PatternColorIndex = 4
                    End With
            ElseIf Target = "2Week" Then
                IndicatorText.Value = "Open"
                IndicatorText.Select
                    With Selection.Interior
                        .ColorIndex = 0
                        .Pattern = xlGray25
                        .PatternColorIndex = 4
                    End With
            ElseIf Target = "ASI" Then
                IndicatorText.Value = "Open"
                IndicatorText.Select
                    With Selection.Interior
                        .ColorIndex = 0
                        .Pattern = xlGray25
                        .PatternColorIndex = 4
                    End With
            End If
        ElseIf Present = False Then
            IndicatorTitle.Select
            With Selection.Interior
                .ColorIndex = 3
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
            IndicatorText.Value = "Not Open"
            IndicatorText.Select
            With Selection.Interior
                .ColorIndex = 0
                .Pattern = xlGray25
                .PatternColorIndex = 3
            End With
        End If

End Sub


'===================================================================================
'===================================================================================
' Find FIROF
'===================================================================================

Public Sub findfirof()
            '=======================================================================
            ' FindFIROF()   looks within open workbooks for the FIROF form
            '               specifically, it is trying to find any open workbook
            '               that has a sheet called "Accessory FIROF"
            '               If the format of the Accessory FIROF changes,
            '               the identifier here must be changed too
            '
            '               Calls SetIndicator() with results, which indicates on
            '               the main screen whether it has been found or not
            '
            '=======================================================================

    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim FastFirof As Workbook
    
    Set FastFirof = ActiveWorkbook
    
    'MsgBox "Sub Find Firof sees: " + FastFirof.Name
                             
    Application.ScreenUpdating = False
        
    For Each wkb In Workbooks
        wkb.Activate
        For Each wks In Worksheets
            If Not SheetExists("Accessory FIROF") Then
               Call SetIndicator(FastFirof, "FIROF", False)
            Else
                Set Firof = ActiveWorkbook
                FirofName = ActiveWorkbook.Name
                Call SetIndicator(FastFirof, "FIROF", True)
                Exit Sub
            End If
        Next
    Next
   
    Application.ScreenUpdating = True
    'FastFirofMain.Activate
End Sub

'========================================================================================
'========================================================================================
' Function SheetExists
'========================================================================================
Function SheetExists(SheetName As String) As Boolean
            '============================================================================
            ' SheetExists() Steps through sheets in active workbook to see if the
            '               SheetName string is the name of an active sheet'
            '
            '               returns TRUE if the sheet exists in the active workbook
            '
            '============================================================================

    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function



'=====================================================================================
'=====================================================================================
' Find 2Week
'=====================================================================================
Public Sub Find2Week()
        '=============================================================================
        ' Find2Week()   looks within open workbooks for the Accessory 2 Week Report
        '               specifically, it is trying to find any open workbook
        '               that has a sheet called wherein the A1 cell = "ASI Location"
        '               If the format of the Accessory FIROF changes,
        '               the identifier here must be changed too
        '
        '               Calls SetIndicator() with results, which indicates on the main
        '               screen whether it has been found or not
        '
        '==============================================================================

    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim FastFirof As Workbook
    
    Set FastFirof = ActiveWorkbook
    'MsgBox "Sub Find 2Week sees: " + FastFirof.Name
           
    Application.ScreenUpdating = False
      
    For Each wkb In Workbooks
        wkb.Activate
        
        For Each wks In Worksheets
            wks.Activate
            
            'If 2Week IS FOUND
            If (Range("a1") = "ASI LOCATION") Or (Range("A1") = "ASI_LOCATION") Then
                Set TwoWeek = ActiveWorkbook
                Call SetIndicator(FastFirof, "2Week", True)
                Exit Sub
                            'If 2Week IS NOT FOUND
            Else
                Call SetIndicator(FastFirof, "2Week", False)
                
            End If
        Next
    Next
    
   Application.ScreenUpdating = True

End Sub



'======================================================================================
'======================================================================================
' Find ASI
'======================================================================================
Sub FindASI()
            '==========================================================================
            ' FindASI()     looks for open ASI
            '               it does this by trying to instantiate the Extra object
            '               there are 3 levels of instantiation
            '               I'm not real sure what each level means
            '               but if fails any one, the sub returns asifound as FALSE
            '
            '               Calls SetIndicator() with results, which indicates on
            '               the main screen whether it has been found or not
            '
            '==========================================================================
       
    Dim ASIfound As Boolean
    Dim FastFirof As Workbook
    
    Set FastFirof = ActiveWorkbook
    
    
    
        
'        Set System = CreateObject("EXTRA.System")   ' Gets the system object
'        Set Sessions = System.Sessions
'        Set Sess0 = System.ActiveSession
        
                Call SetIndicator(FastFirof, "ASI", True)
        
                Set System = CreateObject("EXTRA.System")   ' Gets the system object
                If (System Is Nothing) Then
                    Call SetIndicator(FastFirof, "ASI", False)
                End If
                
                Set Sessions = System.Sessions
                If (Sessions Is Nothing) Then
                     Call SetIndicator(FastFirof, "ASI", False)
                End If
                
                Set Sess0 = System.ActiveSession
                If (Sess0 Is Nothing) Then
                     Call SetIndicator(FastFirof, "ASI", False)
                End If
        
        
        


End Sub

'===================================================================================
'===================================================================================
' Load Skus from FIROF
'===================================================================================

Sub LoadSkusFromFirof(FastFirof As Workbook)
  
    Dim CheckFIROFOpen As Range
    Dim FirofName As String
    Dim Firof As Workbook
    Dim FirofSkus As Worksheet
    Dim FirofSkuSheetName As String
    Dim FirofSkuFirstRow As Long
    Dim FirofLastRow As Long
    Dim CopyRange As Range
    Dim first, last, rangestring, laststring As String
    Dim FastFirofMain As Worksheet
    
    '==========================================================================
    '       See Whether the Firof page is open  by calling SeeWhatsOpen
    '       & checking the value of the Firof indicator on FrontPage
    '==========================================================================
    
    Set CheckFIROFOpen = Range("b2")
           
    Call SeeWhatsOpen
    
    If CheckFIROFOpen.Value = "Not Open" Then
        MsgBox "you need to open the FIROF Form first"
        Exit Sub
    End If
        
    '==========================================================================
    '       Get Firof's Name & Firof / Sku pages name
    '
    '==========================================================================
                     
    FirofName = GetFIROFName()
    FirofSkuSheetName = "Sku's"
        
    Set Firof = Workbooks(FirofName)
    Set FirofSkus = Workbooks(FirofName).Worksheets(FirofSkuSheetName)
    
    '==========================================================================
    '       activate Firof - Sku sheet, get number of rows by calling
    '       Function LastRow()
    '==========================================================================
        
    FirofSkus.Activate
    FirofSkuFirstRow = 2
    FirofSkuLastRow = LastRow()
        
    '==========================================================================
    '       Build a range address for data on Firof - Sku
    '       & then Copy
    '==========================================================================
        
    first = "A2"
    laststring = FirofSkuLastRow
    last = "B" + laststring
    rangestring = first + ":" + last
    
    Range(rangestring).Copy
    
    '==========================================================================
    '       Set location of FastFirof / frontpage
    '       Activate & Paste ( values only ) data from Firof / Sku
    '==========================================================================
        
       
    FastFirofName = FastFirof.Name
    Set FastFirofMain = Workbooks(FastFirofName).Worksheets("FrontPage")
    
    FastFirofMain.Activate
    Range("A8").PasteSpecial xlPasteValues
    
    Range("A8").Select
    
    Call SetQtyZero
         
End Sub


'===================================================================================
'===================================================================================
' Get FirofForm Name
'===================================================================================
Function GetFIROFName() As String

    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim FastFirof As Workbook
    
    Set FastFirof = ActiveWorkbook
                         
    Application.ScreenUpdating = False
        
    For Each wkb In Workbooks
        wkb.Activate
        For Each wks In Worksheets
            If Not SheetExists("Accessory FIROF") Then
               'nothing
            Else
                Set Firof = ActiveWorkbook
                GetFIROFName = ActiveWorkbook.Name
                Exit Function
            End If
        Next
    Next
    Application.ScreenUpdating = True

End Function

'===================================================================================
'===================================================================================
' LastRow()
'===================================================================================

Function LastRow() As Long

    LastRow = Range("A65536").End(xlUp).Row
       
End Function

'===================================================================================
'===================================================================================
' Get Data on Skus
'===================================================================================

Sub CleanUpFrontPage()

    Dim FastFirof As Workbook
    Dim FastFirofMain As Worksheet
    Dim FastFirofName As String
    Dim FastFirofFirst, FastFirofLast As Long
    Dim RowCount As Long
                    
    Set FastFirof = ActiveWorkbook

    FastFirofName = FastFirof.Name
    Set FastFirofMain = Workbooks(FastFirofName).Worksheets("FrontPage")
    
    FastFirofMain.Activate
   
    FastFirofFirst = 8
    FastFirofLast = LastRow()

    'MsgBox FastFirofLast
        
    Cells(FastFirofFirst, 3).Activate
    RowCount = FastFirofFirst
    
    Do
        Cells(RowCount, 3).Value = 0
        RowCount = RowCount + 1
    Loop Until RowCount = FastFirofLast
        
    Range("A8").Select
    MsgBox "finished"
    
End Sub

'===================================================================================
'===================================================================================
' Get 2 Week Data
'===================================================================================

Sub Get2Week()

    Dim FastFirof As Workbook
    Dim FastFirofMain As Worksheet
    Dim FastFirofName As String
    
    Dim TwoWeekName As String
    Dim TwoWeek As Workbook
    Dim TwoWeekSheet As Worksheet
    Dim TwoWeekData As Worksheet
    
    Dim TwoWeekDataFirst, TwoWeekDataLast, FirofMainFirst, FirofMainLast As Long
    Dim FirofMainRowInFocus, TwoWeekDataRowInFocus As Variant
    
    Dim TwoWeekSearch As Range
    
    Dim Warehouse As String
    
    Dim FastFirof2WeekArea As Range
    
    
    
    
    '==========================================================================
    '       Set FastFirof Addreses
    '==========================================================================
    Set FastFirof = ActiveWorkbook
    FastFirofName = FastFirof.Name
    Set FastFirofMain = Workbooks(FastFirofName).Worksheets("FrontPage")
    
    Set FastFirof2WeekArea = Range("h8", "j500")
    
    FastFirof2WeekArea.ClearContents
        
    Warehouse = Range("B1").Value
    
    
    '==========================================================================
    '       See Whether the TwoWeek Workbook page is open by calling SeeWhatsOpen
    '       & checking the value of the TwoWeek indicator on FrontPage
    '==========================================================================
    Set CheckFIROFOpen = Range("b2")
           
    Call SeeWhatsOpen
    
    If CheckFIROFOpen.Value = "Not Open" Then
        MsgBox "you need to open the 2 Week Report first"
        Exit Sub
    End If
    

    
    '==========================================================================
    '       Create a temporary worksheet in FastFirof
    '==========================================================================
    Set TwoWeekData = Worksheets.Add
    TwoWeekData.Name = "TwoWeekData"
        
    FastFirofMain.Activate
    
    
    '==========================================================================
    '       Get The Name of the TwoWeekReport
    '==========================================================================
    TwoWeekName = Get2WeekName()
    
    
    '==========================================================================
    '       Set Workbook Object TwoWeek
    '==========================================================================
    Set TwoWeek = Workbooks(TwoWeekName)
    
        
    '==========================================================================
    '       TwoWeek Sheet Name appears to be random but is only sheet open
    '       so, this will choose the first sheet within the TwoWeek Workbook
    '==========================================================================
    Set TwoWeekSheet = TwoWeek.Worksheets(1)
        
        
    '==========================================================================
    '       Activate TwoWeekSheet
    '       & apply filter, copy into temportary sheet
    '==========================================================================
    With TwoWeekSheet
        .AutoFilterMode = False
        .Range("a2").AutoFilter Field:=1, Criteria1:=Warehouse
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Workbooks(FastFirofName).Worksheets("TwoWeekData").Range("a1")
    End With
    
    
    '==========================================================================
    '       establish first & last rows on firofmain & twoweekdata
    '==========================================================================
    TwoWeekData.Activate
    TwoWeekDataFirst = 2
    TwoWeekDataLast = LastRow()
    
    FastFirofMain.Activate
    FirofMainFirst = 8
    FirofMainLast = LastRow()
    
    
    '==========================================================================
    '       Return Focus to FirofMain
    '==========================================================================
    FirofMainRowInFocus = FirofMainFirst
    Cells(FirofMainFirst, 1).Activate
    
    
    '==========================================================================
    ' Begin Loop from First Data Line in FirofMain to Last Data Line in FirofMain
    '==========================================================================
    
    '==========================================================================
    '        Turn off screen updating
    '==========================================================================
    Application.ScreenUpdating = False
        
    For FirofMainRowInFocus = FirofMainRowInFocus To FirofMainLast
    
        ' Set Search Item = to value of Cell x,1 in RowInFocus
        searchsku = Cells(FirofMainRowInFocus, 1).Value
        
        'activate TwoWeekData sheet
        TwoWeekData.Activate
        
        'find the search item
        Set TwoWeekSearch = Range("c2:c2000").Find(What:=searchsku)
        
        'If search is successful
        If Not TwoWeekSearch Is Nothing Then
        
                'Find row address of search result
                TwoWeekDataRowInFocus = TwoWeekSearch.Row
                ' two week history
                FastFirofMain.Cells(FirofMainRowInFocus, 8) = Cells(TwoWeekDataRowInFocus, 6)
                ' planogram minimum
                FastFirofMain.Cells(FirofMainRowInFocus, 9) = Cells(TwoWeekDataRowInFocus, 4)
                ' forecast
                FastFirofMain.Cells(FirofMainRowInFocus, 10) = Cells(TwoWeekDataRowInFocus, 7)
                
        End If
          
        FastFirofMain.Activate
                
    Next FirofMainRowInFocus
    
    Application.ScreenUpdating = True
    
    Application.DisplayAlerts = False
    FastFirof.Worksheets("TwoWeekData").Delete
    Application.DisplayAlerts = True
          
End Sub


'===================================================================================
'===================================================================================
' Function Get2WeekName
'===================================================================================

Function Get2WeekName() As String

    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim FastFirof As Workbook
    Dim TwoWeek As Workbook
    
    Set FastFirof = ActiveWorkbook
                         
    Application.ScreenUpdating = False
        
    For Each wkb In Workbooks
        wkb.Activate
        For Each wks In Worksheets
            wks.Activate
            'If 2Week IS FOUND
            
            If (Range("a1") = "ASI LOCATION") Or (Range("a1") = "ASI_LOCATION") Then
                Set TwoWeek = ActiveWorkbook
                'MsgBox "get2weekname found"
                Get2WeekName = ActiveWorkbook.Name
            End If
            
            'If 2Week IS NOT FOUND
        Next
    Next
    
    Application.ScreenUpdating = True

End Function
'===================================================================================
'===================================================================================
' GetSCS :
'===================================================================================
Sub GetSCS()

    Dim ItemID As String
    Dim AvailToAllocate As String
    
    Dim TempAnswer, CheckError As String
    Dim CheckResult As Integer
    
    Dim FastFirof As Workbook
    Dim FastFirofMain As Worksheet
    Dim FastFirofName As String
    
    Dim Warehouse As String
        
    Dim FirofMainFirst, FirofMainLast As Long
    Dim FirofMainRowInFocus As Variant
    
    
        
    '==========================================================================
    '  GetSCS :      establish worbook names
    '==========================================================================
    
    Set FastFirof = ActiveWorkbook
    FastFirofName = FastFirof.Name
    Set FastFirofMain = Workbooks(FastFirofName).Worksheets("FrontPage")
       
    Call FindASI
    If Range("b4") <> "Not Open" Then
       
                Set System = CreateObject("EXTRA.System")   ' Gets the system object
                If (System Is Nothing) Then
                    MsgBox "Please Open ASI first."
                End If
                
                Set Sessions = System.Sessions
                If (Sessions Is Nothing) Then
                    MsgBox "Please Open ASI first."
                End If
                
                Set Sess0 = System.ActiveSession
                If (Sess0 Is Nothing) Then
                    MsgBox "Please Open ASI first."
                End If
                  
                
                Warehouse = Range("B5").Value
                
                MsgBox "A search on 100 items takes about 3 min - please wait! _

Try not to do anything while this is running - ASI is sensitive_

I am working on better error handling"

               ' Application.ScreenUpdating = False
                
            
                '==========================================================================
                '    GetSCS :    Set ASI timeout values
                '==========================================================================
            
                 g_HostSettleTime = 250     ' milliseconds
            
                '==========================================================================
                '    GetSCS :    establish first & last rows on firofmain
                '==========================================================================
                
                FastFirofMain.Activate
                
                FirofMainFirst = 8
                FirofMainLast = LastRow()
                
                FirofMainRowInFocus = FirofMainFirst
                    
                ' ItemID = InputBox(" Enter a SKU ")
                
                Sess0.Screen.PutString "         ", 3, 13
                Sess0.Screen.PutString Warehouse, 3, 13
            
                '==========================================================================
                '    GetSCS :    Begin loop through FirofMain
                '==========================================================================
               
                 For FirofMainRowInFocus = FirofMainRowInFocus To FirofMainLast
                 
                        Cells(FirofMainRowInFocus, 1).Select
                        
                        ' *****************************************************************
                        ' *********  Get Input
                        ItemID = Cells(FirofMainRowInFocus, 1).Value
                        
                        ' ***********************************************************************
                        ' *********  assuming you are on the inventory available screen,
                        ' *********  first clear out the warehouse field, with 10 spaces at 3,13
                        ' *********  then put in SCS at 3,13
            '            Sess0.Screen.PutString "         ", 3, 13
            '            Sess0.Screen.PutString "SCS", 3, 13
                                   
                        ' *************************************************************************
                        ' *********  then clear out the Item ID field at 3,36 with 17 spaces  
                        ' *********  then put in the ItemID string at 3,36
                        Sess0.Screen.PutString "                 ", 3, 36
                        Sess0.Screen.PutString ItemID, 3, 36
                        
                        ' *************************************************************************
                        ' *********  Send the Enter Key to execute query
                        ' *********
                        Sess0.Screen.SendKeys ("<Enter>")
                        
                        ' *************************************************************************
                        ' *********  Wait to let Extra process the query or it will putin old value
                        ' *********  currently set to .5 sec
                        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                                  
                        ' *************************************************************************
                        ' *********  then, get AvailableToAllocate value into var AvailToAllocate
                        ' *********  from row 15, col 22, length 11
                        TempAnswer = Sess0.Screen.GetString(15, 22, 11)
                        
                        CheckError = Sess0.Screen.GetString(24, 2, 8)
                        
                                If CheckError = "        " Then
                                        'MsgBox "good"
                                        CheckResult = IsNumeric(TempAnswer)
                                        
                                        If CheckResult = 0 Then
                                           AvailToAllocate = 0
                                        Else
                                            AvailToAllocate = TempAnswer
                                        End If
                                        
                                FastFirofMain.Cells(FirofMainRowInFocus, 7).Value = AvailToAllocate
                                        
             ElseIf CheckError = "Invalid " Then
                MsgBox "Invalid Sku Error - Skipping"
                FastFirofMain.Cells(FirofMainRowInFocus, 11).Value = "Invalid Sku: " + ItemID
                'Sess0.Screen.SendKeys ("<ESC>")
                                        
            ElseIf CheckError = "Keyboard" Then
                MsgBox "Keyboard Input Error - Attempting to Skip-IfStuck, hit ESCtimes & Rerun"
                FastFirofMain.Cells(FirofMainRowInFocus, 11).Value = "Keyboard Error"
                Sess0.Screen.SendKeys ("<ESC>")
                Sess0.Screen.PutString "         ", 3, 13
                Sess0.Screen.PutString Warehouse, 3, 13
                                            
            Else: MsgBox "Unknown Error - Attempting to Skip"
                FastFirofMain.Cells(FirofMainRowInFocus, 11).Value = "Unknown Error"
                Sess0.Screen.PutString "         ", 3, 13
                Sess0.Screen.PutString Warehouse, 3, 13
            End If
                        
                                                           
                Next FirofMainRowInFocus
            
                MsgBox "Finished !"
    Else
            MsgBox "Open ASI first !"
                    
    End If
    
   ' Application.ScreenUpdating = True


End Sub



'===================================================================================
'===================================================================================
' Get StoreOnHand :
'===================================================================================
Sub GetStoreOnHand()

    Dim ItemID As String
    Dim StoreOnHand, StoreOnOrder As String
    Dim StoreInTransit As String
        
    Dim OnHand As String
    Dim InTransit As String
    Dim OnOrder As String
    
    Dim CheckResult1, CheckResult2, CheckResult3 As Integer
    
    Dim FastFirof As Workbook
    Dim FastFirofMain As Worksheet
    Dim FastFirofName As String
    
    Dim Warehouse As String
        
    Dim FirofMainFirst, FirofMainLast As Long
    Dim FirofMainRowInFocus As Variant
    
    '==========================================================================
    '    Get StoreOnHand :   establish worbook names
    '==========================================================================
               
    Set FastFirof = ActiveWorkbook
    FastFirofName = FastFirof.Name
    Set FastFirofMain = Workbooks(FastFirofName).Worksheets("FrontPage")
       
    Call FindASI
    If Range("b4") <> "Not Open" Then
                
                Set System = CreateObject("EXTRA.System")   ' Gets the system object
                If (System Is Nothing) Then
                    MsgBox "Could not create the EXTRA System object.  Stopping macro playback."
                    Stop
                End If
                
                Set Sessions = System.Sessions
                If (Sessions Is Nothing) Then
                    MsgBox "Could not create the Sessions collection objectstopping macro playback."
                    Stop
                End If
                
                Set Sess0 = System.ActiveSession
                If (Sess0 Is Nothing) Then
                    MsgBox "Could not create the Session object.  Stopping macro playback."
                    Stop
                End If
                  
                
                Warehouse = Range("B1").Value
                
            MsgBox "A search on 100 items takes about 3 min - please wait!

                    Try not to do anything while this is running - ASI is sensitive "

                 
               ' Application.ScreenUpdating = False
                
            
                '==========================================================================
                '   Get StoreOnHand :    Set ASI timeout values
                '==========================================================================
            
                 g_HostSettleTime = 250     ' milliseconds
            
                '==========================================================================
                '   Get StoreOnHand :    establish first & last rows on firofmain
                '==========================================================================
                
                FastFirofMain.Activate
                
                FirofMainFirst = 8
                FirofMainLast = LastRow()
                
                FirofMainRowInFocus = FirofMainFirst
                    
                ' ItemID = InputBox(" Enter a SKU ")
                
                ' *************************************************************************
                ' *********  assuming you are on the inventory available screen,
                ' *********  first clear out the warehouse field, with 10 spaces at 3,13
                ' *********  then put in SCS at 3,13
                
                Sess0.Screen.PutString "         ", 3, 13
                Sess0.Screen.PutString Warehouse, 3, 13
                
            
                '==========================================================================
                '  Get StoreOnHand :     Begin loop through FirofMain
                '==========================================================================
               
                 For FirofMainRowInFocus = FirofMainRowInFocus To FirofMainLast
            
                        ' *****************************************************************
                        ' *********  Get Input
                        
                        ' set focus so sheet scrolls
                        Cells(FirofMainRowInFocus, 1).Select
                        
                        ItemID = Cells(FirofMainRowInFocus, 1).Value
                        
            
                                   
          ' ************************************************************************************
          ' *********  then clear out the Item ID field at 3,36 with 17 spaces  
          ' *********  then put in the ItemID string at 3,36
                        Sess0.Screen.PutString "                 ", 3, 36
                        Sess0.Screen.PutString ItemID, 3, 36
                        
          ' ***********************************************************************************
          ' *********  Send the Enter Key to execute query
          ' *********
                        Sess0.Screen.SendKeys ("<Enter>")
                        
          ' **********************************************************************************
          ' *********  Wait to let Extra process the query or it will put in the old value
          ' *********  currently set to .5 sec
                        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                                  
          ' *************************************************************************************
          ' *********  then, get the AvailableToAllocate value into our variable AvailToAllocate
          ' *********  from row 15, col 22, length 11
                        OnHand = Sess0.Screen.GetString(16, 22, 11)
                        InTransit = Sess0.Screen.GetString(18, 22, 11)
                        OnOrder = Sess0.Screen.GetString(17, 64, 11)
                        CheckError = Sess0.Screen.GetString(24, 2, 8)
                                    
                        If CheckError = "        " Then
                                    
                                CheckResult1 = IsNumeric(OnHand)
                                           
                                If CheckResult1 = 0 Then
                                   StoreOnHand = 0
                                Else
                                    StoreOnHand = OnHand
                                End If
                                
                                CheckResult2 = IsNumeric(InTransit)
                                           
                                If CheckResult2 = 0 Then
                                   StoreInTransit = 0
                                Else
                                    StoreInTransit = InTransit
                                End If
                                
                                CheckResult3 = IsNumeric(OnOrder)
                                           
                                If CheckResult3 = 0 Then
                                   StoreOnOrder = 0
                                Else
                                    StoreOnOrder = OnOrder
                                End If
                       
                                FastFirofMain.Cells(FirofMainRowInFocus, 4).Value = StoreOnHand
                                FastFirofMain.Cells(FirofMainRowInFocus, 5).Value = StoreInTransit
                                FastFirofMain.Cells(FirofMainRowInFocus, 6).Value = StoreOnOrder
                                    
             ElseIf CheckError = "Invalid " Then
                      MsgBox "Invalid Sku Error - Skipping"
                      FastFirofMain.Cells(FirofMainRowInFocus, 11).Value = "Invalid Sku: " + ItemID
                     'Sess0.Screen.SendKeys ("<ESC>")
                            
             ElseIf CheckError = "Keyboard" Then
                      MsgBox "Keyboard Input Error - Attempting to Skip - If Stuck, hit ESC couple

                       times & Rerun"
                      FastFirofMain.Cells(FirofMainRowInFocus, 11).Value = "Keyboard Error"
                      'Sess0.Screen.SendKeys ("<ESC>")
                                
             Else: MsgBox "Unknown Error - Attempting to Skip"
                      FastFirofMain.Cells(FirofMainRowInFocus, 11).Value = "Unknown Error"
                      Sess0.Screen.PutString "         ", 3, 13
                      Sess0.Screen.PutString Warehouse, 3, 13
                    End If
                                                          
                Next FirofMainRowInFocus
                
                'Application.ScreenUpdating = True
                
                Range("a8").Select
                
                MsgBox "Finished !"
                
    Else
            
            MsgBox "Open ASI first !"
                    
    End If

End Sub

'===================================================================================
'===================================================================================
'   Highlight Violations
'===================================================================================
Sub HighLightViolations()

Dim rngSrc As Range
Dim NumRows As Integer
Dim FirstRow As Integer
Dim TargetRow As Integer
Dim ValueCol1, ValueCol2, valueCol3, Cell1, Cell2, Cell3 As Integer

Dim J As Integer


Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

    NumRows = LastRow()
    FirstRow = 8
    
    ValueCol1 = 4
    ValueCol2 = 5
    valueCol3 = 8
    
    For J = FirstRow To NumRows
                
            Cell1 = Cells(J, ValueCol1).Value
            Cell2 = Cells(J, ValueCol2).Value
            Cell3 = Cells(J, valueCol3).Value
    
            If Cell3 <> 0 Then
                If (Cell1 + Cell2) >= Cell3 Then
                    Rows(J).Select
                    Selection.Interior.ColorIndex = 36
                    Cells(J, 11).Value = "Violation: OnHand + Intransit more than 2WeekHistory"
                End If
            End If
    
    Next J
    
    Range("A8").Select
    
End Sub

'===================================================================================
'===================================================================================
'  Delete Violations
'===================================================================================
Sub DeleteViolations()

Dim rngSrc As Range
Dim NumRows As Integer
Dim FirstRow As Integer
Dim TargetRow As Integer
Dim ValueCol1, ValueCol2, valueCol3, Cell1, Cell2, Cell3 As Integer

Dim J As Integer


Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

    NumRows = LastRow()
    FirstRow = 8
    
    ValueCol1 = 4
    ValueCol2 = 5
    valueCol3 = 8
    
    For J = NumRows To FirstRow Step -1
            
            Cell1 = Cells(J, ValueCol1).Value
            Cell2 = Cells(J, ValueCol2).Value
            Cell3 = Cells(J, valueCol3).Value
    
            If Cell3 <> 0 Then
                If (Cell1 + Cell2) >= Cell3 Then
                    Rows(J).Select
                    Selection.Delete Shift:=xlUp
                    NumRows = NumRows - 1
                End If
            End If
    
    Next J
    
    Range("A8").Select
    
End Sub


'===================================================================================
'===================================================================================
' Delete InventoryUnavailables
'===================================================================================

Sub DeleteUnavailable()

Dim rngSrc As Range
Dim NumRows As Integer
Dim FirstRow As Integer
Dim TargetRow As Integer
Dim TargetCol As Integer

Dim J As Integer


Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

    NumRows = LastRow()
    FirstRow = 8
    
    TargetCol = 7
    
    For J = NumRows To FirstRow Step -1
        If Cells(J, TargetCol) < 500 Then
            Rows(J).Select
            Selection.Delete Shift:=xlUp
            NumRows = NumRows - 1
        End If
    Next J
    
    Range("A8").Select
    
End Sub



'===================================================================================
'===================================================================================
' SetQty to Zero        Sets the QtyDesired to Zero for all items
'===================================================================================

Sub SetQtyZero()

    Dim FirstRow, FinalRow, Count As Long
    
    Dim FastFirof As Workbook
    Dim FastFirofMain As Worksheet
    Dim FastFirofName As String
    

    Set FastFirof = ActiveWorkbook
    FastFirofName = FastFirof.Name
    Set FastFirofMain = Workbooks(FastFirofName).Worksheets("FrontPage")

    FirstRow = 8
    FinalRow = LastRow()
       
    For Count = FirstRow To FinalRow
        Cells(Count, 3) = 0
    Next Count
    
    Range("A8").Select
    
End Sub
'===================================================================================
'===================================================================================
'  Delete Zero Qty Desired
'===================================================================================
Sub DeleteZeroQtyDesired()

Dim rngSrc As Range
Dim NumRows As Integer
Dim FirstRow As Integer
Dim TargetRow As Integer
Dim ValueCol1, ValueCol2, valueCol3, Cell1, Cell2, Cell3 As Integer

Dim J As Integer


Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

    NumRows = LastRow()
    FirstRow = 8
    
    ValueCol1 = 3
    
    For J = NumRows To FirstRow Step -1
            
            Cell1 = Cells(J, ValueCol1).Value
    
            If Cell1 = 0 Then
                    Rows(J).Select
                    Selection.Delete Shift:=xlUp
                    NumRows = NumRows - 1
            End If
            
    
    Next J
    
    Range("A8").Select
    
End Sub

'===================================================================================
'===================================================================================
' Build FIROF
'===================================================================================

Sub BuildFirof()

    Dim CheckFIROFOpen As Range
    
    Dim FastFirof As Workbook
    Dim FastFirofName As String
    Dim FastFirofMain As Worksheet
    
    Dim wkb As Workbook
    Dim wks As Worksheet
    
    Dim Firof As Workbook
    Dim FirofName As String
    
    Dim FirofSkus As Worksheet
    Dim FirofSkuSheetName As String
    
    Dim FirofMainFirst, FirofMainLast As Long
    
    Dim TempSheet As Variant
            
    Dim FirofSkuFirstRow As Long
    Dim FirofLastRow As Long
    Dim TempLast, CopyRange As Range
        
    Dim rangestring, first, last, FirstString, laststring As String
      
    ' ********************************************************************************************
    ' *********  Check that Firof is open
    ' *********
    
    Set CheckFIROFOpen = Range("b2")
    Call SeeWhatsOpen
    If CheckFIROFOpen.Value = "Not Open" Then
        MsgBox "you need to open the FIROF Form first"
        Exit Sub
    End If
    
    ' ********************************************************************************************
    ' *********  Declare workbooks and sheets
    ' *********
    
    Set FastFirof = ActiveWorkbook
    FastFirofName = FastFirof.Name
    Set FastFirofMain = Workbooks(FastFirofName).Worksheets("FrontPage")
    
    FirofMainFirst = 8
    FirofMainLast = LastRow()
    
    For Each wkb In Workbooks
        wkb.Activate
        For Each wks In Worksheets
            If SheetExists("Accessory FIROF") Then
                Set Firof = ActiveWorkbook
                FirofName = ActiveWorkbook.Name
            End If
        Next
    Next
    
    FirofSkuSheetName = "Accessory FIROF"
    Set FirofSkus = Workbooks(FirofName).Worksheets(FirofSkuSheetName)
    
    'Set Range for filter
        
    FastFirofMain.Activate
    
    Module3.DeleteUnavailable
       
    FirofMainLastRow = LastRow()
        
    first = "A7"
    laststring = FirofMainLastRow
    last = "J" + laststring
    rangestring = first + ":" + last
    
    Range(rangestring).Select
  
    Set FirofTemp = Worksheets.Add
    FirofTemp.Name = "FirofTempData"
    
    FastFirofMain.Activate
       
    Application.ScreenUpdating = False
    
    Range(rangestring).Select
         
    With FastFirofMain
        .AutoFilterMode = False
        .Range("a7").AutoFilter Field:=7, Criteria1:=">0", Operator:=xlAnd
        .Range("a7").AutoFilter Field:=3, Criteria1:=">0", Operator:=xlAnd
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Workbooks(FastFirofName).Worksheets("FirofTempData").Range("A2")
    End With
    
    Selection.AutoFilter
    
    Range("a8").Select
        
    Set TempSheet = Workbooks(FastFirofName).Worksheets("FirofTempData")
    
    TempSheet.Activate

    Rows("1:8").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select

    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("I:J").Select
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select

    Columns("A:A").Select
    Selection.Cut
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B4").Select
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight

    TempLast = LastRow()

    first = "A1"
    laststring = TempLast
    last = "j" + laststring
    rangestring = first + ":" + last

    Range(rangestring).Copy

    FirofSkus.Activate
    Range("A11").PasteSpecial xlPasteValues

    Application.DisplayAlerts = False
    
    FastFirof.Worksheets("FirofTempData").Delete
    
    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

    FirofSkus.Select
    
    Range("B3").Select
           
End Sub


Sub FilterToZeroOnHand()

    Dim CheckFIROFOpen As Range
    
    Dim FastFirof As Workbook
    Dim FastFirofName As String
    Dim FastFirofMain As Worksheet
    
    Dim wkb As Workbook
    Dim wks As Worksheet
    
    Dim Firof As Workbook
    Dim FirofName As String
    
    Dim FirofSkus As Worksheet
    Dim FirofSkuSheetName As String
    
    Dim FirofMainFirst, FirofMainLast As Long
    
    Dim TempSheet As Variant
            
    Dim FirofSkuFirstRow As Long
    Dim FirofLastRow As Long
    Dim TempLast, CopyRange As Range
        
    Dim rangestring, first, last, FirstString, laststring As String

    Set FastFirof = ActiveWorkbook
    FastFirofName = FastFirof.Name
    Set FastFirofMain = Workbooks(FastFirofName).Worksheets("FrontPage")
    
       
    'Set Range for filter
        
    FastFirofMain.Activate
    
          
    FirofMainLastRow = LastRow()
        
    first = "A7"
    laststring = FirofMainLastRow
    last = "J" + laststring
    rangestring = first + ":" + last
    
    Range(rangestring).Select
  
    FastFirofMain.Activate
       
    Range(rangestring).Select
         
    With FastFirofMain
        .AutoFilterMode = False
        .Range("a7").AutoFilter Field:=4, Criteria1:="=0", Operator:=xlAnd
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        
    End With

End Sub