Converting and Excel Visual Basic File to an Access Visual Basic 
Author Message
 Converting and Excel Visual Basic File to an Access Visual Basic

Woulld you please tell me how to convert the attached excel visual
basic file so that it would access data in an "access" table database
and develop a query that would look for the the current date.
What the present database does is to search column A for the current
date. When it finds the current date it it then moves one column to
the right to picks up the subject;then  three columns to the right to
pick up the address; and four columns to the right for the subject.
Then it e-mails the message.  This macro in excel visual basic works
perfectly. My problem is that I want to put the data into access query
and have the query find the date. Can you help me.  The visual basic
access macro is below.

Sub Finddate2()

    Dim DateRange As Range
    Dim c As Range

     'set outlook object references
     Worksheets(1).Activate
     Columns("A:A").Select
    Set DateRange = Application.Intersect(Range("a1").EntireColumn, _
        ActiveSheet.UsedRange)

     For Each c In DateRange
         If IsDate(c.Value) Then
            If CLng(CDate(c.Value)) = CLng(Date) Then
                 Call NextCharacter(c)
             End If
         End If
     Next c

 End Sub

Sub NextCharacter(mycell As Range)
Set ol = CreateObject("Outlook.Application")
Set MailSendItem = ol.CreateItem(olMailItem)
Set olns = ol.GetNamespace("Mapi")

    Dim mySubj As String
    Dim myAddr As String
    Dim myBody As String

     mySubj = mycell.Offset(0, 1).Value
     myAddr = mycell.Offset(0, 3).Value
     myBody = mycell.Offset(0, 4).Value

'     MsgBox mySubj & "--" & myBody & "--" & myAddr

   With MailSendItem
     .Subject = mySubj
     .To = myAddr
     .Body = myBody
     .Send
    End With
End Sub



Sat, 17 Jul 2004 08:56:16 GMT  
 Converting and Excel Visual Basic File to an Access Visual Basic

The SQL view of the query would look something like this

SELECT TableName.Subject, TableName.Address, TableName.Body
        FROM TableName
        WHERE CLng(TableName.TheDate)=CLng(Date());

Note that it's a bad idea to call a field "Date" because of the
existence of the Date() function; can cause confusion.

You'd use it by creating a recordset based on the query (assume it's
called "qryTodaysItems", along these lines:

Dim dbD as DAO.Database
Dim rsR as DAO.Recordset

set dbD = CurrentDB()
set rsR = dbD.OpenRecordset("qryTodaysItems")
If rsr.RecordCount=0 Then
        'no records found
Else
        Do
                'send one item
                rsr.MoveNext
        Loop Until rsr.EOF
End If


Quote:

>Woulld you please tell me how to convert the attached excel visual
>basic file so that it would access data in an "access" table database
>and develop a query that would look for the the current date.
>What the present database does is to search column A for the current
>date. When it finds the current date it it then moves one column to
>the right to picks up the subject;then  three columns to the right to
>pick up the address; and four columns to the right for the subject.
>Then it e-mails the message.  This macro in excel visual basic works
>perfectly. My problem is that I want to put the data into access query
>and have the query find the date. Can you help me.  The visual basic
>access macro is below.

>Sub Finddate2()

>    Dim DateRange As Range
>    Dim c As Range

>     'set outlook object references
>     Worksheets(1).Activate
>     Columns("A:A").Select
>    Set DateRange = Application.Intersect(Range("a1").EntireColumn, _
>        ActiveSheet.UsedRange)

>     For Each c In DateRange
>         If IsDate(c.Value) Then
>            If CLng(CDate(c.Value)) = CLng(Date) Then
>                 Call NextCharacter(c)
>             End If
>         End If
>     Next c

> End Sub

>Sub NextCharacter(mycell As Range)
>Set ol = CreateObject("Outlook.Application")
>Set MailSendItem = ol.CreateItem(olMailItem)
>Set olns = ol.GetNamespace("Mapi")

>    Dim mySubj As String
>    Dim myAddr As String
>    Dim myBody As String

>     mySubj = mycell.Offset(0, 1).Value
>     myAddr = mycell.Offset(0, 3).Value
>     myBody = mycell.Offset(0, 4).Value

>'     MsgBox mySubj & "--" & myBody & "--" & myAddr

>   With MailSendItem
>     .Subject = mySubj
>     .To = myAddr
>     .Body = myBody
>     .Send
>    End With
>End Sub

John Nurick [Access MVP]

Please do not respond by email, but to the newgroup.



Sat, 17 Jul 2004 15:20:29 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. WANTED: Excel-Visual Basic for Applications Wanted: Excel-Visual Basic for applications Programmer - Ottawa

2. Converting Excel File To Access File through Visual Basic 6.0

3. how do i Access visual foxpro database from visual basic

4. Newcomer to Access Basic/Visual Basic

5. Access Basic (VBA) vs Visual Basic

6. change from access basic into visual basic

7. giving users restrictions on a windows NT network with visual basic code or access basic code

8. giving users restrictions on a windows NT network with visual basic code or access basic code

9. Access different excel files in Visual Basic

10. VBAXL8.HLP file (Visual Basic for Excel Help file)


 
Powered by phpBB® Forum Software © phpBB Group