asked on

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

StephenJR

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.

Seamus2626

ASKER

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

StephenJR

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

Seamus2626

ASKER

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

StephenJR

Renaming the file is different. I think you would have to save it under a different name.

Seamus2626

ASKER

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

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

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

Seamus2626

ASKER

I used ActiveWindow.ActivateNext