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..) ?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.