I'm not sure I follow exactly what you are asking, but I've done a lot of
exporting to excel and creating custom excel reports, so I'll attempt to
answer.
Here are the steps I normally take to do an excel report exported from access:
1) Create an excel template with basic formatting already in place. That way
you are inserting data into a report that already has certain column widths,
fonts, etc.
2) pull a recordset from the data table that only contains the data you want
to export. If there is filtering to be done, I do it here.
3) Copy the excel template to a new (invisible) spreadsheet and begin
writing data into the new spreadsheet.
So instead of saying
Set rc = db.OpenRecordset("RSM Table")
You could use SQL statements to filter it similar to the following:
sq = "Select * from 'RSM Table' Where AccountKey = 1234"
rc.open sq, cn
So if your sql statement is filtering properly, you can simply export all
records from the rc recordset into the excel spreadsheet without having to
filter it there.
I didn't go into too great of detail in case I misunderstood what you are
trying to do, but I'm guessing that is what you want.
"TheNovice" wrote:
> Good morning all,
>
> I have a quick Question. I need to send a table out in Excel, I have the
> code set up but I need to filter it out to equal what is on the table.
>
> I am recycling some old code that uses a report but the format is not what
> we are looking for.
>
> Here is a sample of the code: can someone PLEASE HELP!
>
> Private Sub Form_Open(Cancel As Integer)
> Dim db As Database
> Set db = CurrentDb
> Dim rc As Recordset
> Dim stToName As String
> Dim strRSM As String
> Dim strSubj As String
> Dim strBody As String
> Dim strFileName As String
> Dim rpt As TableDef
>
> DoCmd.SetWarnings False
> DoCmd.OpenQuery "qryWinFull"
> DoCmd.OpenQuery "qryWinFullTable"
> DoCmd.SetWarnings True
> 'DoCmd.OpenQuery "WinFull", acViewDesign
> 'DoCmd.OpenTable Winfull, acViewNormal, acEdit
> 'Set rpt = Table!Winfull
> 'rpt.Visible = False
>
> Set rc = db.OpenRecordset("RSM Table")
> If Not (rc.BOF Or rc.EOF) Then
> rc.MoveFirst
> Do Until rc.EOF
> stToName = rc!emailid
> strSubj = "Tomorrow's orders within 20% of Full Pallet"
> strBody = "Please find the Enclosed Report for Tomorrow's orders
> within 20% of Full Pallet"
> strRSM = rc![CMRSM#]
> strFileName = "c:\commun\Within 20 Percent of Pallet for " +
> strRSM + ".xls"
> BuildExcelSht stToName, strSubj, strBody, strFileName, strRSM
> 'Set rpt = Table!Winfull
> rc.MoveNext
> Loop
> End If
> DoCmd.SetWarnings False
> DoCmd.Close acQuery, "qryWinFullTable"
> Exit Sub
>
>
> End Sub
>
> Sub BuildExcelSht(stToName As String, strSubj As String, strBody As String,
> strFileName As String, strRSM As String)
>
> 'rpt.Filter = "RGN = " & Chr(34) & stDSM & Chr(34)
> 'DoCmd.SendObject acReport, stDocName, "Snapshot Format", stToName,
> stCCName, , stSubjLine, stBody, False
> DoCmd.OutputTo acOutputTable, "Winfull", "Microsoft Excel", strFileName
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "(E-Mail Removed)"
> objEmail.To = stToName
> 'objEmail.Cc = stCCName
> objEmail.Subject = stSubjLine
> objEmail.Textbody = stBody
> objEmail.AddAttachment strFileName
> objEmail.Configuration.Fields.Item _
> ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
> objEmail.Configuration.Fields.Item _
> ("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
> "xxxxxx.xxxxx.com"
> objEmail.Configuration.Fields.Item _
> ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
>
>
> Exit Sub
>
>
> End Sub
>
>
> --
> -The Novice
> Learn Today, Teach Tomorrow
>
> Great Success is ones ability to ask for Help.
|
Similar Threads
