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
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
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
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