If user hits 'Cancel', workbook is closing

[Glasgowsmile]

I have some vba to import a document and I call it using a button but I found if I select nothing and hit 'cancel', it's closing the entire workbook. How do I avoid this?

Code I'm calling:

Sub ImportR()
 Application.DisplayAlerts = False
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
 
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Dim ws As Worksheet
   
   Set wkbCrntWorkBook = ActiveWorkbook
   
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Set ws = wkbSourceBook.Sheets(1)
         
        With Sheets(1)
            .Range("A1:AN112").Copy Sheet2.Range("A1")
        End With
    End If
    
    ActiveWorkbook.Close SaveChanges:=False
    End With
Application.Calculation = xlCalculationAutomatic
End Sub

Code the button is using:

Private Sub CommandButton1_Click()
Call ImportR
End Sub

 

[dmt32]

Hi,
move this line

ActiveWorkbook.Close SaveChanges:=False

to where shown here

With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Set ws = wkbSourceBook.Sheets(1)
         
        With Sheets(1)
            .Range("A1:AN112").Copy Sheet2.Range("A1")
        End With
        
        ActiveWorkbook.Close SaveChanges:=False
        
    End If

and see if resolves your issue

Dave

 

[6StringJazzer]

It is not a good practice to rely on ActiveWorkbook. Instead you should make references explicit. You are closing ActiveWorkbook at the end of your code, but if no new file is opened ActiveWorkbook is the file containing your code. I have moved that line inside the If statement and changed ActiveWorkbook to explicitly reference the file you just opened.

         Set ws = wkbSourceBook.Sheets(1)

The object ws is never used after it is set here. Not sure why this line is needed.

        With Sheets(1)
            .Range("A1:AN112").Copy Sheet2.Range("A1")
        End With

I am assuming you are copying from the workbook you just opened into the workbook containing this code.

Sub ImportR()

   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
 
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Dim ws As Worksheet
  
   Set wkbCrntWorkBook = ActiveWorkbook
  
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
     
         Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
         wkbSourceBook.Sheets(1).Range("A1:AN112").Copy ThisWorkbook.Sheet2.Range("A1")
        
         wkbSourceBook.Close SaveChanges:=False
     
      End If
         
   
   End With

   Application.Calculation = xlCalculationAutomatic

End Sub

 

[Glasgowsmile]

It is not a good practice to rely on ActiveWorkbook. Instead you should make references explicit. You are closing ActiveWorkbook at the end of your code, but if no new file is opened ActiveWorkbook is the file containing your code. I have moved that line inside the If statement and changed ActiveWorkbook to explicitly reference the file you just opened.

         Set ws = wkbSourceBook.Sheets(1)

The object ws is never used after it is set here. Not sure why this line is needed.

        With Sheets(1)
            .Range("A1:AN112").Copy Sheet2.Range("A1")
        End With

I am assuming you are copying from the workbook you just opened into the workbook containing this code.

Sub ImportR()

   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
 
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Dim ws As Worksheet
 
   Set wkbCrntWorkBook = ActiveWorkbook
 
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
    
         Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
         wkbSourceBook.Sheets(1).Range("A1:AN112").Copy ThisWorkbook.Sheet2.Range("A1")
       
         wkbSourceBook.Close SaveChanges:=False
    
      End If
        
  
   End With

   Application.Calculation = xlCalculationAutomatic

End Sub

Thanks for the tips! I copied this from another sheet I had so some of it was just leftover code I didn't delete.