Export from access and format in excel

[Tadynn]

Hi,

Using Access 2k


I've got several scripts that I run that use the DoCmd.OutputTo function to output data from temporary tables in access into new excel spreadsheets.

My problem is to do more with formatting. I want to automatically create a heading and insert a date so that my report looks more like this:

---------------------------------------------------------
RMA LISTING

Date: 23/08/2004

RMA No Part No Serial No Status
123456 PT-31000 1g44y179 0
145788 PT-31005 0tyy6ye4 9

Total: 2 item(s)
---------------------------------------------------------

Rather than just
---------------------------------------------------------
RMA No Part No Serial No Status
123456 PT-31000 1g44y179 0
145788 PT-31005 0tyy6ye4 9
---------------------------------------------------------

Below is a piece of code that is behind one of my forms, if it can be used.

Private Sub RunRpt_Click()
On Error GoTo Err_RunRpt_Click

Dim stDocName As String
stDocName = "rpt_ItemsScanned"

'Extract records based on date parameters and make a table of the results
MakeTbl = "SELECT tbl_RMADetails.ScanDate, tbl_RMADetails.Part, " & _
"tbl_RMADetails.Serial INTO tmp_ItemsScanned " & _
"FROM tbl_RMADetails " & _
"GROUP BY tbl_RMADetails.ScanDate, tbl_RMADetails.Part, tbl_RMADetails.Serial " & _
"HAVING (((tbl_RMADetails.ScanDate) Between " & _
"[Forms]![frm_SearchTotal]![FromDate] And " & _
"[Forms]![frm_SearchTotal]![ToDate]))"

'Turn warnings off
DoCmd.SetWarnings False

'User selects whether to show a report or output the results into excel
If Me.ShowRptPreview.Value = -1 Then
DoCmd.Minimize
DoCmd.OpenReport stDocName, acPreview
Else
'Create the table
DoCmd.RunSQL MakeTbl
'Output the data into Excel
DoCmd.OutputTo acTable, "tmp_ItemsScanned", "MicrosoftExcel(*.xls)", "C:\Documents And Settings\tadynn\Desktop\Items scanned from " & Format([Forms]![frm_SearchTotal]![FromDate], "yyyy-mm-dd") & " - " & Format([Forms]![frm_SearchTotal]![ToDate], "yyyy-mm-dd") & ".xls", True, ""
End If
'Turn warnings on
DoCmd.SetWarnings True

Exit_RunRpt_Click:
Exit Sub

Err_RunRpt_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_RunRpt_Click

End Sub

Thanks,
Tadynn

 

[me2you]

First you need to make an excel template file(*.xlt) and format the way you desire. From MsAccess, instantiate this xlt file, place what you want to place in it and then save as an xls file.

Hope this works. i do this all the time.

Peace,

me2you