I cannot get workbooks to close after processing them using the .Close method.

[OaklandJim]

I cannot understand why my code won't close workbooks after they are processed. I apologize in advance to folks who like tighter code. I try to comment a lot so when I revisit code or if someone else is using it there are clues about what is happening. I'd really appreciate a second set of MrExcel eyes.

Sub CopyPasteToolings()

'   Workbook object for Target workbook
    Dim wbTarget As Workbook

'   Worksheet object for the Tooling worksheet in the Master workbook.
    Dim wsMasterTooling As Worksheet
   
'   Worksheet object for the just opened workbook.
    Dim wsTargetTooling As Worksheet
       
    Dim asFolders() As String
   
'   Count of folders to process.
    Dim iFoldersCount As Long
   
'   Used to loop through folders.
    Dim iFolder As Long
   
'   Count of files in folder.
    Dim iFilesCount As Long
   
'   Folder containing the source and target folders.
    Dim sBasePath As String
   
'   Full path plus filename
    Dim sFileSpec As String
   
'   Used to store the list of files after from the Dir function.
    Dim vFiles As Variant
   
'   Specify the number of folders to process.
    iFoldersCount = 3

'   Resize array to accommodate all folder names.
    ReDim asFolders(iFoldersCount) '<< set the Redim value to the number of folders to process
   
'   Load array with folder names. Ideally these are from values in a worksheet. That
'   way you do not have to access this code to change the folders to be accessed.
'
    asFolders(1) = "Other"
    asFolders(2) = "Plastic"
    asFolders(3) = "Steel"
   
    Application.ScreenUpdating = False
   
'   Master workbook has a worksheet named Tooling. Set the wsMasterTooling
'   worksheet object to point to that Tooling worksheet.
    Set wsMasterTooling = ThisWorkbook.Worksheets("Tooling")
   
'   Get the base path for all folders being processed.
    sBasePath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1) & "\"

    For iFolder = 1 To iFoldersCount
       
        vFiles = Dir(sBasePath & asFolders(iFolder) & "\")
       
        While (vFiles <> "")
       
            iFilesCount = iFilesCount + 1

'           This is the full "file specification" with full path and filename.
            sFileSpec = sBasePath & asFolders(iFolder) & "\" & vFiles

            Set wbTarget = Nothing
           
'           Point the wbTarget object to the workbook that is opened.
'           If the file being processed is not an Excel workbook then
'           this would cause an error.
            On Error Resume Next
            Set wbTarget = Workbooks.Open(sFileSpec)
            On Error GoTo 0
           
'           Check that the workbook exists before processing the file.
            If Not wbTarget Is Nothing And Right(sFileSpec, 3) <> "pdf" _
             Then
           
                With wbTarget

                    Set wsTargetTooling = Nothing
                   
'                   Point the wsTargetTooling object to the Tooling worksheet.
'                   If the workbook being processed does not contain the specified
'                   worksheet (Tooling) then this would cause an error.
                    On Error Resume Next
                    Set wsTargetTooling = wbTarget.Worksheets("Tooling")
                    On Error GoTo 0
                   
'                   Check that the worksheet exists before processing the file.
                    If Not wsTargetTooling Is Nothing _
                     Then
                    
                        With wsTargetTooling
                           
'                           Clear existing content in the Tooling worksheet in the target workboook.
                            .Cells.Clear
           
'                           Copy values from the Master workbook, Tooling worksheet.
                            wsMasterTooling.Cells.Copy
           
'                           Paste everything to the target worksheet.
                            .Paste Destination:=.Cells
                               
'                            Macro button is copied too, delete it.
                            .Shapes.Range(Array("UpdateSheetsButton")).Delete
                           
                        End With 'wsTargetTooling
                           
'                       Call sub that adds names to the worksheet.
                        Call AddNames(wsTargetTooling)
               
                    End If 'Not wsTargetTooling Is Nothing
                   
                    'On Error Resume Next
                   
'                   .Close SaveChanges:=True
                   
                    wbTarget.Close SaveChanges:=True
                   
                    'On Error GoTo 0

                End With 'wbTarget
           
            End If 'Not wbTarget Is Nothing
           
            vFiles = Dir
       
        Wend
           
    Next iFolder

    Application.CutCopyMode = False
   
    MsgBox "Done processing " & iFilesCount & " files.", vbInformation, "Updating files with Toolings data"
   
End Sub

 

[Norie]

Apart from not closing the workbooks does the code work?

If you add a breakpoint here (F9) and run the code, does it actually break at this line?

wbTarget.Close SaveChanges:=True

 

[OaklandJim]

Oh man, I have to apologize for posting about an issue even though what I have works. Honest, it was not working but now it is! Errggh.

I do have another issue. I cannot get my code to stop opening pdf files. I tried using this code to avoid opening pdf file butSub still opens them.

If Not wbTarget Is Nothing And Right(sFileSpec, 3) <> "pdf" _

 

[OaklandJim]

I tried a different approach to avoiding pdf files. I cannot understand why the code I wrote originally does not work. The same statement works this way

            If Right(sFileSpec, 3) = "pdf" _
             Then
                wbTarget.Close
            ElseIf Not wbTarget Is Nothing _
             Then

 

[Alex Blakenburg]

I may be misreading this but you are opening the PDF before the If statement which excludes the processing of PDFs.
But you have your close statement inside the If routine that has excluded PDFs.

If you are only picking up xl files why not use:

vFiles = Dir(sBasePath & asFolders(iFolder) & "\*.xls*")

            ' PDF Opens here
            Set wbTarget = Workbooks.Open(sFileSpec)

           
'           Check that the workbook exists before processing the file.
            If Not wbTarget Is Nothing And Right(sFileSpec, 3) <> "pdf" _
             Then
                 ' ..... rest of code - EXCLUDES pdf files
                 ' Close happens here inside If statement
                        wbTarget.Close SaveChanges:=True
                   
            End If 'Not wbTarget Is Nothing

 

[OaklandJim]

Thank you Alex.

 

[Alex Blakenburg]

You're welcome. Thanks for the follow up.