Code Opens File but Isn't Closing It

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
Can someone help me understand why the line
VBA Code:
s.Close SaveChanges:=False
doesn't seem to be closing the file? I have the same line in 16 other modules, and they are all closing just fine.

VBA Code:
Option Explicit
Sub ImportLMData()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook, s As Workbook
Dim mD As Worksheet, w As Worksheet
Dim fP As String, fN As String, fE As String
Dim i As Integer, WSCount As Integer
Dim rng As Range
Dim mDLR As Long, mNLR As Long, sDLR As Long

Set m = ThisWorkbook
Set mD = m.Sheets("Data")

mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row

fP = "C:\Users\Import Files\"
fN = "LM"
fN = Dir(fP & fN & "*.xlsx")

Set s = Workbooks.Open(fP & fN)

WSCount = s.Worksheets.Count

For Each w In s.Worksheets
    If w.Name <> "Notes" Then
        If w.Range("A2").Value = "" Then
            Exit Sub
        Else
        
        sDLR = w.Range("A" & Rows.Count).End(xlUp).Row
        mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row
        
        With w.Range("B2:B" & sDLR).Copy
            mD.Range("F" & mDLR + 1).PasteSpecial xlPasteValues
        End With

        With w.Range("D2:D" & sDLR).Copy
            mD.Range("I" & mDLR + 1).PasteSpecial xlPasteValues
        End With

        With w.Range("A2:A" & sDLR).Copy
            mD.Range("Q" & mDLR + 1).PasteSpecial xlPasteValues
        End With
        
        With w.Range("E2:E" & sDLR).Copy
            mD.Range("O" & mDLR + 1).PasteSpecial xlPasteValues
        End With
        
        mNLR = mD.Range("F" & Rows.Count).End(xlUp).Row

        mD.Range("A" & mDLR + 1 & ":A" & mNLR).Value = "LM"
        
        mD.Range("B" & mDLR + 1 & ":B" & mNLR).Value = "LM"
        
        mD.Range("C" & mDLR + 1 & ":C" & mNLR).Value = "LOB"
     
        With mD.Range("E" & mDLR + 1 & ":E" & mNLR)
            .Value = "=TODAY()"
            .Value = .Value
        End With
        
        With mD.Range("J" & mDLR + 1 & ":J" & mNLR)
            .Value = "=IF(RC[-1]="""","""",RC[-1])"
            .NumberFormat = "MMM-YY"
            .Value = .Value
        End With
        End If
    End If
Next w

s.Close SaveChanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Are you sure that code runs? You have an Exit Sub line earlier in the code.
 
Upvote 0
Are you sure that code runs? You have an Exit Sub line earlier in the code.
Yes, it runs as data is being populated on the destination worksheet. That earlier exit sub will only occur if the IF statement is true.
 
Upvote 0
Yes but the Exit sub line could be run in response to any worksheet having blank A2 after a previous one has copied data.
 
Upvote 0
Solution
Yes but the Exit sub line could be run in response to any worksheet having blank A2 after a previous one has copied data.
Darn good point! I added a GoTo directing the code to the file closure line and it's resolved. Thank you!!!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top