Use another method to export your data
What were they thinking?
Microsoft has dropped the Export function from Access 2007, but you would never know it from your legacy code since the program will not error! I understand the feature had it's issues, but the developer community learned how to deal with them, instead consider using the method suggested below.
Use a Temporary Table to Export Data
The following code will create a temporary table in your current Access database and then export that table to Excel. In our Access report generator we use a table, Reports, to store information used in the program. Replace the strTable and strSQL with your own versions in order to make the code work for you.
Private Sub OutputToExcel(strSpreadsheetName As String, strWhere As String)
'Created by Juan Soto for AccessExperts.net
'Code can be used anywhere as long as you keep above comment and this one intact
Dim strSQL As String 'Used to create temporary table in local database
Dim strTable As String 'Used to store temporary table name
On Error GoTo ErrorHandler
'Delete temporary table
DoCmd.RunSQL "Drop Table tblTemp"
strTable = DLookup("TableSource", "Reports", "[ReportID] = '" & Me.lstReports & "'")
strSQL = "Select " & strTable & ".* into tblTemp from " & strTable & " Where " & strWhere
CurrentDb.Execute strSQL, dbSeeChanges
DoCmd.OutputTo acOutputTable, "tblTemp", acFormatXLS, strSpreadsheetName, True
Exit Sub
ErrorHandler:
If Err.Number = 3376 Then 'Table does not exist, ok to proceed
Resume Next
End If
End Sub
Conclusion
You may consider using a temporary Access database to store the data if your concerned with file bloating.
|
