Writing a file to the documents folder



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



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

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

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 Sub


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"
Case Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdImagesListExcel_Click
End Select

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question