Get path to My Documents folder on different drive paths through VBA 2

[zionman4]

Hi All,
I was reading thread705-805911 where exporting data to "My Documents" folder can be done through
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "QueryName", _
"C:\Documents and Settings\" & Environ("username") & "\My Documents\" & strFileName & ".xls", True, ""

Some users have the My Documents folder pointing to Drive D and others on Drive C. How can I export to this folder regardless of the path?

Thanks!

 

[PHV]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "QueryName", _
CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\" & strFileName & ".xls", True, ""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zionman4]

This worked great!
I have another question regarding the same. Because the data I'm exporting comes from a link SQL Server tables I need to add the "Save formatted" otherwise I get an error "Operation not supported for this type of object". I know that when I exported the query manually and click on Save formatted the error went away. How can I do this using the above code suggested by PHV?

Thanks Again!!!