Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel/SQL Automation help
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") "cred" wrote: 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 3 2 23-04-2009 2 1 23-02-2009 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... Count12, 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 = 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel/SQL Automation help
On Jul 27, 11:06*am, cred wrote:
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 * 3 * * * * * * *2 * * * * * * 23-04-2009 * 2 * * * * * * *1 * * * * * * 23-02-2009 * 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... Count12, 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 = 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 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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel/SQL Automation help
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" wrote: 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") "cred" wrote: 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 3 2 23-04-2009 2 1 23-02-2009 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... Count12, 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 = 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel/SQL Automation help
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,l ookat:=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 "cred" wrote: 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" wrote: 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") "cred" wrote: 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 3 2 23-04-2009 2 1 23-02-2009 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... Count12, 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 = 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Supressing the ctrl-c and other keys during word automation in automation | Excel Programming | |||
Excel automation in VB.net | Excel Programming | |||
Excel automation in MFC | Excel Programming | |||
Excel automation | Excel Programming | |||
Vb.net - excel 97 automation | Excel Programming |