Error catching/ handling if file not open

[Rip1971]

Hi,

I would like to put a error handling code taht does the following. in my code i have this line. this works well if the srcWS is already open. But when i do not have the file open i would like a msg telling me to open the file. or even better a (don't know how it is called) but a explore window so i can locate and open the file which is then used as srcWS.

Since the code is started from the active workbook is the order in which this is done important??

Sub CopyDepartement(control As IRibbonControl)
   Application.ScreenUpdating = False
   Dim LastRow As Long, r As Long
   Dim srcWS As Worksheet, desWS As Worksheet
   Dim Ary As Variant, Nary As Variant
   Set srcWS = Workbooks("Bewonerslijst.xlsx").Sheets("Sheet1")
   Set desWS = ActiveWorkbook.Sheets("Data")

thanks in advance

 

[Fluff]

How about

   Dim Wbk As Workbook
   
   On Error Resume Next
   Set Wbk = Workbooks("Bewonerslijst.xlsx")
   On Error GoTo 0
   If Wbk Is Nothing Then Set Wbk = Workbooks.Open("yourfilepath\Bewonerslijst.xlsx")
   Set srcWS = Wbk.Sheets("Sheet1")

change yourfilepath to match the location of the file.

 

[Rip1971]

This works good. only had to change which workbook is set first desWS and srcWS because of active workbook is no longer the workbook that started the code.
However is still requires me to know the location upfront. How can I make i so that even if i do not know location I can open explorer select file and then use.

Set desWS = ActiveWorkbook.Sheets("Data")
   
   On Error Resume Next
   Set Wbk = Workbooks("Bewonerslijst.xlsx")
   On Error GoTo 0
   If Wbk Is Nothing Then Set Wbk = Workbooks.Open("C:\Users\u123456\OneDrive\Desktop\Bewonerslijst.xlsx")
   Set srcWS = Wbk.Sheets("Sheet1")

 

[Fluff]

How about

   Dim Fname As String
   
   On Error Resume Next
   Set Wbk = Workbooks("Bewonerslijst.xlsx")
   On Error GoTo 0
   If Wbk Is Nothing Then
      Fname = Application.GetOpenFilename
      Set Wbk = Workbooks.Open(Fname)
   End If

 

[Rip1971]

How about

   Dim Fname As String
 
   On Error Resume Next
   Set Wbk = Workbooks("Bewonerslijst.xlsx")
   On Error GoTo 0
   If Wbk Is Nothing Then
      Fname = Application.GetOpenFilename
      Set Wbk = Workbooks.Open(Fname)
   End If

This is what i was looking for for opening the file thank you. how do i know that the right sheet is selected within that file. What i have now is adding the line below which tells the sheet name.
Set srcWS = Wbk.Sheets("Sheet1")

But as you can imaging that ppl change this without knowing the impact of this change.
slowly working on making it an user friendly piece of code.

 

[Fluff]

You could use the sheets codename, but you would need to change the codename to something non-standard.

 

[Rip1971]

This is what i was looking for for opening the file thank you. how do i know that the right sheet is selected within that file. What i have now is adding the line below which tells the sheet name.
Set srcWS = Wbk.Sheets("Sheet1")

But as you can imaging that ppl change this without knowing the impact of this change.
slowly working on making it an user friendly piece of code.

Is there also a line that prevents error when someone presses the cancel button instead of the oke button

 

[Fluff]

How about

   If Wbk Is Nothing Then
      Fname = Application.GetOpenFilename
      If Fname = "False" Then Exit Sub
      Set Wbk = Workbooks.Open(Fname)
   End If

 

[Rip1971]

Thanks for al the pieces of code. together they do what i want.

 

[Fluff]

You're welcome & thanks for the feedback.