export query to Excel

  • Thread starter Thread starter Song Su
  • Start date Start date
S

Song Su

Access 2003
I have a query called MyRoster. I setup a form and ask user to type in
Employee Number which will be a criterial in my MyRoster query.

I want user to click a command button on my form so the query result will be
exported to Excel and the Excel should open and show. How to do that?

Thanks.
 
Song Su said:
Access 2003
I have a query called MyRoster. I setup a form and ask user to type in
Employee Number which will be a criterial in my MyRoster query.

I want user to click a command button on my form so the query result will
be exported to Excel and the Excel should open and show. How to do that?
Click to expand...
I think I can help you, but it would help me
if you could post back the SQL of your query.

In a general sense, I would imagine you have
a table "tblRoster" with fields

[Employee Number] (type Long)
Field2
Field3
Field4

You would have a combo box on your form
(say "cmboEmpNum") whose source was

SELECT DISTINCT tblRoster.[Employee Number]
FROM tblRoster ORDER BY tblRoster.[Employee Number]

Your user selects an empnum in combobox,
then clicks on a command button (say "cmdOpenXLS")

whose event code might look like:
(***UNTESTED***)

Private Sub cmdOpenXLS_Click()
On Error GoTo Err_cmdOpenXLS_Click

Dim strSQL As String
Dim lngEmpNum As Long
Dim strExcelPath As String
Dim objExcel As Object 'late binding


'has user selected EmpNum?
If IsNumeric(Me!cmboEmpNum)=TRUE Then
lngEmpNum = Me!cmboEmpNum
Else
MsgBox "Please select an Employee Number."
GoTo Exit_cmdOpenXLS_Click
End If

strExcelPath = "C:\EmpNum.xls"

strSQL = "SELECT * INTO " _
& "[Excel 8.0;Database=" & strExcelPath & "].[" _
& Format(Now(), "yyyymmdd\_hhnnss") _
& "_EmpNum_" & lngEmpNum & "] " _
& "FROM MyRoster "
& "WHERE [Employee Number] = " & lngEmpNum

CurrentDb.Execute strSQL, dbFailOnError

'open xls file
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open strExcelPath


Exit_cmdOpenXLS_Click:
If NOT (objExcel Is Nothing) Then Set objExcel = Nothing
Exit Sub

Err_cmdOpenXLS_Click:
MsgBox Err.Description
Resume Exit_cmdOpenXLS_Click

End Sub

There are so many details that could cause this to fail....

-- does not check if xls file is already open
-- assumed [Employee Number] was type Long...
 
Thank you for the info. Here is my query. Yes, my frmRoster has a button and
I want event of the button to export query result to Excel and start Excel
and display the roster.

SELECT Student.SECT, qryUniqCourse.SUBJ, qryUniqCourse.[NO],
qryUniqCourse.LT, Student.SID, Student.Last, Student.First, Student.M,
qryUniqCourse.RUNDATE
FROM qryUniqCourse INNER JOIN Student ON qryUniqCourse.SECT = Student.SECT
WHERE (((Student.Sem)=[Forms]![frmMainForm]![fraSemester].[value]) AND
((qryUniqCourse.EMPNO)=[Forms]![frmRoster].[txtEmpNo]))
ORDER BY Student.SECT, Student.Last, Student.First;

Thanks.

Gary Walter said:
Song Su said:
Access 2003
I have a query called MyRoster. I setup a form and ask user to type in
Employee Number which will be a criterial in my MyRoster query.

I want user to click a command button on my form so the query result will
be exported to Excel and the Excel should open and show. How to do that?
Click to expand...
I think I can help you, but it would help me
if you could post back the SQL of your query.

In a general sense, I would imagine you have
a table "tblRoster" with fields

[Employee Number] (type Long)
Field2
Field3
Field4

You would have a combo box on your form
(say "cmboEmpNum") whose source was

SELECT DISTINCT tblRoster.[Employee Number]
FROM tblRoster ORDER BY tblRoster.[Employee Number]

Your user selects an empnum in combobox,
then clicks on a command button (say "cmdOpenXLS")

whose event code might look like:
(***UNTESTED***)

Private Sub cmdOpenXLS_Click()
On Error GoTo Err_cmdOpenXLS_Click

Dim strSQL As String
Dim lngEmpNum As Long
Dim strExcelPath As String
Dim objExcel As Object 'late binding


'has user selected EmpNum?
If IsNumeric(Me!cmboEmpNum)=TRUE Then
lngEmpNum = Me!cmboEmpNum
Else
MsgBox "Please select an Employee Number."
GoTo Exit_cmdOpenXLS_Click
End If

strExcelPath = "C:\EmpNum.xls"

strSQL = "SELECT * INTO " _
& "[Excel 8.0;Database=" & strExcelPath & "].[" _
& Format(Now(), "yyyymmdd\_hhnnss") _
& "_EmpNum_" & lngEmpNum & "] " _
& "FROM MyRoster "
& "WHERE [Employee Number] = " & lngEmpNum

CurrentDb.Execute strSQL, dbFailOnError

'open xls file
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open strExcelPath


Exit_cmdOpenXLS_Click:
If NOT (objExcel Is Nothing) Then Set objExcel = Nothing
Exit Sub

Err_cmdOpenXLS_Click:
MsgBox Err.Description
Resume Exit_cmdOpenXLS_Click

End Sub

There are so many details that could cause this to fail....

-- does not check if xls file is already open
-- assumed [Employee Number] was type Long...
Click to expand...
 
My employee number is Text type.
My command button on form is cmdGetIt.
User types employee number in a unbound textbox called txtEmpNo on my form.
I prefer the path is ..\My Documents\ (user cannot save file in c: root as
the computers have joined domain in the network)
Users might use Excel 2000, Excel XP or Excel 2003.

See my query in previous reply.

thanks.

Gary Walter said:
Song Su said:
Access 2003
I have a query called MyRoster. I setup a form and ask user to type in
Employee Number which will be a criterial in my MyRoster query.

I want user to click a command button on my form so the query result will
be exported to Excel and the Excel should open and show. How to do that?
Click to expand...
I think I can help you, but it would help me
if you could post back the SQL of your query.

In a general sense, I would imagine you have
a table "tblRoster" with fields

[Employee Number] (type Long)
Field2
Field3
Field4

You would have a combo box on your form
(say "cmboEmpNum") whose source was

SELECT DISTINCT tblRoster.[Employee Number]
FROM tblRoster ORDER BY tblRoster.[Employee Number]

Your user selects an empnum in combobox,
then clicks on a command button (say "cmdOpenXLS")

whose event code might look like:
(***UNTESTED***)

Private Sub cmdOpenXLS_Click()
On Error GoTo Err_cmdOpenXLS_Click

Dim strSQL As String
Dim lngEmpNum As Long
Dim strExcelPath As String
Dim objExcel As Object 'late binding


'has user selected EmpNum?
If IsNumeric(Me!cmboEmpNum)=TRUE Then
lngEmpNum = Me!cmboEmpNum
Else
MsgBox "Please select an Employee Number."
GoTo Exit_cmdOpenXLS_Click
End If

strExcelPath = "C:\EmpNum.xls"

strSQL = "SELECT * INTO " _
& "[Excel 8.0;Database=" & strExcelPath & "].[" _
& Format(Now(), "yyyymmdd\_hhnnss") _
& "_EmpNum_" & lngEmpNum & "] " _
& "FROM MyRoster "
& "WHERE [Employee Number] = " & lngEmpNum

CurrentDb.Execute strSQL, dbFailOnError

'open xls file
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open strExcelPath


Exit_cmdOpenXLS_Click:
If NOT (objExcel Is Nothing) Then Set objExcel = Nothing
Exit Sub

Err_cmdOpenXLS_Click:
MsgBox Err.Description
Resume Exit_cmdOpenXLS_Click

End Sub

There are so many details that could cause this to fail....

-- does not check if xls file is already open
-- assumed [Employee Number] was type Long...
Click to expand...
 
This code will export the query results to Excel and open the spreadsheet:

Private Sub cmdGetIt_Click
Dim sXL as String, oXL as Object
sXL = "C:\folder\folder\...\SSName.xls" 'Full path to your spreadsheet
' Insert the actual name of your query between the quotes below
DoCmd.TransferSpreadsheet acExport, , "queryName", sXL
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
oXL.WorkBooks.Open FileName:=sXL
End Sub
 
Back
Top