Output Query to two Excel spreadsheets (1 Viewer)

reglarh

Registered User.
Local time
Today, 04:10
Joined
Feb 10, 2014
Messages
118
There have been a number of postings regarding outputting data from Access to Excel, but my problems is slightly different.
I want to output email addresses to MailChimp, but my members database contains a number of cases where two partners use the same email address. MialChimp does not allow duplication of an email address. Data is imported into MailChimp via a csv Excel file where I have removed duplicate records. Up till now this has been fine with just one email going to two partners.

Now I want to email all members via MailChimp and therefore need to create two Excel csv files followed by two MailChimp mailings. The code I need will read through all records from a query and output most of the records to Excel file 1, with the duplicates going to Excel File 2. Therefore bulk transfer is not possible. I also want to clear all rows in each Excel file before each run other than the first row which contains column headings.

Any ideas on the best approach?
 

June7

AWF VIP
Local time
Today, 03:10
Joined
Mar 9, 2014
Messages
5,425
How do you remove duplicates? Use that dataset for Excel File 1. Then maybe for Excel File 2 use a query that returns records NOT IN query 1.
 

reglarh

Registered User.
Local time
Today, 04:10
Joined
Feb 10, 2014
Messages
118
I currently remove them in Excel using a standard Excel function. I am keen to do as much processing in Access as possible to simplify operation for users of the system, which is 98% Access based. In Excel, I would use three sheets. Raw data on Sheet 1, copy it to Sheet 2 and remove duplicates, read Sheet 1 and 2, copying data only in Sheet 1 into Sheet 3. But then I would need to copy Sheet 2 and 3 into separate spreadsheets since MailChimp only reads the first sheet in a file.

That's why I would prefer a vba implementation in Access.
 

June7

AWF VIP
Local time
Today, 03:10
Joined
Mar 9, 2014
Messages
5,425
Should be able to do a query that will return one record for each email address that would include the record ID. A nested TOP N might accomplish. Review: http://allenbrowne.com/subquery-01.html#TopN. Then another query could return records from table where record ID NOT IN the first query.

Export each query to csv.
 

reglarh

Registered User.
Local time
Today, 04:10
Joined
Feb 10, 2014
Messages
118
My goodness, that looks complicated, and I am trying to pass over my work to a less experienced person!
I have persevered with a simpler approach with partial success. The code to eliminate duplicates and write to a second worksheet will not be a problem, apart from not being able to close down the workbooks correctly.

When my code exits, the MC1 data is written to a copy of MC1, and when I try to open MC1 or MC2 I am told they are locked by another user (me!). How do I overcome this problem?

Also, how do I insert code I the way that others do?

The as yet incomplete code is:

Private Sub Command0_Click()

Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim i As Integer
Set db = CurrentDb

Dim objExcelApp As Object
Dim wb1, wb2 As Object
Dim ws1, ws2 As Object

Set objExcelApp = CreateObject("Excel.Application")
Set wb1 = objExcelApp.Workbooks.Open("D:/MC1")
Set wb2 = objExcelApp.Workbooks.Open("D:/MC2")
Set ws1 = wb1.Sheets(1)
Set ws2 = wb1.Sheets(2)



ws1.Cells(1, 1).Value = "FirstName"
ws1.Cells(1, 2).Value = "LastName"
ws1.Cells(1, 3).Value = "PrivateeMail"
ws1.Cells(1, 4).Value = "GroupLeader"
ws2.Cells(1, 1).Value = "FirstName"
ws2.Cells(1, 2).Value = "LastName"
ws2.Cells(1, 3).Value = "PrivateeMail"
ws2.Cells(1, 4).Value = "GroupLeader"

i = 2

strSQL = "select * from MailChimpAddresses"
Set rs = db.OpenRecordset(strSQL)

Do While Not rs.EOF
ws1.Cells(i, 1).Value = rs!FirstName
ws1.Cells(i, 2).Value = rs!LastName
ws1.Cells(i, 3).Value = rs!PrivateeMail
ws1.Cells(i, 4).Value = rs!GroupLeader
rs.MoveNext
i = i + 1
Loop
MsgBox i

wb1.Close SaveChanges:=True
wb2.Close SaveChanges:=True
Set wb1 = Nothing
Set wb2 = Nothing



End Sub
 

Users who are viewing this thread

Top Bottom