Select Workbook/sheet with variable name

[Marty the Hat]

I need to write some VBA that will run some instructions in a workbook and worksheet that has a different name each month.

For example the first month I want it to select workbook named 'Marchadmits' and then in the worksheet 'admins01' add a formula to column A, the next month I want the same VBA to select workbook 'Apriladmits' and add the same formula to the worksheet 'admins02'.

Basically the VBA needs to select any workbook with '...admits' in the title and any worksheet with 'admins...' in the title regardless of the rest of the name.

I'm hoping there is a simple way of doing this but I can't figure it out at the moment, grateful for advice...

 

[BobBridges]

Sure, this doesn't look too hard. But I see something missing in the defintion: Are you going to see to it that there is only one one <month>admits workbook in the folder at a time? Because if the last few months' worth of workbooks are going to be there, then your program shouldn't select just any one of them; it would need to know which one. Maybe pick out the one it wants by looking at today's date?

But for now let's assume there's only one (and you will keep manually moving workbooks around to be sure it stays that way. More work that way, but one thing at a time). I would do it this way:

Const fnp = "C:\Folder\More path\" 'your path
Set ofs = CreateObject("Scripting.FileSystemObject") 'the FSO lets you examine and manipulate files and folders
If Not ofs.FolderExists(fnp) then 'abend - the folder is missing
Set ofo = ofs.GetFolder(fnp)

' Look through the folder for any file with "admits" in the name, and take the first one you find.
For Each ofi in ofo.Files
  fnn = ofi.Name
  If InStr(fnn,"admits") Then

    ' Does this workbook have a worksheet named "...admins..."?
    Set owb = Workbooks.Open(fnp & fnn)
    For Each ows in owb.Worksheets
      If InStr(ows.Name, "admins") Then Exit For
      Next ows
  Next ofi
If ofi Is Nothing then 'abend - no file with "admits" was found

' Now owb and ows point to a workbook and worksheet that you can work with.

How's that? Ask if you don't understand what's supposed to be going on here.

 

[Marty the Hat]

Sure, this doesn't look too hard. But I see something missing in the defintion: Are you going to see to it that there is only one one <month>admits workbook in the folder at a time? Because if the last few months' worth of workbooks are going to be there, then your program shouldn't select just any one of them; it would need to know which one. Maybe pick out the one it wants by looking at today's date?

But for now let's assume there's only one (and you will keep manually moving workbooks around to be sure it stays that way. More work that way, but one thing at a time). I would do it this way:

Const fnp = "C:\Folder\More path\" 'your path
Set ofs = CreateObject("Scripting.FileSystemObject") 'the FSO lets you examine and manipulate files and folders
If Not ofs.FolderExists(fnp) then 'abend - the folder is missing
Set ofo = ofs.GetFolder(fnp)

' Look through the folder for any file with "admits" in the name, and take the first one you find.
For Each ofi in ofo.Files
  fnn = ofi.Name
  If InStr(fnn,"admits") Then

    ' Does this workbook have a worksheet named "...admins..."?
    Set owb = Workbooks.Open(fnp & fnn)
    For Each ows in owb.Worksheets
      If InStr(ows.Name, "admins") Then Exit For
      Next ows
  Next ofi
If ofi Is Nothing then 'abend - no file with "admits" was found

' Now owb and ows point to a workbook and worksheet that you can work with.

How's that? Ask if you don't understand what's supposed to be going on here.

Thank you so much for this I'll give it a go today, and sorry for the delayed response - posted this as I was leaving work on Friday and just got back to it today.