Renaming a workbook before emailing

[strippy]

Hi,

the first time i run this code i get the desired result but if I try to repeat the action the code breaks at the part where it needs to rename the workbook.

Please help

The code is here:

Please Login or Register  to view this content.

Thank you in advance,

marko

[JBeaucaire]

Seems like you're implying the file can't be saved because the filename already exists on your hard drive? So, delete it if necessary:

Please Login or Register  to view this content.

[strippy]

Hi JBeaucaire,

thank you for your quick reply. Unfortunately the script still doesn't work.

It breaks at the same point:

Please Login or Register  to view this content.

I'm working in Excel 2003 and Windows XP.

Thank you in advance,

br,
marko

[JBeaucaire]

The script works for up to and past that point. I also tried this and it worked:

Please Login or Register  to view this content.

What is the current value of ddate at the moment of failure? I see no reason other than a bad string with illegal characters that it should fail.

What is the error message?

[strippy]

Hi,

i have created a new workbook with a simple table. The first time i run the code everything works fine. The second time around the code breaks at the SaveAs part of the code. It does not matter i'm trying to put the date into the file name or if i name it "Test.xls". the code breaks every single time.

The error says: Run-time error "1004": Application-defined or object-defined error.

It doesn't make any sense as i have used this method to save files before.

Thank you for your help,

Br,
Marko

[JBeaucaire]

I'd happily look at your file. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[strippy]

hi,

will prepare a sample of the actual workbook i'm working on with some data and attach here on monday. have to finish some other stuff that's very urgent so don't have time to do the rest.

Thank you for your help,

br,
Marko

[strippy]

Hi,

sorry i just did not have time last week to post an example of the excel sheet i'm working on.

The principle is the same for the rest of the workbook (other 4 sheets), just with a few more columns that are formatted based on a set of certain values.

The space above the table is reserved for two command buttons.

I'm still getting an error the second time i try to run the sub send_the_data. I'm usually filtering the Region column.

Thank you in advance.

Best regards,
Marko

[JBeaucaire]

Try these changes to your macros... I redid your selectionchange macro to a standard "ws_change" macro so it only triggers when you make a change to a the sheet, not every time you move.

Please Login or Register  to view this content.

[strippy]

Hi,

it seems that the procedure does not delete the file in My documents folder after the email is sent.

Also i have tried it on three different computers in the office with the same excel 2003 build and the procedure breaks each time. It might be a bug in excel.

Thank you for your help.

br,
marko

[JBeaucaire]

No, there is no line of code in there to delete anything after the email is sent. You would need to add that:

Please Login or Register  to view this content.

Breaks where?

[strippy]

Hi,

thank you for all the help. Figured out the reason why i had problems with the code. I did not have sufficient rights on temp folder within Common files in Program files. We have a lot of restrictions regarding user rights.

So thank you for all the help. Your code works perfectly and also deletes the file in My documents folder.

Thank you once again.

br,
Marko