Macro to close open files without saving it

**Roma1r** · 01-08-2019, 09:11 AM

Require assistance with setting up a macro.

The macro the way it’s currently setup reads a filename in a cell reference (data extraction worksheet, cell reference b7) and opens that file, copies data back to the main workbook and then opens the next file (data extraction worksheet, cell reference b8). Before the file copy, it unhides a worksheet (where it’s copying from).

I need assistance with closing the files opened (in cell reference b7 and b8) within the macro, and no need to save.

Sub Open_ExistingWorkbook()

    filetoopen = ThisWorkbook.Worksheets("data extraction").Range("b7")
    Workbooks.Open Filename:=filetoopen
    Sheets("Payspace").Visible = True
    Sheets("Payspace").Select
    Range("A4").Select
    Range("A4:D117").Select
    Selection.Copy
    Windows("T&O PaySpace upload template_ 2019 - test.xlsm").Activate
    Sheets("Location A").Select
    Range("A7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-21
    Sheets("Data extraction").Select
    Range("A16").Select
         
    filetoopen = ThisWorkbook.Worksheets("data extraction").Range("b8")
    Workbooks.Open Filename:=filetoopen
    Sheets("Payspace").Visible = True
    Sheets("Payspace").Select
    Range("A4").Select
    Range("A4:D117").Select
    Selection.Copy
    Windows("T&O PaySpace upload template_ 2019 - test.xlsm").Activate
    Sheets("Location B").Select
    Range("A7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-21
    Sheets("Data extraction").Select
    Range("A16").Select
       
End Sub

**BellyGas** · 01-08-2019, 09:18 AM

When you open the workbook assign it to a variable:


Dim WorkbookToOpen as Workbook

set WorkbooktoOpen = workbooks.open(Filetoopen)

Then, to close the workbook you simply:


Application.Displayalerts = false
WorkbookToOpen.close
Application.displayalerts = true

**mohan.r1980** · 01-08-2019, 10:53 AM

Hi

i am trying to make easy your code
try

Sub Open_ExistingWorkbook()
Dim TempWnd As Workbook, fName As String
Dim r As Range
    
    For Each r In Sheets("Data extraction").Range("B7:B14")
        If r.Address = "$B$7" Then
            filetoopen = ThisWorkbook.Worksheets("data extraction").Range("b7")
            fName = Dir(filetoopen)
            Workbooks.Open Filename:=filetoopen
            Set TempWnd = Workbooks(fName)
            Sheets("Payspace").Visible = True
            Sheets("Payspace").Select
            Range("A4:D117").Copy ThisWorkbook.Sheets("Alrode").Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(5)
            TempWnd.Close False
        End If
        
        If r.Address = "$B$8" Then
                filetoopen = ThisWorkbook.Worksheets("data extraction").Range("b8")
                fName = Dir(filetoopen)
                Workbooks.Open Filename:=filetoopen
                Set TempWnd = Workbooks(fName)
                
                Sheets("Payspace").Visible = True: Sheets("Payspace").Range("A4").Select
                TempWnd.Close False
                 
        End If
        
    Next r
    
 End Sub

**mohan.r1980** · 01-08-2019, 10:24 AM

try

Sub Open_ExistingWorkbook()
Dim TempWnd As Workbook, fName As String
    
    filetoopen = ThisWorkbook.Worksheets("data extraction").Range("b7")
    
    fName = Dir(filetoopen)
    Workbooks.Open Filename:=filetoopen
    Set TempWnd = Workbooks(fName)
    
    Sheets("Payspace").Visible = True
    Sheets("Payspace").Select
    Range("A4").Select
    Range("A4:D117").Select
    Selection.Copy
    Windows("T&O PaySpace upload template_ 2019 - test.xlsm").Activate
    Sheets("Alrode").Select
    Range("A7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-21
    Sheets("Data extraction").Select
    Range("A16").Select
    
    TempWnd.Close False
    
    filetoopen = ThisWorkbook.Worksheets("data extraction").Range("b8")
    
    fName = Dir(filetoopen)
    Workbooks.Open Filename:=filetoopen
    Set TempWnd = Workbooks(fName)
    
    Sheets("Payspace").Visible = True
    Sheets("Payspace").Select
    Range("A4").Select
    
    TempWnd.Close False
End Sub

**mohan.r1980** · 01-09-2019, 02:56 AM

Your welcome and thanks for rep+

Macro to close open files without saving it

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to close open files without saving it

Re: Macro to close open files without saving it

Re: Macro to close open files without saving it

Re: Macro to close open files without saving it

Re: Macro to close open files without saving it

Thread Information

Users Browsing this Thread

Similar Threads

Open and close files using excel macro

[SOLVED] macro to open all files in a folder, save them, then close them

[SOLVED] Macro to open and close all files in a folder and sub folders

Macro to open, copy/paste, and close files with variable name

Macro to Open, Save and Close Excel Files

Macro to Open, Print and Close multiple PDF Files

Macro to open all files, make changes, save and close

Bookmarks

Bookmarks

Posting Permissions