Keeping file open and saving a copy

[Coleman34]

I am not even sure that the base functionality of Excel can do this, BUT I am hopeful. My conundrum is this: I am looping over a file deleting rows based on a given variable. if a cell value does not match the given variable then the row that cell lives in is deleted. My problem is the File is 250MB (HUGE) and it takes a long time to open. Is there a way to keep the original file open, make the deletions and save it, all while keeping the original in tact and able to be reused for the next variable in the loop?

My guess is a hard "No", but there are smarter people then I, out there.

[Greg M]

Hi there,

The following code will save a copy of the workbook which contains this routine, as an Excel workbook with whatever filename is specified in the SaveAs dialog box:

Option Explicit


Sub SaveCopyOfThisWorkbook()

    Const sFILE_FILTER  As String = "Excel Files (*.xlsx), *.xlsx"

    Dim vNewFileName    As Variant
    Dim sNewFileName    As String
    Dim iErrorNo        As Long

    vNewFileName = Application.GetSaveAsFilename(FileFilter:=sFILE_FILTER)

    If vNewFileName <> False Then

        sNewFileName = CStr(vNewFileName)

        On Error Resume Next
            ThisWorkbook.SaveCopyAs Filename:=sNewFileName
            iErrorNo = Err.Number
        On Error GoTo 0

        If iErrorNo = 0 Then

              MsgBox "The file """ & sNewFileName & """ has been saved", vbInformation

        Else: MsgBox "An error occurred - the copy workbook has NOT been saved", _
                      vbExclamation
        End If

    End If

End Sub

The copy workbook will be saved as an Excel workbook (i.e. macro-free).

An error message will be displayed if (for whatever reason) the copy file fails to save successfully.


If you wish to save the copy workbook as a Macro-Enabled workbook the FileFilter statement should be changed as follows:

    Const sFILE_FILTER  As String = "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm"

Hope this helps - please let me know how you get on.

Regards,

Greg M