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?
 
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
 
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.