Excel Workbook Closes after running Macro

[howard]

I have the following macro to open a workbook and then to fo to a named cell on a sheet

However when running the macro, the workbook closes

If I manually open the soource workbook and then run the macro, The workbook does not close. I suspect that this portion of the code is causing the issue

 Windows("STATS BR ales.xls").Activate
 Sheets("Sales Acc Numbers").Select
Application.Goto Reference:="Sales"

 Sub Open_SalesFile()
    
   
ChDir "C:\My Documents"
Dim A As Variant
Dim LR As Long

MsgBox "Select Current Years Sales File"

If TypeName(A) = "Boolean" Then Exit Sub


Dim FileAry As Variant, Fle As Variant
   With Application.FileDialog(3)
      .InitialFileName = "C:\My Documents\"
      .AllowMultiSelect = True
      .InitialFileName = "Sales.*.xls*"
      If .Show Then Set FileAry = .SelectedItems()
   End With
   For Each File In FileAry
   If TypeName(A) = "Boolean" Then Exit Sub

   
      With Workbooks.Open(File)
   
     


End With
Next
Windows("STATS BR ales.xls").Activate
 Sheets("Sales Acc Numbers").Select
Application.Goto Reference:="Sales"

End Sub

It would be apreciated if someone could amend my code to prevent file closing down unexpectently

 

[HaHoBe]

Hi howard,

A is dimmed as Type Variant but no value is set. So both the checks inside your sniplet are obsolete. Maybe check your code and cancel when the Dialog is displayed...

Your description about selecting a range is not clear to me: inside each opened workbook or only at the end?

Sub Open_SalesFile()
' https://www.mrexcel.com/board/threads/excel-workbook-clolses-after-running-macro.1228098/
Dim FileAry As Variant
Dim Fle As Variant

ChDir "C:\My Documents"

MsgBox "Select Current Years Sales File"

With Application.FileDialog(3)
  .InitialFileName = "C:\My Documents\"
  .AllowMultiSelect = True
  .InitialFileName = "Sales.*.xls*"
  If .Show <> 0 Then
    Set FileAry = .SelectedItems()
  Else
    Exit Sub
  End If
End With
For Each Fle In FileAry
  With Workbooks.Open(Fle)
    On Error Resume Next
    Application.Goto Reference:="Sales"
    Err.Clear
    On Error GoTo 0
  End With
Next Fle
Workbooks("STATS BR ales.xls").Activate
'Sheets("Sales Acc Numbers").Select
Application.Goto Reference:="Sales"

End Sub

Ciao,
Holger

 

[howard]

Thanks for the hrlp Holger

The range must only be seelected at the end

 

[HaHoBe]

Hi howard,

instead of

For Each Fle In FileAry
  With Workbooks.Open(Fle)
    On Error Resume Next
    Application.Goto Reference:="Sales"
    Err.Clear
    On Error GoTo 0
  End With
Next Fle

use

For Each Fle In FileAry
  Workbooks.Open (Fle)
Next Fle

If there is only one range in "STATS BR ales.xls" named "Sales" then you must only activate that workbook and the proper cell will be selected (no need to select the sheet then).

Holger

 

[howard]

Many Thanks Holger