Saving files

[Kathy]

I want to create a file so that when the user saves it
(with in a macro) it sequentally names the file

first save File1.xls, then file2.xls, file3.xls

The #'s 1, 2,3 are part of the worksheet, but I do not
know who to get that # to be part of the file name with
asking the user to do a file saveas file#

please advise
thanks

 

[speidlbacsi]

try this; you may define variables more precisely

Sub SavContinous()

Y = "C:\temp\FileName" ' can be whichever existing folder
For Nr = 1 To 9999

Fname = Y & Nr & ".xls"

Set fs = CreateObject("Scripting.FileSystemObject")
A = fs.FileExists(Fname)

If A = False Then GoTo rutin

Next Nr

rutin:

ActiveWorkbook.SaveAs Filename:=Fname

End Sub

 

[Nigel]

Kathy,
You can build the file name up from text strings.

In the following snippet, the string "C:\Book1.xls" can be built however you
like.

ActiveWorkbook.SaveAs Filename:="C:\Book1.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Here the file name string is replaced a string variable

ActiveWorkbook.SaveAs Filename:=vFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

So all you need to do is define vFileName eg

Dim vFileName as String
vFilename = "C:\" & "Book" & "1" & ".xls"

Any of all parts of the above can be changed programmatically

So if you have a cell with a number in it, say A1 on Sheet1, then

vFileName = "C:\File" & Worksheets("Sheet1").Range("A1").Value & ".xls"

You need to be careful in case the value in cell A1 contains leading or
trailing spaces and you do not want these in the filename.
So use a trim function to remove them. eg

vFileName = "C:\File" & Trim(Worksheets("Sheet1").Range("A1").Value) &
".xls"

HTP
Cheers
Nigel

 

[Kathy Goldie]

I used the idea from Nigel

ActiveWorkbook.SaveAs FileName:=vFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False

and it worked GREAT -
I think it will help me alot in other worksheets as well - but now it
has brought on another issue - I worked through what I thought would
work, but it doesn't
the code actually looks like this (this is a macro I used after saving
the file with vFileName)

Workbooks.Open FileName:="C:\Temp\misc_log.xls"
Windows("Misc Shipper_43.xls").Activate
Application.Goto Reference:="R1C3"
Selection.Copy
Windows("misc_log.xls").Activate
Rows("5:5").Select
Application.CutCopyMode = False

Where the
Windows("Misc Shipper_43.xls").Activate
line is in the macro - i tried to use
Windows vFileName.Activate
vFileName in place of ("Misc Shipper_43.xls")
and I get an error Invalid qualifer
I am still looking on how to fix this - but if you have an answer I
would appricate it

Thanks again

 

[Nigel]

Kathy,
The window should be referred to by its workbook name, so substitute

Windows("Misc Shipper_43.xls").Activate

with

Windows("Misc Shipper_43").Activate

or if VFileName contains Misc Shipper_43 then

Windows(vFileName).Activate

Cheers
Nigel