Create new unique excel file name automatically

[ryan.fitzpatrick3]

Is there away to create a new excel name of a file when I export from
access? Like have the name of the file have a timestamp or have it get
named by one of the query field names?

Here is my code. It works by the way.


Private Sub Command84_Click()
On Error GoTo Err_Command84_Click

Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim lngOrderBy As Long
Dim strQueryName As String
Dim strSQL As String

' You only need to go to this effort if there's a filter
If Len(Me.Filter) > 0 Then
Set dbCurr = CurrentDb

' Get the SQL for the existing query
strSQL = dbCurr.QueryDefs("QryAdageVolumeSpend").SQL

' Check whether there's an ORDER BY clause in the SQL.
' If there is, we need to put the WHERE clause in front of it.
lngOrderBy = InStr(strSQL, "ORDER BY")
If lngOrderBy > 0 Then
strSQL = Left(strSQL, lngOrderBy - 1) & _
" WHERE " & Me.Filter & " " & _
Mid(strSQL, lngOrderBy)

Else
' There's no ORDER BY in the SQL.
' Remove the semi-colon from the end, then append the WHERE clause
strSQL = Left(strSQL, InStr(strSQL, ";") - 1) & _
" WHERE " & Me.Filter
End If

' By using the current date and time, hopefully that means
' a query by that name won't already exist
strQueryName = "qryTemp" & Format(Now, "yyyymmddhhnnss")

' Create the temporary query
Set qdfTemp = dbCurr.CreateQueryDef(strQueryName, strSQL)

' Export the temporary query
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"K:\Corp Sourcing\Shared\Ryan Fitzpatrick\AdageData.xls", _
hasfieldnames:=True

' Delete the temporary query
dbCurr.QueryDefs.Delete strQueryName

Else

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\rfitz03\My Documents\AdageData.xls",
_
hasfieldnames:=True

End If

Exit_Command84_Click:
Set dbCurr = Nothing
Exit Sub

Err_Command84_Click:
MsgBox Err.Description
Resume Exit_Command84_Click

End Sub



It's this line "K:\Corp Sourcing\Shared\Ryan Fitzpatrick
\AdageData.xls", _

where I would like to have it unique every time I save it. Also is
there away to have it save defaulted to the desktop? I created a
access program that multiple people use, and I would like for the data
they transfer to go straight to their desktop. Is this possible?
Thanks.

Ryan

 

[ryan.fitzpatrick3]

Thanks for reply. Is there away to have the excel file save directly
to desktop only?

 

[Douglas J. Steele]

Sure. Just replace "K:\Corp Sourcing\Shared\Ryan Fitzpatrick\" in your code
with the appropriate path.

To determine the appropriate path for each given user, see
http://www.mvps.org/access/api/api0054.htm at "The Access Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for reply. Is there away to have the excel file save directly
to desktop only?

 

[ryan.fitzpatrick3]

My issue is that my database is used by many people so I would want
the excel to save to their own desktop. I have it saved to

C:\Documents and Settings\rfitz03\My Documents\AdageData.xls

but rfitz03 is my computer only, so if someone else opens and saves it
says can't find path.

C:\Documents and Settings\rfitz03\My Documents\AdageData.xls

 

[Douglas J. Steele]

Reread my suggestion.

The URL I gave you includes code to determine the desktop for the currently
logged on user.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


My issue is that my database is used by many people so I would want
the excel to save to their own desktop. I have it saved to

C:\Documents and Settings\rfitz03\My Documents\AdageData.xls

but rfitz03 is my computer only, so if someone else opens and saves it
says can't find path.

C:\Documents and Settings\rfitz03\My Documents\AdageData.xls