CODEWITHSUNDEEP
vbaexcel
Gary Dorman
Gary Dorman

Reputation: 477

DIR is not finding the next file when passing workbook into another sub

Could someone please help me out? My DIR function finds the first file in the specified folder (in the "loopThroughMNFiles sub) but after that it can't find the next file. It has something to do with me passing the workbook into the other "INSINQ_Macro" sub but i'm not sure how to correct. Thanks in advance! 

    Sub loopThroughMNFiles()

    Application.ScreenUpdating = False

    Dim MnLoopingFolder As String

    Dim MnWbk As Workbook

    Dim MnFile As String

    Dim sheetName As String

    MnLoopingFolder = "C:\Users\xxxxxx\Desktop\outlook-attachments\MN Reports\2-26-18\INSINQ\"

    MnFile = Dir(MnLoopingFolder) 'DIR gets the first file of the folder

'Loop through all files in a folder until DIR cannot find anymore

    Do While MnFile <> “”

'Opens the file and assigns to the wbk variable for future use

        Set MnWbk = Workbooks.Open(FileName:=MnLoopingFolder & MnFile)

        Call INSINQ_Macro(MnWbk)

'MnWbk.Close

        MnFile = Dir 'DIR gets the next file in the folder

    Loop

    Application.ScreenUpdating = True

    MsgBox "Task Finished"
End Sub


    Sub INSINQ_Macro(InsinqSourceBook As Workbook)

        Application.DisplayAlerts = False

        Application.ScreenUpdating = False

        Dim OutlookApp As Outlook.Application

        Dim MItem As Outlook.MailItem

        Dim insinqWorkbookName As String

        Dim fileDate As String

        Dim folderDate As String

        fileDate = Format(Date, "yymmdd")

        folderDate = Format(Date, "mm-dd-yy")

        Dim sUserName As String

        sUserName = Environ$("username")

        Dim ws As Worksheet

        Dim success As Boolean

        Dim localDesktopMnPath As String

        For Each ws In Sheets

            sheetName = LCase(ws.Name)

            If sheetName Like "*high" Or sheetName Like "*mark" Then

                InsinqSourceBook.ws.Delete

            End If

        Next

        localDesktopMnPath = "C:\Users\" + sUserName + "\Desktop\MN Weekly\" + folderDate + "\"

        If Dir(localDesktopMnPath, vbDirectory) = "" Then

            MkDir localDesktopMnPath

        End If

        success = True

        insinqWorkbookName = LCase(InsinqSourceBook.Name)

        If InStr(insinqWorkbookName, "tenv2") <> 0 Then

            InsinqSourceBook.SaveAs FileName:=localDesktopMnPath + fileDate + " Minnesota Users on INSINQ Security Tables TENV2.csv", fileFormat:=xlCSV, CreateBackup:=False

            Set InsinqSourceBook = ActiveWorkbook

        ElseIf InStr(insinqWorkbookName, "tenv3") <> 0 Then

            InsinqSourceBook.SaveAs FileName:=localDesktopMnPath + fileDate + " Minnesota Users on INSINQ Security Tables TENV3.csv", fileFormat:=xlCSV, CreateBackup:=False

            Set InsinqSourceBook = ActiveWorkbook

        ElseIf InStr(insinqWorkbookName, "tenv4") <> 0 Then

            InsinqSourceBook.SaveAs FileName:=localDesktopMnPath + fileDate + " Minnesota Users on INSINQ Security Tables TENV4.csv", fileFormat:=xlCSV, CreateBackup:=False

            Set InsinqSourceBook = ActiveWorkbook

        ElseIf InStr(insinqWorkbookName, "tenv5") <> 0 Then

            InsinqSourceBook.SaveAs FileName:=localDesktopMnPath + fileDate + " Minnesota Users on INSINQ Security Tables TENV5.csv", fileFormat:=xlCSV, CreateBackup:=False

            Set InsinqSourceBook = ActiveWorkbook

        ElseIf InStr(insinqWorkbookName, "tenv6") <> 0 Then

            InsinqSourceBook.SaveAs FileName:=localDesktopMnPath + fileDate + " Minnesota Users on INSINQ Security Tables TENV6.csv", fileFormat:=xlCSV, CreateBackup:=False

            Set InsinqSourceBook = ActiveWorkbook

        ElseIf InStr(insinqWorkbookName, "tenv7") <> 0 Then

            InsinqSourceBook.SaveAs FileName:=localDesktopMnPath + fileDate + " Minnesota Users on INSINQ Security Tables TENV7.csv", fileFormat:=xlCSV, CreateBackup:=False

            Set InsinqSourceBook = ActiveWorkbook

        ElseIf InStr(insinqWorkbookName, "tenvb") <> 0 Then

            InsinqSourceBook.SaveAs FileName:=localDesktopMnPath + fileDate + " Minnesota Users on INSINQ Security Tables TENVB.csv", fileFormat:=xlCSV, CreateBackup:=False

            Set InsinqSourceBook = ActiveWorkbook

        ElseIf InStr(insinqWorkbookName, "tenvc") <> 0 Then

            InsinqSourceBook.SaveAs FileName:=localDesktopMnPath + fileDate + " Minnesota Users on INSINQ Security Tables TENVC.csv", fileFormat:=xlCSV, CreateBackup:=False

            Set InsinqSourceBook = ActiveWorkbook

        ElseIf InStr(insinqWorkbookName, "prod") <> 0 Then

            InsinqSourceBook.SaveAs FileName:=localDesktopMnPath + fileDate + " Minnesota Users on INSINQ Security Tables PROD.csv", fileFormat:=xlCSV, CreateBackup:=False

            Set InsinqSourceBook = ActiveWorkbook

        Else

            success = False

            InsinqSourceBook.Close savechanges:=False

'send failure email - filename wasn't named correctly

            Exit Sub

        End If

        InsinqSourceBook.Close savechanges:=False

        Application.ScreenUpdating = True

' send succesful email

    End Sub

Upvotes: 1

Views: 410

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Here's what I meant in my comment:

Sub loopThroughMNFiles()

    Const MnLoopingFolder As String = "C:\Users\xxxxxx\Desktop\outlook-attachments\MN Reports\2-26-18\INSINQ\"

    Dim colFiles As New Collection, f
    Dim MnWbk As Workbook, MnFile As String, sheetName As String

    'add the files to a collection
    MnFile = Dir(MnLoopingFolder)
    Do While MnFile <> ""
        colFiles.Add MnFile
        MnFile = Dir()
    Loop

    Application.ScreenUpdating = False
    'loop over the filenames from the collection
    For Each f In colFiles
        INSINQ_Macro Workbooks.Open(Filename:=MnLoopingFolder & f)
    Next
    Application.ScreenUpdating = True
    MsgBox "Task Finished"
End Sub

Upvotes: 2

Related Questions