if sheet not found go to next workbook

[FROGGER24]

I am using this piece of code that I need to modify that if worksheet "receipts" is not found then go to the next closed workbook...

Sub GetMyData()
Dim myDir As String, fn As String, sn As String, NR As Long
myDir = "c:\test1"
sn = "receipts"
fn = Dir(myDir & "\*.xls")
NR = 1
Do While fn <> ""
If fn <> ThisWorkbook.Name Then
With ThisWorkbook.Sheets("Sheet1")
NR = NR + 1

Thanks for any help!

 

[bertie]

You need to open the workbook.

      [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(fn)

Loop through each worksheet and test it's name.
If name="receipts" then do something.

      [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] wb.Worksheets
        [COLOR=darkblue]If[/COLOR] ws.Name = sn [COLOR=darkblue]Then[/COLOR]
          [COLOR=green]'===============[/COLOR]
          [COLOR=green]'do something[/COLOR]
          '===============
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]Next[/COLOR] ws

Loop to the next workbook.

      [COLOR=green]'get the next file[/COLOR]
      fn = Dir()
  [COLOR=darkblue]Loop[/COLOR]

I have highlighted where this all goes in your code. I have used two new variables, ws=worksheet, wb=workbook.

[COLOR=darkblue]Sub[/COLOR] GetMyData()
  [COLOR=darkblue]Dim[/COLOR] myDir [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], fn [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], sn [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], NR [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] [COLOR=Red]ws[/COLOR] [COLOR=darkblue]As[/COLOR] Worksheet
  [COLOR=darkblue]Dim[/COLOR] [COLOR=Red]wb [/COLOR][COLOR=darkblue]As[/COLOR] Workbook

  myDir = "c:\test1"
  sn = "receipts"
  NR = 1
  fn = Dir(myDir & "\*.xls")
  
  [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] fn <> ""
    [COLOR=darkblue]If[/COLOR] fn <> ThisWorkbook.Name [COLOR=darkblue]Then[/COLOR]
      
      [COLOR=Red]Set wb = Workbooks.Open(fn)[/COLOR]
      
      [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] [COLOR=Red]ws [/COLOR][COLOR=Red]In wb.Worksheets[/COLOR]
        [COLOR=darkblue]If[/COLOR] [COLOR=Red]ws.Name = sn[/COLOR] [COLOR=darkblue]Then[/COLOR]
          [COLOR=green]'===============[/COLOR]
          [COLOR=green]'do something[/COLOR]
          '===============
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]Next[/COLOR] ws
      
      [COLOR=SeaGreen]'Your code[/COLOR]
      [COLOR=darkblue]With[/COLOR] ThisWorkbook.Sheets("Sheet1")
          NR = NR + 1
          [COLOR=green]'[/COLOR]
          '
          '
          
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
      
      [COLOR=green]'get the next file[/COLOR]
    [COLOR=Red]  fn = Dir()[/COLOR]
  [COLOR=darkblue]Loop[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]