Activate unknown WB - VBA

Hi,

Im trying to switch between two workbooks, one will always be named the same

(treats file for merit.xls)

The other wb will change with the date, can i activate the next workboko as opposed to actually naming it.

Thanks
Semaus
Columns("F:F").Select
    Selection.Cut
    Windows("Treats file for Merit.xls").Activate
    Columns("A:A").Select
    ActiveSheet.Paste
    Windows("Coupons 610 - 08 April 2011.xls").Activate
    Columns("H:H").Select
    Selection.Cut
    Windows("Treats file for Merit.xls").Activate
    Columns("B:B").Select
    ActiveSheet.Paste
    Windows("Coupons 610 - 08 April 2011.xls").Activate
    Columns("G:G").Select
    Selection.Cut
    Windows("Treats file for Merit.xls").Activate
    Columns("C:C").Select
    ActiveSheet.Paste
    Windows("Coupons 610 - 08 April 2011.xls").Activate
    Columns("A:A").Select
    Selection.Cut
    Windows("Treats file for Merit.xls").Activate
    Columns("D:D").Select
    ActiveSheet.Paste
    Windows("Coupons 610 - 08 April 2011.xls").Activate
    Columns("B:B").Select
    Selection.Cut
    Windows("Treats file for Merit.xls").Activate
    Columns("E:E").Select
    ActiveSheet.Paste
    Windows("Coupons 610 - 08 April 2011.xls").Activate
    Columns("C:C").Select
    Selection.Cut
    Windows("Treats file for Merit.xls").Activate
    Columns("F:F").Select
    ActiveSheet.Paste
    Windows("Coupons 610 - 08 April 2011.xls").Activate
    Columns("I:I").Select
    Selection.Cut
    Windows("Treats file for Merit.xls").Activate
    Columns("G:G").Select
    ActiveSheet.Paste
    Windows("Coupons 610 - 08 April 2011.xls").Activate
    ActiveWindow.ScrollColumn = 12
    Columns("S:S").Select
    Selection.Cut
    Windows("Treats file for Merit.xls").Activate
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.LargeScroll ToRight:=-1
    Columns("H:H").Select
    ActiveSheet.Paste
    Columns("H:H").EntireColumn.AutoFit
    Windows("Coupons 610 - 08 April 2011.xls").Activate
    ActiveWindow.LargeScroll ToRight:=1
    Columns("P:P").Select
    Selection.Cut
    Windows("Treats file for Merit.xls").Activate
    ActiveWindow.LargeScroll ToRight:=-1
    Columns("I:I").Select
    ActiveSheet.Paste
    
        Windows("Coupons 610 - 08 April 2011.xls").Activate
    Columns("L:L").Select
    Selection.Cut
    Windows("Treats file for Merit.xls").Activate
    Columns("J:J").Select
    ActiveSheet.Paste
    
    
    ActiveWindow.LargeScroll ToRight:=-1
    Columns("A:B").Select
    Selection.NumberFormat = "General"
    Columns("C:E").Select
    Selection.NumberFormat = "General"
    Columns("H:H").Select
    Selection.NumberFormat = "0.00"
    Columns("I:I").Select
    Selection.NumberFormat = "0"
    Columns("J:J").Select
    Selection.NumberFormat = "0.00"
    ActiveWindow.SmallScroll ToRight:=1
    Range("H5").Select
    ActiveWindow.ScrollColumn = 1
    Rows("1:4").Select
    Range("A4").Activate
    Selection.Delete Shift:=xlUp
    Range("A1:K1").Select
    Selection.Interior.ColorIndex = 6
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A2").Select

Open in new window

Seamus2626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenJRCommented:
Seamus - if you are opening the other workbook in code you could assign a variable to it, e.g.

Set wb1=Workbook.Open(".....xls")

and then reference it that way.
0
Seamus2626Author Commented:
Hey Stephen, i have attached the code that i use to open the latest spreadsheet in a folder, maybe at that point of the code i could set a name for the workbook?

Im not sure how i would do it though?

Thanks
Seamus
Sub OpenLatestFile()
   Dim initPath As String, Direc As String, strFile As String, strFinalFile As String
   Dim Dt As Date, dteFile As Date
   Dim objFSO, objFdr, objSubFdr
   
   
   ' Change Path of parent directory here
   ' Don't forget the "\" after the path
   

   
   initPath = "\\ukhibmdata02\rights\Eurobond\COUPONS\Coupon Tickets\Coupons 2011\"
   
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFdr = objFSO.getfolder(initPath)
   
   ' loop through folders, checking names
   For Each objSubFdr In objFdr.subfolders
      If IsDate(objSubFdr.Name) Then
          If Dt = #12:00:00 AM# Then
            Dt = CDate(objSubFdr.Name)
            Direc = objSubFdr.Name
         Else
            If CDate(objSubFdr.Name) > Dt Then
               Dt = CDate(objSubFdr.Name)
               Direc = objSubFdr.Name
            End If
         End If
      End If
   Next objSubFdr
   ' check we found a date
   If Len(Trim(Direc)) <> 0 Then
      ' now need to loop through the files and find the last one
      ' assumes file names are like "09022011 Merit.xls"
      ' so we want the first 8 characters converted to a date
      strFile = Dir(initPath & Direc & "\*.xls")
      If strFile <> "" Then
         Do
            If dteFile < GetDateFromFileName(strFile) Then
               strFinalFile = strFile
               strFile = Dir
            End If
         Loop While strFile <> ""

         If Len(strFinalFile) > 0 Then
           Workbooks.Open initPath & Direc & "\" & strFinalFile
           Sheets("Sheet1").Visible = True
           Sheets("Sheet1").Select
         End If
         
         
      Else
         MsgBox "No workbooks in " & initPath & Direc & "\"
      End If
   Else
      MsgBox "No date files found"
   End If
   
   With ActiveWindow
   
  
   
   End With
   
   Windows("Treats file for Merit.xls").Activate
   
   
  Call RunReport

End Sub


Function GetDateFromFileName(strFile As String) As Date
   ' returns date from last 8 characters of file name
   ' assumes dd mmmm yyy format
   Dim dteTemp As Date, strTemp As String
   strTemp = Replace$(strFile, ".xls", "", , , vbTextCompare)
   strTemp = Right(strTemp, Len(strTemp) - InStr(strTemp, " - ") - 2)
   If Len(strTemp) < 11 Then Exit Function
   If IsDate(strTemp) Then GetDateFromFileName = CDate(strTemp)
End Function

Open in new window

0
StephenJRCommented:
Seaumus - maybe change the section below, lines 46-48 perhaps? Not sure where your first bit of code fits in to the second.
Dim wb as Workbook 'at the top
If Len(strFinalFile) > 0 Then
           Set wb = Workbooks.Open(initPath & Direc & "\" & strFinalFile)
           wb.Sheets("Sheet1").Visible = True
           wb.Sheets("Sheet1").Select
         End If

Open in new window

0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Seamus2626Author Commented:
In lines 59-70 i have the attached code.

At that stage the file is open, is it possible to rename the file then to something solid like xyz.xls?

That would solve the problem if i could?

Thanks
Seamus


With ActiveWindow
   
  
   
   End With
   
   Windows("Treats file for Merit.xls").Activate
   
   
  Call RunReport

End Sub

Open in new window

0
StephenJRCommented:
Renaming the file is different. I think you would have to save it under a different name.
0
Seamus2626Author Commented:
But could i set the name of it at that point, so where the code says

With ActiveWindow

Could i say

Set Active WB = "XYZ"

Thanks
Seamus
0
StephenJRCommented:
Seamus - no you cannot rename a file like that. I think you can only do it via save as. You started off asking "can i activate the next workbook as opposed to actually naming it" so we have shifted ground, and I'm not sure you need to rename this file (at least not just rename it).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seamus2626Author Commented:
Theres not going to be any other worksbooks open in this exercise, so can i simply say

Activate.workbook.next

Something along those lines?

Thanks
Seamus
0
Seamus2626Author Commented:
I used  ActiveWindow.ActivateNext
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.