OnOpen event

Hi All

I regularly send out .xls files to clients, and (for reasons you might guess, but which aren't relevant here) I need to have a button on Sheet1 of the excel file that, when clicked by the client, will save a copy of the current workbook to the same directory as the current workbook, but with the file extension amended from .xls to .abc . I would like the button to have the caption "Save this file as C:/My Documents/admin/June13/summary.abc" - but using the actual path and filename, which will vary.

I'm pretty familiar with VBA coding in Access, and I've been googling how to do this, and I think I'm nearly there.

So far I have:
In the ThisWorkbook module

Private Sub Workbook_Open()
Dim NewFileName As String
NewFileName = FindAndReplace(Application.ActiveWorkbook.FullName, "xls", "abc")
Sheet1.Shapes("Button 1").TextFrame.Characters.Text = "Save this file as " & NewFileName
End Sub

and in Module1:

Sub Button1_Click()
Dim NewFileName As String

ActiveWorkbook.Save
NewFileName = FindAndReplace(Application.ActiveWorkbook.FullName, "xls", "abcd")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=NewFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.Quit
End Sub

(I've not shown the FindAndReplace function, which is also in Module1, and works fine)

This all seems to work, but I don't understand why the Module1 code has to be in Module1 and not in Sheet1: it didn't work when I tried it there
I also don't really get how the particular button (which will always be on Sheet1) is referenced. This needs to be right before I go any further, and I'm concerned that I'm not going about it the right way (if I am - great .

In case it's relevant, the workbook is in fact created from an Access module (using a template), with Access VBA being used to populate some data and to create lots of dropdowns, validation rules, formatting, etc.

Hope someone can help.
Thanks in advance
Les

Hi tehneXus
Thanks for your reply. I obviously need to do some more homework on the referencing and assigning a sub, etc., and your input is helpful
Les

I must admit, I'm intrigued what FindAndReplace() does and how it differs from Replace()

Hi Holger
Good idea - reckon I'll do that - thanks
Les

Hi Kyle123
I knew someone would ask

Public Function FindAndReplace(ByVal strInString As String, _
        strFindString As String, _
        strReplaceString As String) As String
Dim intPtr As Integer
    If Len(strFindString) > 0 And Len(strInString) > 0 Then  'catch if try to find empty string
        Do
            intPtr = InStr(strInString, strFindString)
            If intPtr > 0 Then
                FindAndReplace = FindAndReplace & Left(strInString, intPtr - 1) & _
                                        strReplaceString
                    strInString = Mid(strInString, intPtr + Len(strFindString))
            End If
        Loop While intPtr > 0
    Else
        FindAndReplace = strInString
    End If
    FindAndReplace = FindAndReplace & strInString
End Function

I can't remember why I wrote this (it was in access, years ago) - can you see if it is any different from excel's Replace()?

The replace function exists in Access IIRC, I suspect it does exactly what yours does, although I suspect somewhat more efficiently