Export Queries to Excel

D

Denver

I have a list box on my Form that has this RowSource
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Name] NOT LIKE
"~sq_*" ORDER BY [Name];

i have a cmdbutton (cmdRun Process) which has an event
Call exportspreadsheet

I have this module - for the Event Procedure of cmdRun Process(Call
exportspreadsheet)

Sub exportspreadsheet()
On Error GoTo HandleError

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook


Dim db As DAO.Database

Set db = CurrentDb

conPath = "D:\Database\Export Folders\EXCEL\DCL All Site2.xlt"

'delete the spreadsheet
Kill conPath & "MySpreadsheet.xls"

' create a workbook from the template
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(conPath & "DCL All Site2.xlt")
'objXLApp.Visible = True

objXLBook.SaveAs (conPath & "MySpreadsheet.xls")
objXLBook.Close

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "DCN01",
conPath & "MySpreadsheet.xls", True


MsgBox "Done!" & vbCrLf & vbCrLf & "Look in the directory" & vbCrLf & vbCrLf
& "where the application sits for ""MySpreadsheet.xls"""

ProcDone:
On Error Resume Next

' Let's clean up our act
Set qdf = Nothing
Set db = Nothing
Set rs = Nothing
Set objResultsSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing


ExitHere:
Exit Sub
HandleError:
Select Case Err.Number
Case 3265
Resume Next
Case 1004
Set objXLBook = objXLApp.Workbooks.Open("D:\Database\Export
Folders\EXCEL\DCL All Site2.xlt")
Resume Next
Case 53
Resume Next
Case 75
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
End Select
Resume ProcDone
End Sub

MY PROBLEM IS HOW CAN I MODIFY THE CODES THAT WHATEVER SELECTION I MADE
FROM THE LIST BOX WILL BE EXPORTED TO EXCEL? AND EXPORT IT TO THIS PATH
D:Database\Export Folders\Excel, is it possible? pls. need your help and pls
modify the codes.
I am using Access 2003 and get this codes from the examples.

thanks

denver
 

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

Top