Excel Macro check if file is open

Question

My recorded macro errs sometimes because the file is open already (See macro below).

Can somebody tell me how to change the macro to first check if the Excel worksheet (Volledige Ice export.xlsx) is open, and if it is open, close the file first? With regards to the latter, I see I can do that with:

Windows("Volledige Ice export.xlsx").Activate
ActiveWorkbook.Close

The full macro:

Sub Ice()

    Workbooks.OpenText Filename:="C:\Users\Doe\Documents\Ice\export.txt", _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
        Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 9), Array(8, 9), _
        Array(9, 2), Array(10, 1), Array(11, 9), Array(12, 1), Array(13, 9), Array(14, 9), Array(15 _
        , 9), Array(16, 9), Array(17, 9), Array(18, 9), Array(19, 9), Array(20, 9), Array(21, 2), _
        Array(22, 9), Array(23, 9), Array(24, 2), Array(25, 2)), DecimalSeparator:=".", _
        ThousandsSeparator:=",", TrailingMinusNumbers:=True
    Windows("Ice_macro.xlsm").Activate
    Windows("export.txt").Activate
    Columns("A:B").Select
    Selection.ColumnWidth = 21
    Columns("D:D").Select
    Selection.ColumnWidth = 18
    Columns("E:E").Select
    Selection.ColumnWidth = 15
    Columns("F:F").Select
    Selection.ColumnWidth = 60
    Columns("G:G").Select
    Selection.ColumnWidth = 45
    Columns("H:H").Select
    Selection.ColumnWidth = 12
    Columns("L:L").Select
    Selection.ColumnWidth = 45
    Columns("G:G").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A1").Select
    Selection.AutoFilter
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Doe\Documents\Ice\Volledige Ice export.xlsx", FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close

End Sub

You may want to read [How to avoid using `Select` in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) - for example, you can use `Windows("Volledige Ice export.xlsx").Close` directly, instead of activating the workbook first, or `Columns("A:B").ColumnWidth = 21` — Chronocidal, Jan 18 '20 at 11:31
Try to close the workbook but use Error Trapping for a `subscript out of range` error. — Ron Rosenfeld, Jan 18 '20 at 11:58
https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open — Siddharth Rout, Jan 18 '20 at 16:49

Vitaliy Prushak · Answer 1 · 2020-01-18T18:30:48.613

Below you'll find possible solution, together with Chronocidal's comment implementation. You should compare your recorded code to this to understand it.
In short, Chronocidal means that this:

Columns("H:H").Select
Selection.ColumnWidth = 12

is the same as this:

Columns("H:H").ColumnWidth = 12

but second version is a lot more efficient.

The code:

Sub Ice()
Dim wsExport As Worksheet
Dim wbIce As Workbook

' try to assign workbook to variable
On Error Resume Next
' in case wb is closed - there will be an error
Set wbIce = Workbooks("Volledige Ice export")
On Error GoTo 0

' if wb is open it will be assigned to variable
If Not wbIce Is Nothing Then
    ' close without saving
    wbIce.Close False
End If

Workbooks.OpenText fileName:="C:\Users\Doe\Documents\Ice\export.txt", _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
    Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 9), Array(8, 9), _
    Array(9, 2), Array(10, 1), Array(11, 9), Array(12, 1), Array(13, 9), Array(14, 9), Array(15 _
    , 9), Array(16, 9), Array(17, 9), Array(18, 9), Array(19, 9), Array(20, 9), Array(21, 2), _
    Array(22, 9), Array(23, 9), Array(24, 2), Array(25, 2)), DecimalSeparator:=".", _
    ThousandsSeparator:=",", TrailingMinusNumbers:=True

Set wsExport = ActiveSheet

With wsExport
    .Columns("A:B").ColumnWidth = 21
    .Columns("D:D").ColumnWidth = 18
    .Columns("E:E").ColumnWidth = 15
    .Columns("F:F").ColumnWidth = 60
    .Columns("G:G").ColumnWidth = 45
    .Columns("H:H").ColumnWidth = 12
    .Columns("L:L").ColumnWidth = 45
    With .Columns("G:G").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    .Cells("A1").AutoFilter
End With

With wsExport.Parent
    .SaveAs fileName:= _
    "C:\Users\Doe\Documents\Ice\Volledige Ice export.xlsx", FileFormat _
    :=xlOpenXMLWorkbook, CreateBackup:=False
    .Close
End With

End Sub

@EEM Actually in this case - yes, but this is not the worst habit, especially if one would like to catch errors further with something like `If Err.Number <> 0` — Vitaliy Prushak, Jan 18 '20 at 16:51
I don't understand, why it would make a difference if an error is been validated using If `Err.Number <> 0` as `On Error Goto 0` clears the `Err` object. That's the reason why `Err.Clear` is not needed. — EEM, Jan 18 '20 at 16:55
@EEM Didn't know about that, thanks! I was sure that `On Error GoTo 0` just [Disables any enabled error handler in the current procedure.](https://learn.microsoft.com/en-gb/office/vba/language/reference/user-interface-help/on-error-statement). Edited answer. — Vitaliy Prushak, Jan 18 '20 at 18:30

Excel Macro check if file is open

1 Answers1