Send a Table with Filters. HELP!

T

TheNovice

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

Jeff Boyce

I'm not clear on something.

I thought you needed to send out a set of data (table or otherwise).

A common approach to doing this is to create a query that returns what you
want to export, then export that query (actually, when you do the Export,
only the data goes.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

TheNovice said:
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 address 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.
Click to expand...
 
T

TheNovice

Jeff,

Thanks for the reply. What I have is a single table that has multiple
associates that it need to go to. but only their info.

the first two queries build the working tables,

the table WinFull has all of the information that has been formatted as
requested.

So I ponder this question. is there a way that I can apply a criteria
through VBA, say;

docmd.openquery "qryWinFull" where RGN = strRSM? this would create the
table need with only that Info?!?!

as you can tell I am very new to this.

I have a good Idea as the where to put this option if only i knew how?

PLEASE any advice is always appreciated.
--
-The Novice
Learn Today, Teach Tomorrow

Great Success is ones ability to ask for Help.


Jeff Boyce said:
I'm not clear on something.

I thought you needed to send out a set of data (table or otherwise).

A common approach to doing this is to create a query that returns what you
want to export, then export that query (actually, when you do the Export,
only the data goes.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

TheNovice said:
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 address 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.
Click to expand...
Click to expand...
 
A

APNetworking

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 said:
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 address 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.
Click to expand...
 

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

Similar Threads

Sending Emails to Individuals 3
CODE HELP! 2
HELP! - Import Loop 3
Delete a table using VBA 1
count of records in a table in external database 3
Code to send email to group of contacts 6
Accessing a new record 1
Garbage in a string variable loaded from a field 2

Top