Excel/SQL Automation help

[cred]

Hi there... not sure if this is the correct place for this. If not please
tell me where to move it! Cheers...

Basically at the moment I have an SQL table with data relating to email
addresses as such:

Email Count1 Count2 Date_added
(e-mail address removed) 3 2 23-04-2009
(e-mail address removed) 2 1 23-02-2009
(e-mail address removed) 1 1 23-01-2009

etc...

I have written a query in MS SQL that creates a new table, with only email
addresses inside a certain criteria... Count1>2, Count2>=2, <= 14 days old

And this is linked to a pivot table in excel. At the moment, every day I am
running the code from MS SQL Manager, creating a duplicate excel sheet for
that specific date and refreshing the data from the SQL form and adding the
emails that are flagged to a list.

Finally the NEW emails (for each day) are being moved into another sheet
(request form) to be sent off with some standardised text before them, like
customer_email = (e-mail address removed)

Ideally what I want to do is automate this to a certain extent... perhaps
run this all from an Excel sheet? I am particularily frustratred with the
last bit... copying only the NEW emails to the request form. Emails can
appear on the original database upto 14 days after they are relavent so I
can't use the ones from the last day... I need to use the ones which haven't
appeared ever before.

I think the help I predominantly need is with the logistics of it all and
the linking SQL to Excel, but I'm at a loss for the last bit! Any help you
can provide would be awesome as I'm really stumped on this and wasting a lot
of time doing it manually!

I have a little bit of experience with Macros/VB so if that is the way to go
could you give me some tips?

Thanks

 

[Joel]

The only way you can figure out which email have been moved is by the person
who sent the email and the exact time of the e-mail including seconds. Not
the data that was added.

The only change you need in your code is to include the recieve time of the
email in the SQL, copying the receivved time to the new sheet, and a
comparison before yo move the e-mail to the new sheet to check if it has
already been moved.


You can run your macro from Outlook VBA, Access VBA, or Excel VBA. Any
office application can run VBA code from any other office application. You
can either

From Access you can open an excel workbook then run the code from the open
workbook or put the same excel code into access by opening an excel
application

set excelobj = CreateObject("Excel.Application")

or

set excelobj = GetObject(filename:=book1.xls")

 

[Marko Zitic]

Hi there... not sure if this is the correct place for this. If not please
tell me where to move it! Cheers...

Basically at the moment I have an SQL table with data relating to email
addresses as such:

Email         Count1     Count2     Date_added
(e-mail address removed)   3              2             23-04-2009
(e-mail address removed)   2              1             23-02-2009
(e-mail address removed)   1              1             23-01-2009

etc...

I have written a query in MS SQL that creates a new table, with only email
addresses inside a certain criteria... Count1>2, Count2>=2, <= 14 days old

And this is linked to a pivot table in excel. At the moment, every day I am
running the code from MS SQL Manager, creating a duplicate excel sheet for
that specific date and refreshing the data from the SQL form and adding the
emails that are flagged to a list.

Finally the NEW emails (for each day) are being moved into another sheet
(request form) to be sent off with some standardised text before them, like
customer_email = (e-mail address removed)

Ideally what I want to do is automate this to a certain extent... perhaps
run this all from an Excel sheet? I am particularily frustratred with the
last bit... copying only the NEW emails to the request form. Emails can
appear on the original database upto 14 days after they are relavent so I
can't use the ones from the last day... I need to use the ones which haven't
appeared ever before.

I think the help I predominantly need is with the logistics of it all and
the linking SQL to Excel, but I'm at a loss for the last bit! Any help you
can provide would be awesome as I'm really stumped on this and wasting a lot
of time doing it manually!

I have a little bit of experience with Macros/VB so if that is the way togo
could you give me some tips?

Thanks

Hi,

try using GemBoxSpreadsheet component for easiest work in VB with
Excel files. You can try component by using free version (limit 150
rows).
http://www.gemboxsoftware.com/GBSpreadsheet.htm

Filip
http://www.gemboxsoftware.com/

 

[cred]

Sorry bit of bad communication from me there... by 'emails' I am referring to
email addresses.

So each email address can appear on multiple days... weeks etc... Each day I
am execting an SQL statement which counts 1&2 and groups by email address.

I want a way to be able to add only the new email addresses. Which have
never been seen before. So I end up with a list of all correspondants and the
date on which they were first added to the list?

Is this possible? I think I can do the last bit... looking up which of there
are on that day and generating a request for that day... shouldn't be too
hard. But the list is really stumping me!

Cheers

 

[Joel]

It is simple to use the Find VBA function
set Tmpsht = Sheets("Sheet1")
set Mastersht = Sheets("Sheet2")

with Mastersht
LastRow = .Range("A" & Rows.count).end(xlup).row
NewRow = LastRow + 1
end with

RowCount = 1
with Tmpsht

Do while .range("A" & RowCount) <> ""
email = .range("A" & RowCount)
with Mastersht
set c =
..Columns("A").find(what:=email,lookin:=xlvalues,lookat:=xlwhole)
end with

if c is nothing then
.rows(RowCount).copy _
destination:=MasterSht.Rows(NewRow)
NewRow = NewRow + 1
end if
RowCount = RowCount + 1
loop
end with