Export from MS Access to MS Excel and show exported data in MS Excelta

[ctaylor]

I have already got a function running from a command button on a subform that exports data to MS Excel. What I would like to do is have MS Excel open at the end of the command click so that it shows the data on screen and appears to be part of the same operation to the end user.

Here is my working code that exports query results to an MS Excel file:


Private Sub cmdExportInfluenzaPneumoniaReport_Click()
On Error GoTo Err_cmdExportInfluenzaPneumoniaReport_Click

Dim dtDateTime As Date
Dim strDateTime As String

' Returns current system time in the system-defined long time format.
dtDateTime = Date
strDateTime = Format(dtDateTime, "MMM d yyyy")
MsgBox strDateTime

'Export report to MS Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryInfluenzaPneumoniaReport_7", "Influenza_Pneumonia_Report_" & strDateTime & ".xls"
DoCmd.Beep


Exit_cmdExportInfluenzaPneumoniaReport_Click:
Exit Sub

Err_cmdExportInfluenzaPneumoniaReport_Click:
MsgBox Err.Description
Resume Exit_cmdExportInfluenzaPneumoniaReport_Click

End SubExit Sub

Err_cmdInfluenzaReport_Click:
MsgBox Err.Description
Resume Exit_cmdInfluenzaReport_Click
End Sub




Alternately I was considering writing another line or so of code to try to open the MS Excel document from within MS Access, but didn't figure that one out either. This statement


DoCmd.RunCommand acCmdOutputToExcel


will export the contents of the main form (excluding the contents of containing the subform to MS Excel and leave the MS Excel open so that data may be viewed, but don't know how to do this and have it open a pre-existing MS Excel file.

Any thoughts would be appreciated.

Thanks in advance.

[Creosote]

Put this is your global declarations of the vba:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpszOp As String, _
ByVal lpszFile As String, ByVal lpszParams As String, _
ByVal LpszDir As String, ByVal FsShowCmd As Long) _
As Long

Put this wherever you actually want to open the file:

ShellExecute src_hDC, "Open", "C:\SomeFolder\SomeFile.xls", "", "C:\", 1

More info/Source

[ctaylor]

Once again, I could not get the code to compile when I got around to testing it. ('src_hDC' would not compile as it remained undefined.)

I did a little looking around and found something that does appear to work for me though by using the OUTPUTTABLE method in place of the TRANSFERSPREADSHEET method. The last argument of the OUTPUTTABLE method actually allows you to open the document. (The -1 argument opens the document once it has been created. A value of 0 would leave the document created, but unopened.) To get this to work I created a temporary extract table to store the values prior to outputting them.

'Returns current system time in the system-defined long time format.
dtDateTime = Date
strDateTime = Format(dtDateTime, "MMM_d_yyyy")
strExportFileName = "Influenza_Pneumonia_Report_" & strDateTime & ".xls"


'Export report to MS Excel
With DoCmd
.SetWarnings False
.OpenQuery "qryDeleteInfluenzaPneumoniaExtract"
.OpenQuery "qryAppendInfluenzaPneumoniaExtract"
.SetWarnings True
.OutputTo acOutputTable, "tblInfluenzaPneumoniaExtract", acFormatXLS, strExportFileName, -1
.Beep
End With

[ctaylor]

Creosote,

I'm still interested in resolving this ShellExecute concern. Do you have any ideas? Do you think there are any specific reference libraries I should have enabled for the shell23.dll library to be accessed?

My VBA module has the following libraries enabled:

Visual Basic for Applications
Microsoft Access 9.0 Object Library (MSACC9.OLB
OLE Automation (stdole2.tbl)
Microsoft ActiveX Data Objects 2.1 Library (msado21.tbl)
Microsoft Excel 9.0 Object Library (EXCEL9.OLB)


<EDIT>
Oops - I think I found it

Microsoft Shell Controls and Automation (SHELL32.DLL).

I will let you know if this works sometime next week.

CT
</EDIT>

[Creosote]

Yeah, I'm thinking its just VBA stuff, whereupon, you have to specifically reference things you normally wouldn't have to in VB.

Good luck