I have a query which needs to be sent to somebody each week as a spreadsheet, which I have set up using the docmd.sendobject command. However, they require the current date to be part of the filename each time it runs. As far as I can tell, the spreadsheet is always named the same as the query name, so I would like, if possible, to rename the query each time the function runs before being emailed out, so that this will be reflected in the filename. Is this possible?

Thanks,

Ben

Hi,

Try this

Dim sOutput As String
Dim strlocal As String
Dim strData As String

(...)

strlocal = "C:\files_to_send"
strData = Format(Now(), "dd-mm-yyyy hhmmss")

(...)

sOutput = strlocal & "\" & strData & "Your_EXCEL_FILE_NAME.xls"

Cheers

Hi, thanks for your reply. I'm not sure that this does what I need though to be honest - what I'm really looking for is the VBA code required to rename a query within a database.

or... this example... zips the file

Function SendMessage()
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
Dim strAppName As String
Dim MyAtt1
Dim MyAtt2

Set Fs = CreateObject("Scripting.FileSystemObject")

strlocal = "C:\"

DoCmd.TransferSpreadsheet acExport, 8, "BD SGCF Actual", strlocal & "BD_Warrants.xls" ' Export the query to your C:\

strAppName = "C:\WINRAR\WinRAR.exe a "
strAppName = strAppName & "C:\ZipBD.zip "
strAppName = strAppName & "C:\BD_Warrants.xls"

Call Shell(strAppName, 1)

strSubject = "Listagem de warrants"
strEMailMsg = "Junto enviamos a listagem de warrants actualizada." & Chr(10) & Chr(10) _
& "Ao dispor," & Chr(10) & Chr(10) _
& "6666666" & Chr(10) _
& "9999999999ISDA SUPT UNIT" & Chr(10) _
& " Piso 2 b" & Chr(10) _
& "Porto Salvo" & Chr(10) _
& "Portugal" & Chr(10) _
& " " & Chr(10) _
& " " & Chr(10) _
& " " & Chr(10) _
& " "


MyAtt2 = "C:\ZipBD.zip"

Const olMailItem As Long = 0
Const olFormatPlain As Long = 1

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.To = "x071833;"
.CC = "X999999; X888888;"
.Subject = strSubject
.Importance = 2
'.BodyFormat = olFormatPlain
.Body = strEMailMsg
.Attachments.Add (MyAtt2)
.Display
End With

DoCmd.SetWarnings True

End Function

Not to disagree with ICE929RR , but, to just answer your question...

I am using Access 2007 and it has...

docmd.rename "new query name", acquery, "old query name"

Ben,

Here's one strategy: Use your existing query as a template. Copy it, using the current date to come up with the new name. Then, send it, and finally delete the temporary copy.



Hope this helps,

Dennis

Hi,

to rename a Query through VBA, try this (I haven't done it, but it would be my first guess):

Ben,

As you can see... there is a lot of help here...

Cya...

PS: Still stuck in Access 2000 LOL

Hi, many thanks for all of your replies! It's all working ok now, I just used the following, as suggested by Bob G:

docmd.rename "new query name", acquery, "old query name"

This worked perfectly. Thanks for your help.