Writing a file to the documents folder

[dhstein]

I have a macro that exports a table to an Excel file. There is a field in
the embedded macro for output file where I entered C:\FILENAME.xlsx. I want
to specify the user's documents folder regardless of what operating system
he's using. In DOS I could use something like %USERPROFILE%\MYDOCU~1. Is
there a way to do this in Access? Thanks for any help on this.

 

[M Skabialka]

I use this in Access 2007 VBA which defaults to the user's desktop, no
matter who uses it.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryImagesListExcel", "..\Desktop\ImagesList.xls", True
It also works with
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryImagesListExcel", "..\My Documents\ImagesList.xls", True

Mich

 

[dhstein]

Thanks for your response. I have a couple of questions.

1) Does this work for XP and Vista?

2) What does the qryImagesListExcel look like ?

 

[M Skabialka]

Good question - I know it works in XP. I don't have a Vista machine here -
I thinks it's c:\users\theirname\documents - I'll have to check. In which
case use ..\documents - you'll have to test this.
There is nothing special about the query - it selects from a table and query
and is created in VBA code (I shortened it for this example).
This creates an unformatted Excel worksheet.

Private Sub cmdImagesListExcel_Click()
On Error GoTo Err_cmdImagesListExcel_Click

Kill "..\Desktop\ImagesList.xls" 'Deletes previous copy on the
desktop

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strDateSQL As String

Set dbs = CurrentDb

strDateSQL = "SELECT [FileName] & '.tif' AS FILENAME_TIF,
qryImagesinList2.BaseDocNumber AS [DWG No], " & _
"qryImagesinList2.BaseDocType AS [DOC TYPE],
qryImagesinList2.JFrameNumber AS [FRAME No], " & _
"qryImagesinList2.JNumberOfFrames AS [No FRAMES] " & _
"FROM qryImagesinList2, MetaData " & _
"WHERE (((MetaData.FILENAMETIF) = [FileName] & '.tif')) " & _
"ORDER BY [FileName] & '.tif';"

For Each qdf In CurrentDb.QueryDefs 'deletes the query created
previously to avoid an error message
If qdf.Name = "qryImagesListExcel" Then
CurrentDb.QueryDefs.Delete "qryImagesListExcel"
Exit For
End If
Next

Set qdf = dbs.CreateQueryDef("qryImagesListExcel", strDateSQL)
'creates a new query and saves it

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryImagesListExcel", "..\Desktop\ImagesList.xls", True
'send to desktop

MsgBox "'ImagesList.xls' speadsheet created successfully on Desktop"

Exit_cmdImagesListExcel_Click:
Exit Sub

Err_cmdImagesListExcel_Click:

Select Case Err.Number
Case 53 'File not found to delete
Resume Next
Case 3012 'File already exists
DoCmd.SetWarnings True
CurrentDb.QueryDefs.Delete "qryImagesListExcel"
Resume
Case Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdImagesListExcel_Click
End Select

End Sub