Saving an unactivated worksheet

[Hari]

Hi,

I have a workbook by the name -- " ABC.xls"

It could be possible that it is not activated.

I want to save this workbook by appending todays' date to the present name.

I wrote the code ( Pinched it from NG) :-

Windows("ABC.xls").Activate
If InStr(ActiveWorkbook.Name, CStr(Format(Now, "dd-mmm-yy"))) = 0 Then
ActiveWorkbook.SaveAs filename:= _
"C:\Documents and Settings\hprasadh\Desktop\xyz\Project comparison\ABC
" & " " & CStr(Format(Now, "dd-mmm-yy")), FileFormat:=xlNormal
End If

The above is part of a bigger code and my code gets a compile error at the
point Windows("ABC.xls").Activate . I tried changing it to

Windows("ABC").Activate but to no avail.

Curiously at other points of the code also when I try to activate this sheet
using this line ( in order to perform some cut paste operation) it bombs at
the same point.

1. Please tell me why this is happening.

2. Is there a way I can save the "ABC.xls" as a new workbook with the
filename "C:\Documents and Settings\hprasadh\Desktop\xyz\Project
comparison\ABC " & " " & CStr(Format(Now, "dd-mmm-yy")),
FileFormat:=xlNormal. Why Im asking this is presently while saving
ActiveWorkbook.SaveAs filename statement has to be used and if there is a
way one could save a POSSIBLY unactivated sheet without first activating it
my problem may be solved. ( Still please do tell me at the firs place why is
the activate code not working)

{ The file does have the name "ABC" only. I copied the filename from
rightclick properties to doublecheck }

Regards,
Hari
India

 

[Hari]

Hi,

I missed to add this.

The error I get is "Run time error 9 : Subscript out of range"

Regards,
Hari
India

 

[Dave Peterson]

That subscript out of range error means that the window named "abc.xls" doesn't
exist.

A couple of guesses:

Your first comment:

I have a workbook by the name -- " ABC.xls"

Had a leading space. (typo in the post or in your code??)

If the workbook had multiple windows open (window|new window) and you'd see:
abc.xls:1
or
abc.xls:2

Then your windows("abc.xls").activate will fail.

========
Since you know the name of the workbook, it doesn't look like you really need to
inspect it for it's name.

Maybe:

workbooks("abc.xls").saveas filename:= _
"C:\Documents and Settings\hprasadh\Desktop\xyz\Project comparison\ABC" _
& " " & CStr(Format(Now, "dd-mmm-yy")), FileFormat:=xlNormal


or more generically:

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim myPattern As String

Dim myNewName As String
Dim myFolder As String

'Set wkbk = Workbooks("abc.xls") 'or anything you want!
Set wkbk = ActiveWorkbook

myPattern = "##-???-##.xls"


myFolder = "C:\Documents and Settings\hprasadh\Desktop\" & _
"xyz\Project comparison\"

'myFolder = "c:\my documents\excel\test\"


With wkbk
If LCase(Right(.Name, Len(myPattern))) Like myPattern Then
myNewName = Left(.Name, Len(.Name) - Len(myPattern)) _
& Format(Date, "dd-mmm-yy")
Else
If LCase(Right(.Name, 4)) = ".xls" Then
myNewName = Left(.Name, Len(.Name) - 4) _
& Format(Date, "dd-mmm-yy")
Else
myNewName = .Name & Format(Date, "dd-mmm-yy")
End If
End If

Application.DisplayAlerts = False
.SaveAs Filename:=myFolder & myNewName, _
FileFormat:=xlWorkbook
Application.DisplayAlerts = True
End With

End Sub

 

[Hari]

Hi Dave,

Thanx a lot for the method of referencing the workbook name directly (
"Since you know the name of the workbook ..")

Im able to do what I wanted with ur method.

Yes , typo in name of the file was in my post.

Also, I had only one window of "ABC" open at that time.

Still Im not able to understand why I get compile errors when I use
windows("ABC.xls").activate

Thanx again

Regards,
Hari
India

 

[Dave Peterson]

You didn't have multiple windows into that ABC workbook open?

Maybe this'll help you see what's wrong:

Option Explicit
Sub testme01()
Dim myWindow As Window

For Each myWindow In Windows
MsgBox "|" & myWindow.Caption & "|"
Next myWindow

End Sub