Open Excel Spreadsheet after TransferSpreadsheet (1 Viewer)

[KACJR]

:banghead:
Greetings to the well of knowledge...

Running Access 2010 and suffering from CRS...

There was a time, as I recall, when one could launch Excel from Access VB with a single command line but now I cannot recall how to do this.

Additionally, when I execute TransferSpreadsheet, I only supply the file name for the XL file, no path. Access knows what the default file/save path is. But I can't seem to find a way identify this path in Access VB. Environ("USERPROFILE") does not provide this and I can't find another environment variable that does.

Can anyone point me in the right direction?

Kind regards,
Ken

 

[Ranman256]

vFile = "c:\my docuemtns\myfile.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, vQry, vFile, True, vTab

call shell("c:\folder\excel.exe " & vFile)

 

[KACJR]

I understand this. The issue is identifying the end user's file/save folder. For example, my default file/save folder is on my E: drive, but using Environ("USERPROFILE") only tells me that profile is on my C: drive. There must be another system variable that tells me where that file/save location is.

 

[sxschech]

I have the user select the folder location, then I have the information. You may want to add a message or inform your users that they are selecting the folder location where they want to save the file and not choosing the filename as you indicated that you already have that information.

...
Dim stExportPath As String
...
...
stExportPath = selectFolder()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, stTableQuery, stExportPath & "\" & stTableQuery & ".xls", True

The selectFolder code goes in a standard module

Function selectFolder()
'--------------------------------------------------
' File Browse Code and pick a folder
'--------------------------------------------------
'NOTE: To use this code, you must reference
'The Microsoft Office 14.0 (or current version)
'Object Library by clicking menu Tools>References
'Check the box for:
'Microsoft Office 14.0 Object Library in Access 2010
'Microsoft Office 15.0 Object Library in Access 2013
'Click OK
'http://answers.microsoft.com/en-us/office/forum/office_2003-customize/vba-example-select-a-directory/f1c57e80-8185-48de-8c03-8bc52770a44e
'modified to style of Function selectFile
'--------------------------------------------------
    Dim Fd As FileDialog, FolderName As String
    Set Fd = Application.FileDialog(msoFileDialogFolderPicker)
    Fd.AllowMultiSelect = False
    Fd.Title = "Choose a Folder"
    If Fd.Show = True Then
        FolderName = Fd.SelectedItems(1)
    End If
    
    'clear file dialog
    Set Fd = Nothing
    'Return Folder name and path
    selectFolder = FolderName
End Function