Activate Workbook

[Guest]

Hi,

I have a macro in workbook "AP.xls" that (1) save a backup copy then (2)
open other workbooks as follows:

Dim myDate As String
Dim myFileName As String

myDate = Format(Date, "yymmdd")
myFileName = "C:\" & myDate & " Main.xls"
ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal

Workbooks.Open Filename:="C:\1.xls"
Workbooks.Open Filename:="C:\2.xls"

THEN i want to activate the newly saved workbook again by adding the
following code, but it doesn't work. Why?

ActiveWorkbook.Activate Filename:=myFileName, FileFormat:=xlWorkbookNormal

Regards,
Dolphin

 

[Guest]

i tried to do this, but it still can't work. There is an error.

Sub test()

Dim myDate As String
Dim myFileName As String

myDate = Format(Date, "yymmdd")
myFileName = "C:\" & myDate & " Main.xls"
ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal


Workbooks.Open Filename:="C:\1.xls"


Workbooks(myFileName).Activate

End Sub

 

[Dave Peterson]

You don't include the drive and path in this line:

Workbooks(myFileName).Activate

You could use something like:

option Explicit
sub test2()

dim JustFileName as string
dim myFileName as string
dim myDate as string

mydate = format(date,"yymmdd")
justfilename = "mydate & " main.xls"
myfilename = "C:\" & justfilename

ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
Workbooks.Open Filename:="C:\1.xls"
Workbooks(justfilename).Activate

End sub
=======
Or something like:

Option Explicit
Sub test()

dim wkbk as workbook
Dim myDate As String
Dim myFileName As String

myDate = Format(Date, "yymmdd")
myFileName = "C:\" & myDate & " Main.xls"
ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
set wkbk = activeworkbook

Workbooks.Open Filename:="C:\1.xls"
wkbk.Activate

End Sub

===============
And as a note, you may want to look at .SaveCopyAs in VBA's help. You can save
a copy of the workbook as a new name--that workbook is a snapshot of the way
that workbook looks right then. And the original isn't renamed, so you don't
have to open it again.