ACCESS VBA

[auujxa2 via AccessMonster.com]

I'm working on a project, which basically needs to email someone anytime a
specific column in one of many workbooks.

So, first, I'd need to do a search of all subfolders for all .xls files, then,
determine if certain columns were change, and if so, the auto email.

If anyone could lend me a sense of direction, it'd be greatly appreciated.

Thank you

 

[John Spencer]

First question that comes to mind is HOW are you going to know that a value
has changed and whether or not you need an email.

Are you recording the values somewhere so you can compare the "old" value
with the currently existing value? It seems you would have to have a table
that recorded the name of the xl file, the name of the worksheet (assuming
multiple worksheets), the cell locations, and the cell values. That table
then would have to be used to spot the changes and send the email and then
update the specific record with the new value(s)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Klatuu]

In addition to John's comments, you are designing a method that will,
depending on the number of xls files, will be very slow.
Aslo, will users be working with the xls files? If so, you may have sharing
problems.

If there is any other way to accomplish your actual goal, I recommend you
persue it.

 

[auujxa2 via AccessMonster.com]

I was considering creating a temp folder, and copying each xls to that folder,
then having those linked as a table in access. Then, create queries to
compare last week's data to the newly copied/linked data. (i.e. if a-b is
null, then don't do anything)

but, if the fields aren't equal (i.e. there's been a change), then append
them to one master table.

Then email the table as xls to recipient, if there is data in it.

does that make sense?

First question that comes to mind is HOW are you going to know that a value
has changed and whether or not you need an email.

Are you recording the values somewhere so you can compare the "old" value
with the currently existing value? It seems you would have to have a table
that recorded the name of the xl file, the name of the worksheet (assuming
multiple worksheets), the cell locations, and the cell values. That table
then would have to be used to spot the changes and send the email and then
update the specific record with the new value(s)

I'm working on a project, which basically needs to email someone anytime a
specific column in one of many workbooks.

[quoted text clipped - 6 lines]

Thank you

 

[auujxa2 via AccessMonster.com]

I'm not too concerned about the speed. it will be a scheduled task early in
the morning, or on a weekend. as far as sharing goes... that's why i was
going to copy the files into a temp folder first, each run time

In addition to John's comments, you are designing a method that will,
depending on the number of xls files, will be very slow.
Aslo, will users be working with the xls files? If so, you may have sharing
problems.

If there is any other way to accomplish your actual goal, I recommend you
persue it.

I'm working on a project, which basically needs to email someone anytime a
specific column in one of many workbooks.

[quoted text clipped - 5 lines]

Thank you

 

[auujxa2 via AccessMonster.com]

or perhaps i can loop through the subfolders, importing each excel file into
a separate table. I already know the field headings in excel are in row 6.
Then, save the recently imported temp table, as the master table, so the next
week, I can compare it to the next imported data, and so on...

I was considering creating a temp folder, and copying each xls to that folder,
then having those linked as a table in access. Then, create queries to
compare last week's data to the newly copied/linked data. (i.e. if a-b is
null, then don't do anything)

but, if the fields aren't equal (i.e. there's been a change), then append
them to one master table.

Then email the table as xls to recipient, if there is data in it.

does that make sense?

First question that comes to mind is HOW are you going to know that a value
has changed and whether or not you need an email.

[quoted text clipped - 11 lines]

 

[Tony Toews [MVP]]

I'm working on a project, which basically needs to email someone anytime a
specific column in one of many workbooks.

So, first, I'd need to do a search of all subfolders for all .xls files, then,
determine if certain columns were change, and if so, the auto email.

So what happens when users insert a column in the spreadsheet? Or
delete rows or many other things that users are prone to do.

Should this data be put in an Access database instead to ensure
orderly updating of data?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[auujxa2 via AccessMonster.com]

Currently, I loop through subfolders to import all excel workbooks. Then
copy the tables and rename them, so I have the originals AND an archive to
compare it to. Then I built several queries that show the difference between
the two tables. And if there are changes, it's appended to a table, which i
attach to an email.

My challenge, is to somehow be able to link this week's table, with last
week's table, IF there is a new workbook created.

Here is my code below.

Private Sub Form_Open(Cancel As Integer)

DoCmd.SetWarnings False

'Imports every excel workbook from all subfolders into temp table
Call ListFilesToTable("F:\Advertising Planning\Limited Time Restore\Limited
Time Offer Templates\Spring 08", "*.xls", True)

'appends newly created files to master email table
DoCmd.OpenQuery "NewFileThisWeekQry"
'appends deleted or renamed files to master email table
DoCmd.OpenQuery "LastWeekFileRenameOrDeletedQry"

'Imports this weeks workbooks as tables
Dim dbs As Database, rst As Recordset
Dim myPath As String
Dim myCount As Variant
Dim myFile As String
Dim myFile1 As String
myCount = 0
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Files")
With rst
Do While Not rst.EOF
myCount = myCount + 1
myPath = rst.Fields("FPath")
myFile = rst.Fields("FName")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, myFile,
myPath & myFile
rst.MoveNext
Loop
rst.Close
End With

myCount = 0
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("DeleteTableQry")


''''''''this is where i need help. I have 2 queries, one with the new table
names, and one with last weeks. I need to link them together and show there
difference between several columns




With rst
Do While Not rst.EOF
myCount = myCount + 1
myPath = rst.Fields("Name")
DoCmd.Rename myPath & "LastWeek", acTable, myPath
rst.MoveNext
Loop
End With
rst.Close

'Emails table if there are changes, sends email with no attachment if there
are no changes
If DCount(1, "EmailTemplateTbl") > 0 Then
DoCmd.OutputTo acOutputTable, "EmailTemplateTbl", acFormatXLS, "C:\Documents
and Settings\auujxa2\Desktop\My Reports\Weekly LTO Updates.xls"
Dim notesdb As Object
Dim notesdoc As Object
Dim notesrtf As Object
Dim notessession As Object

EmailSendto = "John Abrahamson"
'EmailCCto = ""
EmailSubject = "LTO Weekly Updates"

Set notessession = CreateObject("Notes.Notessession")
Set notesdb = notessession.getDatabase("", "")

Call notesdb.openmail
Set notesdoc = notesdb.createdocument
Call notesdoc.replaceitemvalue("Sendto", EmailSendto)
Call notesdoc.replaceitemvalue("CopyTo", EmailCCto)
Call notesdoc.replaceitemvalue("Subject", EmailSubject)
Set notesrtf = notesdoc.createrichtextitem("body")
Call notesrtf.appendtext("This is an automated email, informing you of
updates in any of the LTO folders within the past week.")
Call notesrtf.addnewline(2)
Call notesrtf.embedObject(1454, "", "C:\Documents and Settings\auujxa2\
Desktop\My Reports\Weekly LTO Updates.xls")
Call notesdoc.Send(False)
Set notessession = Nothing
Else
EmailSendto = "John Abrahamson"
'EmailCCto = ""
EmailSubject = "NO LTO Updates This Week"

Set notessession = CreateObject("Notes.Notessession")
Set notesdb = notessession.getDatabase("", "")

Call notesdb.openmail
Set notesdoc = notesdb.createdocument
Call notesdoc.replaceitemvalue("Sendto", EmailSendto)
Call notesdoc.replaceitemvalue("CopyTo", EmailCCto)
Call notesdoc.replaceitemvalue("Subject", EmailSubject)
Set notesrtf = notesdoc.createrichtextitem("body")
Call notesrtf.appendtext("This is an automated email, informing you there
are NO updates in the LTO folders this past week.")
Call notesdoc.Send(False)
Set notessession = Nothing
End If

'archives this weeks file names to another table
DoCmd.OpenQuery "LastWeeksFileNamesQry"
'removes this weeks file names from temp table
DoCmd.RunSQL "DELETE Files.* FROM Files;"
'clears master email table after it's emailed
DoCmd.RunSQL "DELETE EmailTemplateTbl.* FROM EmailTemplateTbl;"

DoCmd.SetWarnings True

DoCmd.Close acForm, "frmCheckForLTOupdates"

End Sub

First question that comes to mind is HOW are you going to know that a value
has changed and whether or not you need an email.

Are you recording the values somewhere so you can compare the "old" value
with the currently existing value? It seems you would have to have a table
that recorded the name of the xl file, the name of the worksheet (assuming
multiple worksheets), the cell locations, and the cell values. That table
then would have to be used to spot the changes and send the email and then
update the specific record with the new value(s)

I'm working on a project, which basically needs to email someone anytime a
specific column in one of many workbooks.

[quoted text clipped - 6 lines]

Thank you