?
Solved

Using VS2005 to create an application to read a text file from the server and then mailmerge the data

Posted on 2007-07-26
2
Medium Priority
?
211 Views
Last Modified: 2010-04-30
I have written this using VS2005:




Option Strict Off
Option Explicit On



Imports System.IO
Imports System.Runtime.InteropServices

Imports Microsoft.Office.Interop.Word







Public Class Form1

    Dim UserID

    Private Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" _
       Alias "DeleteUrlCacheEntryA" _
      (ByVal lpszUrlName As String) As Long






    Sub DownloadFromWeb()
        '
        ' DownloadFromWeb Macro
        ' Macro created 3/16/2007 by Brian O'Gorman
        '
        Dim sSourceUrl As String
        Dim sLocalFile As String
        Dim myConn As New Odbc.OdbcConnection
        Dim myRS
        Dim myCmd
        Dim mySQL



        Dim connectionString = "DSN=lifenet;" + "UID=brianog;" + "PWD=boggle"
        myConn = New Odbc.OdbcConnection(connectionString)
        myConn.Open()


        mySQL = "SELECT fldID from tblpasswords WHERE fldname = " & Trim(Me.txtLoginName.Text)
        myRS.Source = mySQL
        myRS.ActiveConnection = myConn
        myRS.Open()
        UserID = myRS("fldID")

        myRS.Close()
        myConn.Close()



        'sSourceUrl = "http://63.134.211.113/TestText.csv"

        sSourceUrl = "http://63.134.211.113/MMSchoolsDat" & CStr(UserID)
        sLocalFile = "c:\Web Design\LIFEnet\MMSchoolsDat" & CStr(UserID) & ".txt"

        'Label1.Caption = sSourceUrl
        'Label2.Caption = sLocalFile

        'Attempt to delete any cached version of
        'the file. Since we're only interested in
        'nuking the file, the routine is called as
        'a sub. If the return value is requires
        '(calling as a function), DeleteUrlCacheEntry
        'returns 1 if successful, or 0 otherwise, e.g.
        '  If DeleteUrlCacheEntry(sourceUrl) = 1 Then
        '     Debug.Print "cached file found and deleted"
        '  Else
        '     Debug.Print "no cached file for " & sourceUrl
        '  End If
        'Note that the remote URL is passed as this is the
        'name the cached file is known by. This does NOT
        'delete the file from the remote server.
        Call DeleteUrlCacheEntry(sSourceUrl)

        Try
            My.Computer.Network.DownloadFile(sSourceUrl, sLocalFile, "", "", False, 10000, True)
        Catch ex As Exception
            MessageBox.Show("Unable to download data for Mailmerging" & vbCrLf & ex.Message)
        End Try

    End Sub


    Private Sub PrintLetters(ByVal OneOnly As Boolean)
        Dim strWordDocFile As System.String = Me.txtLetter.Text
        Dim WordDocFile As New System.IO.FileInfo(strWordDocFile)
        Dim Msg As String

        ' See if the file specified exists
        If (WordDocFile.Exists) Then

            DownloadFromWeb()
            Dim wordApplication As New Microsoft.Office.Interop.Word.Application
            Dim wordDoc As Microsoft.Office.Interop.Word.Document = wordApplication.Documents.Open("C:\Web Design\LIFEnet\SchoolsLetters\Secondary Schools.doc", , True, False)
            Dim datfile


            datfile = "C:\Web Design\LIFEnet\MMSchoolsDat" & CStr(UserID) & ".txt"

            wordDoc.MailMerge.OpenDataSource(datfile)
            wordDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToPrinter

            If OneOnly Then
                wordDoc.MailMerge.DataSource.FirstRecord = 1
                wordDoc.MailMerge.DataSource.LastRecord = 1
            End If




            wordDoc.MailMerge.Execute()



            wordDoc.Close(False)



            ' Quit Microsoft Word and release the object variable.
            wordApplication.Quit()
            wordApplication = Nothing

        Else
            ' Fill Message string
            Msg = "You have not selected any schools yet" & Chr(13)
            Msg = Msg & "This must first be done online by selecting School Letters on the Schools menu" & Chr(13)
            Msg = Msg & "When you have done this, re-run this program." & Chr(13)
            MsgBox(Msg, vbOK)

        End If
    End Sub

    Private Sub btnPrintOne_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrintOne.Click
        If Trim(Me.txtLoginName.Text) = "" Then
            MsgBox("Please enter your Login Name")
            Exit Sub
        End If
        If Trim(Me.txtLetter.Text) = "" Then
            MsgBox("Please select a letter")
            Exit Sub
        End If

        PrintLetters(True)
    End Sub

    Private Sub btnPrintAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrintAll.Click
        If Trim(Me.txtLoginName.Text) = "" Then
            MsgBox("Please enter your Login Name")
            Exit Sub
        End If
        If Trim(Me.txtLetter.Text) = "" Then
            MsgBox("Please select a letter")
            Exit Sub
        End If

        PrintLetters(False)

    End Sub






    Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click

    End Sub


    Private Sub Label3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label3.Click

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim o As New OpenFileDialog()

        o.Title = "Select Letter"
        o.Filter = "Text file|*.doc"
        o.InitialDirectory = "c:\Web Design\LIFEnet\SchoolsLetters\"
        If o.ShowDialog = DialogResult.OK Then
            If o.FileName <> "" Then
                Me.txtLetter.Text = o.FileName
            End If
        End If
        o.Dispose()
        o = Nothing

    End Sub
End Class




When I run the project using  Debug/Start Debugging, I get an error on the line myRS.Source = mySQL in the DownloadFromWeb function. The Help box tells me:

use the New keyword to create an object instance

Not sure of the syntax to correct this. I am new to this sort of coding.

Also, have written this to copy a text file from the server to the users local disk and then mailmerge the data with a Word file. Can you comment on whether this is a good method? (or should I put this in a separate question?)
0
Comment
Question by:bogorman
2 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 2000 total points
ID: 19577638
It looks like you are using a lot of vb6-coding ...
You need to replace this piece of code:
       Dim myConn As New Odbc.OdbcConnection
        Dim myRS
        Dim myCmd
        Dim mySQL



        Dim connectionString = "DSN=lifenet;" + "UID=brianog;" + "PWD=boggle"
        myConn = New Odbc.OdbcConnection(connectionString)
        myConn.Open()


        mySQL = "SELECT fldID from tblpasswords WHERE fldname = " & Trim(Me.txtLoginName.Text)
        myRS.Source = mySQL
        myRS.ActiveConnection = myConn
        myRS.Open()
        UserID = myRS("fldID")

        myRS.Close()
        myConn.Close()

INTO

Dim cn As New Odbc.OdbcConnection
Dim cm As New Odbc.OdbcCommand
cn.ConnectionString = "DSN=lifenet;" + "UID=brianog;" + "PWD=boggle"
cn.Open()
cm.Connection = cn
cm.CommandType = CommandType.Text
cm.CommandText = "SELECT fldID from tblpasswords WHERE fldname = " & Trim(Me.txtLoginName.Text)
UserID = cm.ExecuteScalar().ToString()

Into:
0
 

Author Comment

by:bogorman
ID: 19581282
Thanks, Dhaest. Works  fine now.
Have problem with the mailmerging bit but feel it is fair to ask this in a separate question.
Thanks for you help. Will assign the points.
Regards
Brian
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question