Add date to filename

[davea300]

Hi

I have the following line of code that attaches a file to an email and allows you to send it.

DoCmd.SendObject acReport, "Activity2Weeks", "MicrosoftExcelBiff8(*.xls)", "", "emailaddresshere", "", "email subject", "email body text", True, ""

Problem is I want to append todays date onto the end of the filename, in this case "Activity2Weeks 13/08/2008". I forget the code used to do this, something like "& Format ("ddmmyyy") & " or similar I think?

Anyone help? Sure this is an easy fix for someone here.

 

[MrQ]

Hi Dave,
You could use ,"Activity2Weeks" & " " & Date(),

Think that should work.

Cheers

 

[JohnLee]

Hi,
You were nearly there. see below

& Format(Date, "ddmmyy") &

John

 

[MrQ]

Meant to have a on the end.

Anyway, hope the answer helps

Cheers

Q

 

[davea300]

Thanks to both however when I try to add something like that to the filename e.g.

DoCmd.SendObject acReport, "Activity2Weeks" & " " & Date, "MicrosoftExcelBiff8(*.xls)", etc etc

I get an error saying that access can't find the report "Activity2Weeks 13/8/2008"

I understand that it is looking for the report "Activity2Weeks" to convert to excel so any name change in that part of the code is changing the filename before it has converted it to excel.

Will I have to append the date after it has done the excel conversion and before it attaches the file to an email?

 

[JohnLee]

Hi,

Correct me if I'm wrong, but your trying to send an access report as an excel file via email.

I'm not sure, but I didn't think you could do that.

However, this is purely guess work, as I've never done this before, but try the following

DoCmd.SendObject acReport, "Activity2Weeks" & Format(Date, "ddmmyy") & ".xls"

I'm guessing this may not work, because the report is in fact not a true excel document.

Sorry I couldn't be of more assistance

John

 

[davea300]

Correct me if I'm wrong, but your trying to send an access report as an excel file via email.

I'm not sure, but I didn't think you could do that.

Yup you can do it. You have to be careful of the formatting of the report as excel tends to lose a lot of it but it can be done. It's relatively easy to do but I can't seem to get the resulting filename to add a date onto the end.

DoCmd.SendObject acReport, "Activity2Weeks" & Format(Date, "ddmmyy") & ".xls"

Unfortunately this won't work as DoCmd.SendObject looks for an access report with the name you specify so "Activity2Weeks" has to be exactly the same as the report name.

I think I have to append the date after this part of code but I don't know where to put it.

 

[tokoloshi]

Try DoCmd.SendObject acReport, "Activity2Weeks" & trim(cstr(Format(Date, "ddmmyy") )) & ".xls"

 

[dkinley]

I already had my directory structure set and used a variable at the end of my call because my export procedure is stored in a module and I call it from various places.

sOutputName = "\" & Format(Now(), "dd-mmm-yy") & " " & Forms!FormName!txtControlName & " ObjectName.xls"

So the final file name could be rendered as:
10-Mar-2008 Big Boy Stores Inventory.xls
15-Mar-2008 Rock Haven Orders.xls

-dK

 

[tokoloshi]

Oops, I'm an idiot. Just looked at this properly now and realised that the format had included "/" in the name - a definite no-no in file naming conventions. The format chosen that used the hyphen is a better option.

The issue I had in mind when replying earlier on was that the date value may not have been string when concatenating it to the filename. :]

 

[davea300]

Thanks everyone for the suggestions. Got it working using this:

stDocName = "Activity2Weeks"

DoCmd.SendObject acReport, stDocName & " " & Date, "MicrosoftExcelBiff8(*.xls)", "",

For some reason it doesn't work appending the date if I use the filename within the SendObject line but using stdocname seems to have solved it.