Excel Macro to Save Consecutive Files

Microsoft Excel 2003 (full product)
September 25, 2009 at 06:46:35
Specs: Windows XP
I am very new to this and was wondering if anyone knew how to do the following:
In my excel Sheet1 I have a button that when clicked I want it to save on my server. In addition I want the macro to look at the files on this server and save it one number from the previous file. For example I have 2009-001 on the server and when I click the Save button on Sheet1 it will look at the server and save it as 2009-002. Is this possible?? I would greatly appreciate your help!


See More: Excel Macro to Save Consecutive Files

Report •

September 25, 2009 at 08:18:16

Here is code that will save to the next file name

It has no real error checking, so there is room for improvement. Also I have only tested this on my own PC and not on a server.

In the visual basic editor (Alt + f11) or Tools - Macro - Visual Basic Editor (in Excel 2003) find VBAProject(PERSONAL.XLS)
Right click and Insert - Module (not Class Module)
Give it a name (change the name in the Properties window), or leave it at its default (Module 1 if this is the first one you have added)
Paste the following code into the module

Option Explicit

Sub SaveNext()
'saves the active workbook with the next sequential number

Dim strDirectory As String
Dim strBaseName As String
Dim strLastNumber As String
Dim intLastNumber As Integer
Dim strLastFile As String
Dim strNewFile As String

'set the base directory and name for your files
strDirectory = "C:\Increment\"
strBaseName = Year(Now()) & "-"

'get last matching filename
With Application.FileSearch
    .LookIn = strDirectory
    .FileType = msoFileTypeExcelWorkbooks
    .Filename = strBaseName & "*.xls"
    If .Execute = 0 Then
        'no matching file so start with -000
        strLastFile = strDirectory & strBaseName & "000.xls"
        'matching file found - get the last one
        strLastFile = .FoundFiles(.FoundFiles.Count)
    End If
End With

'create the new file name
strLastNumber = Mid(strLastFile, Len(strLastFile) - 6, 3)
intLastNumber = CInt(strLastNumber)
strNewFile = strDirectory & strBaseName & Format(intLastNumber + 1, "000") & ".xls"

'save this workbook with this filename
ActiveWorkbook.SaveAs (strNewFile)
End Sub

Now hook this up to a button on a toolbar.
In Excel, right click on a free space in the toolbar, and select Customize

In the Commands tab select Macros on the left
From the right pane drag the Custom button 'Smiley' onto the toolbar.

Right click the new icon and select 'Assign Macro'
From the box select PERSONAL.XLS!SaveNext and click OK

(You can customize your new icon at this stage - either edit button image to make your own or select button image to select from a few ready made images. Give it some tip text with Name (don't worry about the '&' at this stage - just call it 'Save next')
Right click and select Text only in Menus)

Now you need to set the base directory - I have it hard-coded.

Go to the code in your new module and change the line
strDirectory = "C:\Increment\"
to the directory you are using - don't forget the closing "\"

The base name is set to the current year e.g., "2009-"
strBaseName = Year(Now()) & "-"

Save the new code from the Visual basic File menu

And you should be good to go.

Create a new workbook, click on the new icon and you should see the new filename come up along the top of the workbook. Check the folder for the new file.

When a new year starts it should start with a new filename and -001 e.g., 2010-001.xls.

PS I haven't looked at directory write permissions for a server, so this could be an issue

Report •
Related Solutions

Ask Question