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

Please Login or Register  to view this content.

and in Module1:

Please Login or Register  to view this content.

(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

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

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

Hi Kyle123
I knew someone would ask

Please Login or Register  to view this content.

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()?

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

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