Option Explicit

Sub Importfind()
'Goes to the folder, finds the csv file, opens it and then does the next step.
ChDir "\\fngn.com\us\Projects\Print Production\Reports\DSG Drop reports"

Application.FindFile
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'The next step separates the package code into two columns, example SCE XE instead of SCEXE which allows for wasy lookup by users.
'Then is copies everything and paste the first 7 columns to the tab called Master. The Master tab has formula's that matches the code SCE to another tab in which I developed a way to get the sponsor name and the provider.
   Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("A:g").Select
    Selection.Copy
    
    Windows("DSG Drop Reports 2016.xlsm").Activate
    Columns("A:g").Select
    ActiveSheet.Paste
    Range("A1").Select
    Dim row, rwindex As Integer
    row = 0
    rwindex = 5
    Range("A2:G100000").Select
    Selection.Copy
    Sheets("Master").Select
    Range("a1").Select
    Do
       ActiveCell.Offset(1, 0).Activate
             
    Loop Until ActiveCell.Value = ""
 'The next step takes columns A-P and paste them to the Master resulting in:Job No  Sponsor Code    File Name   Date Dropped    Quantity    Status  POID  Provider    Sponsor Code    Sponsor name

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("Data").Select
    Application.CutCopyMode = False
    Range("A8").Select
    Rows("2:2").Select
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("k2:k100000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("E2:E100000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
        .SetRange Range("A1:k100000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("K1").Select
    Range("A2:G100000").Select
    Selection.ClearContents
    Sheets("Master").Select
    Cells.Select
    ActiveSheet.Range("$A$1:$G$174011").RemoveDuplicates Columns:=4, Header:= _
        xlYes
  'It also de-dupes because some of the records are duplicated. The csv files are a running list that keep adding records, which causes some duplication.
    
   Columns("E:E").Select
    Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
  
    
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
  
'The sponsor package codes tab comes from a table in sharepoint that needs to be updated i/e refreshed so any new data will be there.
    
    Sheets("Sponsor Package Codes").Select
    ActiveWorkbook.refreshall
'The Data tabe is just a sheet that I have the MACRO BUTTONS.
Sheets("DATA").Select
    Range("A1").Select
    

    
        
End Sub

Option Explicit
Const strMasterPath As String = "\\fngn.com\us\Projects\Print Production\Reports\Master.csv"    'The full name of the master csv
Const strPath As String = "\\fngn.com\us\Projects\Print Production\Reports\DSG Drop reports\"

Sub ProcessAttachment(oItem As MailItem)
Dim strFileName As String
Dim olAtt As Attachment
Dim lngCount As Long
    lngCount = 0
    On Error GoTo err_handler
    If oItem.Attachments.Count > 0 Then
        For Each olAtt In oItem.Attachments
            If Right(olAtt.FileName, 4) = ".csv" Then
                strFileName = strPath & olAtt.FileName
                olAtt.SaveAsFile strFileName
                Exit For
            End If
        Next olAtt
    End If
    If FileExists(strMasterPath) Then lngCount = lngCount + 1
    If lngCount = 1 Then
        GetCSVData strFileName, True
    Else
        GetCSVData strFileName
    End If
lbl_Exit:
    Exit Sub
err_handler:
    Err.Clear
    GoTo lbl_Exit
End Sub

Sub ProcessCSVFiles()
'Graham Mayor - www.gmayor.com
'The lngCount variable is used to determine whether to add the header row to the master file
Dim strFile As String
Dim lngCount As Long
    lngCount = 0
    If FileExists(strMasterPath) Then lngCount = lngCount + 1
    strFile = Dir$(strPath & "*.csv")
    While strFile <> ""
        lngCount = lngCount + 1
        If lngCount = 1 Then
            GetCSVData strPath & strFile, True
        Else
            GetCSVData strPath & strFile
        End If
        DoEvents
        strFile = Dir$()
    Wend
    MsgBox "Finished"
lbl_Exit:
    Exit Sub
End Sub

Sub GetCSVData(sfName As String, Optional bHeader As Boolean = False)
'Graham Mayor - www.gmayor.com
'If vHeader = true then write the header row
'The header row is a row that contains the text "Job No"
Dim sTextRow As String
Dim iFileNo As Integer
    iFileNo = FreeFile
    Open sfName For Input As #iFileNo
    Do While Not EOF(iFileNo)
        Line Input #iFileNo, sTextRow
        If bHeader Then
            AddToMaster sTextRow
        Else
            If InStr(1, sTextRow, "Job No") = 0 Then
                AddToMaster sTextRow
            End If
        End If
    Loop
    Close #iFileNo
lbl_Exit:
    Exit Sub
End Sub

Sub AddToMaster(strLine As String)
'Graham Mayor - www.gmayor.com
'strline is the line of text to be added
Dim oFSO As Object
Dim oFile As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = oFSO.OpenTextFile(strMasterPath, 8, True, 0)
    oFile.Write strLine & vbCrLf
    oFile.Close
lbl_Exit:
    Set oFSO = Nothing
    Set oFile = Nothing
    Exit Sub
End Sub

Private Function FileExists(strFullName As String) As Boolean
'Graham Mayor - www.gmayor.com
'strFullName is the name with path of the file to check
Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    If oFSO.FileExists(strFullName) Then
        FileExists = True
    Else
        FileExists = False
    End If
lbl_Exit:
    Set oFSO = Nothing
    Exit Function
End Function