PDA

View Full Version : add alert to txt if criteria met



tsatsos007
04-07-2011, 04:19 AM
Hi all,

I have in mind making a file to look in a range of cells (g4:g30) ..which is a date column ...

and say If(this date is equal to today() ) ...go to the txt file in the specified folder ... say C:\alerts.txt [assume already created] ......
and write in there the specific text of a cell in this excel (actually the name of a company)

also could that be done without opening the file? ..or as usual.. on open/close..??

...any ideas ?

thank u

mbarron
04-07-2011, 06:40 PM
The following assumes the company name is in the E column, It will add the date and company name to the end of the text file.

Sub alerter()
Dim iFile As Integer
Dim sAlert As String
Dim r As Range

iFile = FreeFile

Open "c:\alerts.txt" For Append As #iFile
For Each r In Range("G4:G30")
If r = Date Then
Print #iFile, r & " " & r.Offset(0, -2)
End If
Next
Close #iFile
End Sub

tsatsos007
04-08-2011, 12:31 AM
grrrrr...u make it seem so easy!!! lol...
thank u ..i ll try it asap and let u know of the results

ohhh...and does it work 'offline' or does it record the text in txt when the excel is opened?

..for the specific sheet should i replace with:

sheets("sheet1").range("g4:g30")

? is this correct?

tsatsos007
04-08-2011, 01:17 AM
it doesn t seem to record anything in txt.. i must be doing sth wrong.. my guess is that i should paste the code to a different declaration? take a look at the files..
i also included sheet2 for company name.. how can i make it select the company name from that cell?

mbarron
04-08-2011, 05:40 AM
The macro will run fine in the workbook module but should be in a Standard module instead.
The macro, when run with Sheet1 of your attached workbook being active (on 4/8/2011 -US dating) will insert the following into the C:\alerts.txt file (If the file does not exist, it will be created):

4/8/2011 c
4/8/2011 a
4/8/2011 a

mbarron
04-08-2011, 05:42 AM
Sorry - I missed the part of your last post concerning using the company name on Sheet2

Sub alerter()
Dim iFile As Integer
Dim sAlert As String
Dim r As Range

iFile = FreeFile

Open "c:\alerts.txt" For Append As #iFile
For Each r In Range("G4:G30")
If r = Date Then
Print #iFile, r & " " & Worksheets("Sheet2").Range("E2")
End If
Next
Close #iFile
End Sub

tsatsos007
04-08-2011, 07:22 AM
works like charm ...
but i think i am confused with the module the code should be in... because i need to press run code ..for it to be run!
did u mean i have to create a new module and paste the code there ... or should i select 'this workbook' and paste the code there as a general object (or workbook object selection..) ?