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

 

[ErezM via AccessMonster.com]

hello Ryan

your first question:
you are using the right mechanism in the wrong place.
since the query you are exporting is created and then destroyed every time
the command button is clicked, you can do better with just one query, let's
say you call it "qryTempExport".

then only change it's contents (using the same query everytime):
Dim qdfTemp As DAO.QueryDef
Set qdfTemp=dbCurr.QueryDefs("qryTempExport")
qdfTemp.SQL="whatever the sql sentence is..."
set qdfTemp=Nothing

then only use the timestamp for the filename in the export procedure:
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=qryTempExport, FileName:= _

"K:\Corp Sourcing\Shared\Ryan Fitzpatrick\AdageData" & Format(Now, "yyyymmddhhnnss" & ".xls", hasfieldnames:=True

good luck
Erez

 

[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