Prevent Workbook From Reopening as Read Only

Logit · #1 11-11-2024, 04:11 PM

The following macro copies/pastes data from the "Out" sheet located in each workbook in the same folder. When done the master workbook closes and then Excel reopens the master workbook as Read Only.

I want the workbook to either remain open when done or to close and not reopen as Read Only.

Any suggestions ? Thank you.

Code:

Option Explicit

Sub CopyDataFromWorkbooks6()
    Dim wb As Workbook
    Dim masterWb As Workbook
    Dim sourceWb As Workbook
    Dim sourceSheet As Worksheet
    Dim masterSheet As Worksheet
    Dim folderPath As String
    Dim fileName As String
    Dim lastRow As Long
    'Dim FullName As String

    ' Set the folder path
    folderPath = ThisWorkbook.Path & "\" ' Ensure the path ends with a backslash
    fileName = Dir(folderPath & "*.xls*")
    
    ' Create or set the Master Workbook
    Set masterWb = ThisWorkbook
    Set masterSheet = masterWb.Sheets("Sheet1")
    
    ' Clear existing data in Master Sheet
    'masterSheet.Cells.Clear
    
    ' Disable alerts
    Application.DisplayAlerts = False
    
    ' Loop through each workbook in the folder
    Do While fileName <> ""
    
        Set sourceWb = Workbooks.Open(folderPath & fileName, ReadOnly:=True)
        On Error Resume Next
        Set sourceSheet = sourceWb.Sheets("Out")
        On Error GoTo 0
        
        If Not sourceSheet Is Nothing Then
         
            ' Find the last row in Master Sheet
            lastRow = masterSheet.Cells(masterSheet.Rows.Count, 1).End(xlUp).Row
            
            ' Copy UsedRange from source sheet and paste to Master Sheet
            sourceSheet.UsedRange.Copy
            masterSheet.Cells(lastRow + 2, 1).PasteSpecial Paste:=xlPasteAll
            
            ' Close the source workbook
            sourceWb.Close False
         masterWb.Save
         
        End If
        
        
        fileName = Dir

        
    Loop
    
'ThisWorkbook.Close

    Application.DisplayAlerts = True

ThisWorkbook.Save
Application.Workbooks.Open (ThisWorkbook.FullName)
    
End Sub

Logit · #2 11-12-2024, 07:29 PM

I finally located a solution. New macro with changes :

Code:

Sub CopyDataFromWorkbooks6()
    Dim wb As Workbook
    Dim masterWb As Workbook
    Dim sourceWb As Workbook
    Dim sourceSheet As Worksheet
    Dim masterSheet As Worksheet
    Dim folderPath As String
    Dim fileName As String
    Dim lastRow As Long

    ' Set the folder path
    folderPath = ThisWorkbook.Path & "\" ' Ensure the path ends with a backslash
    fileName = Dir(folderPath & "*.xls*")
    
    ' Create or set the Master Workbook
    Set masterWb = ThisWorkbook
    Set masterSheet = masterWb.Sheets("Sheet1")
    
    
    ' Disable alerts
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    ' Loop through each workbook in the folder
    Do While fileName <> ""
    
        Set sourceWb = Workbooks.Open(folderPath & fileName, ReadOnly:=False)
        On Error Resume Next
        Set sourceSheet = sourceWb.Sheets("Out")
        On Error GoTo 0
        
        If Not sourceSheet Is Nothing Then
         
            ' Find the last row in Master Sheet
            lastRow = masterSheet.Cells(masterSheet.Rows.Count, 1).End(xlUp).Row
            
            ' Copy UsedRange from source sheet and paste to Master Sheet
            sourceSheet.UsedRange.Copy
            masterSheet.Cells(lastRow + 2, 1).PasteSpecial Paste:=xlPasteAll
            
            ' Close the source workbook
            sourceWb.Close False
            masterWb.Save
         
        End If
        
        
        fileName = Dir

        
    Loop
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

ThisWorkbook.Save
    
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Open second instance of a workbook 2010 as read only method fails for 2013	DBenz	Excel	5	02-16-2023 03:53 PM
Workbook transferred to another computer, keeps opening as "read-only"	ue418	Excel	1	10-28-2017 12:39 PM
Autofill SaveAs File Name box using read only workbook	newbieX	Excel Programming	1	05-20-2015 11:16 PM
Read text Report file with VBA and write parsed fields to Excel workbook	tpcervelo	Excel Programming	1	01-05-2012 10:14 PM
Any way to prevent mail appearing as read after opening from a desktop alert?	pumpkin_feet	Outlook	0	10-24-2011 06:55 AM