Opening a Word Doc from Access with File Name Stored in a Table 
Author Message
 Opening a Word Doc from Access with File Name Stored in a Table

I hope this is the right ng to be asking this question. I have a database in
which a user can decide to send out a certain type of letter. When they do
this a query runs which determines who gets sent the letter. This query is
actually a make table query as all (around 40) of the letters have the same
data source. I would like to completely automate this process by storing all
of the Word file (letters) locations in an access table and have Access open
up the correct file and run the merge. I can get Word to open up but I keep
getting error messages saying that it either can't find the file or that the
file does not exist. I'm assuming that my problem is one of syntax. I'm
using a DLookup function to find the location of the file as indicated
below.

stMergeFile = DLookup("[File Location]", "App Letters to Send", "[Letter
ID]=" & I)

stMergeFile has been dimensioned as a string.

I'm using this line of code to try to open up the document:

WordObj.Documents.Open (stMergeDir)

Obviously this is wrong. What am I missing here? Is there a better way to do
what I want to do?

Also, there are times when a user would like to run multiple merges at the
same time. I'm assuming that this is possible but I'm not sure what the best
way to do this would be.

Thanks.

Brian



Tue, 10 Sep 2002 03:00:00 GMT  
 Opening a Word Doc from Access with File Name Stored in a Table
I have a small database that does a lot, if not all of what you are asking
about.  It has a main form that allows the user to enter a date to place on
the letter, and a combo box that listing the different letters that the can
merge in word.  The letters are stored in a table along with information
about what table/query to use as datasource.  They pick the letter from the
list, click a button, get a prompt to either print immediately or do the
merge and preview the results.  The whole process is automated and very
simple for the user.

This setup also allows you to have multiple datasources(linked tables).  You
don't even have the {*filter*} problem of having a second occurance of Access
appearing because the data for the merge is exported to a text file and the
datasource for the main document is changed to use the text file.  This
seems to be a lot faster anyhow.

If you would like a copy of this mdb feel free to contact me via email, just
remove NOSPAM.

HTH
Terry



Tue, 10 Sep 2002 03:00:00 GMT  
 Opening a Word Doc from Access with File Name Stored in a Table
So do you have the complete paths to the Word docs stored in a table? Are
all these docs mail merge enabled?  If yes, then one alternative could be to
just pass the filename to ShellExecute APi which will open up Word and pass
the file to it automatically.

--
Dev Ashish     (http://www.mvps.org/access)


: I hope this is the right ng to be asking this question. I have a database
in
: which a user can decide to send out a certain type of letter. When they do
: this a query runs which determines who gets sent the letter. This query is
: actually a make table query as all (around 40) of the letters have the
same
: data source. I would like to completely automate this process by storing
all
: of the Word file (letters) locations in an access table and have Access
open
: up the correct file and run the merge. I can get Word to open up but I
keep
: getting error messages saying that it either can't find the file or that
the
: file does not exist. I'm assuming that my problem is one of syntax. I'm
: using a DLookup function to find the location of the file as indicated
: below.
:
: stMergeFile = DLookup("[File Location]", "App Letters to Send", "[Letter
: ID]=" & I)
:
: stMergeFile has been dimensioned as a string.
:
: I'm using this line of code to try to open up the document:
:
: WordObj.Documents.Open (stMergeDir)
:
: Obviously this is wrong. What am I missing here? Is there a better way to
do
: what I want to do?
:
: Also, there are times when a user would like to run multiple merges at the
: same time. I'm assuming that this is possible but I'm not sure what the
best
: way to do this would be.
:
: Thanks.
:
: Brian
:
:



Tue, 10 Sep 2002 03:00:00 GMT  
 Opening a Word Doc from Access with File Name Stored in a Table

Quote:

>So do you have the complete paths to the Word docs stored in a table? Are
>all these docs mail merge enabled?  If yes, then one alternative could be
to
>just pass the filename to ShellExecute APi which will open up Word and pass
>the file to it automatically.

>--

The documents are extracted from the table and saved into the current
directory, as is the data source text file.
The Word docs are mail merge enabled but their "header information" is
removed before inserting the document into the table by removing the data
source used to layout the document.  This saves me the trouble having the
document try to find a mdb datasource that causes all the dde stuff on the
status bar of Word.

This all requires a reference to Word.  The code to extract the document
from the table was modified from an article in Advisor Access/Office VB
July, 1999, Titled Generate Database Reports with Word.  When you click on
the button to do the merge, MergeIt is run, see below. It starts up the
form, designed in the article, hidden.  Exctracts the select document and
saves it into the current directory as "tmp.doc".   Exports the data, using
TransferText to "tmpsrc.txt".  For SQL strings the results are appended to a
temp table then the temp table is transferred.  The Word is started, opens
tmp.doc, changes its datasource to "tmpsrc.txt", then does the merge and
closes tmp.doc to the user only gets the results.  The tmp files are
immediatly deleted.  Using "tmpsrc.txt" eliminates all the dde stuff word
wants to do when opening the merge document.

Here is the code for doing the merge itself:

Declare Function FindWindow Lib "User32" _
                                Alias "FindWindowA" _
                                (ByVal lpClassName As String, _
                                ByVal lpCaption As String) As Integer

 Function CurrentDbPath() As String
    Dim strWork As String
    Dim x As Integer
    Dim bSlashFound As Boolean

    'This function pulls the path out of the current database name
    'it does this by starting at the last character in the name, and
    'working backwards, look at every character until it comes upon
    'a backslash.  Once the backslash is found, then everything to the left
    'is the path of the current database.

    strWork = CurrentDb.Name
    x = Len(strWork)

    While Not bSlashFound And x > 0
        If Mid(strWork, x, 1) = "\" Then
            bSlashFound = True
        Else
            x = x - 1
        End If
    Wend

    If x <> 0 Then
        CurrentDbPath = Left(strWork, x)
    End If

End Function

Function MergeIt(sSourceType As String, _
                  sSourceName As String, _
                  sDocumentNumber As String, _
                  sDocumentTable As String, _
                  Optional sQuery As String = "", _
                  Optional sLclFileName As String = "", _
                  Optional sSQL As String = "" _
                  ) As Boolean

    'sSourceType     = State whether the source is a Table, Query, or SQL _
                       If it is an SQL, then a Local file (sLclFileName)
name _
                       is needed as well as an SQL string statement(sSQL).
    'sSourceName     = Name of the source table or query even if sSourceType
is _
                       an SQL statement.
    'sDocumentNumber = Document ID to use
    'sDocumentTable  = Table or Query Containing the document
    'sQuery          = If sQuery is specified then use a predefined query
that _
                       contains all the fields needed for the merge.  If not
_
                       specified then use sLclFileName.
    'sLclFileName    = Optional, used when sSource Type is SQL.  Since you
cannot _
                       TransferText an SQL statement, the records are placed
in an _
                       empty table locally.
    'sSQL            = Optional, used when sSource Type is SQL.  Contains
the _
                       condition statement of the SQL string.

    'MsgBox "In MergeIt"
    MergeIt = True
    On Error GoTo Error_Mergeit
    GoSub CheckIfWordIsAlreadyRunning
    If MergeIt Then
        Dim db As DATABASE
        Dim rst As Recordset

        Set db = CurrentDb

        GoSub DetermineIfRecordsToProcess
        If MergeIt Then
            GoSub CleanUpSourceTextFile
            If MergeIt Then
                GoSub ExportSourceRecords
                If MergeIt Then
                    GoSub ExtractWordDocument
                    If MergeIt Then
                        GoSub PerformMerge
                    End If
                End If
            End If
        Else
            MsgBox "No documents need to be printed for this selection.",
vbInformation + vbOKOnly, sAppName + " - No Records"
        End If
        Set db = Nothing
    End If

FunctionExit:
    Exit Function

Error_Mergeit:
    If Err.Number = 2501 Then 'Form opening was cancelled
        Resume Next
    Else
        MsgBox Err & ": " & Err.Description, , "Merge It"
        MergeIt = False
        Resume FunctionExit
    End If

CheckIfWordIsAlreadyRunning:
    Dim l_hndw As Long
    l_hndw = FindWindow("OpusApp", vbNullString)

    If l_hndw > 0 Then
        MsgBox ("Word is currently running." + _
                Chr(13) + Chr(13) + _
                "Please close MS Word before executing this option")
        MergeIt = False
    End If
    Return

DetermineIfRecordsToProcess:
    Select Case sSourceType
        Case "Table"
            Dim tdf As TableDef
            'Open the Table record source
            Set tdf = db.TableDefs(sSourceName)
            Set rst = tdf.OpenRecordset(dbOpenSnapshot)
        Case "Query"
            Dim qdf As QueryDef
            'Open the Query record source
            Set qdf = db.QueryDefs(sSourceName)
            Set rst = qdf.OpenRecordset(dbOpenSnapshot)
        Case "SQL"
            'Set the record set to an SQL statement
            Dim strSQL As String
            strSQL = "SELECT * FROM " + _
                        sSourceName + IIf(Len(Trim(sSQL)) > 0, " " + sSQL,
";")
            Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    End Select

    If rst.EOF And rst.BOF Then
        'No records in the record set
        rst.Close
        MergeIt = False
    End If
    Return

CleanUpSourceTextFile:
    If Dir(CurrentDbPath() + "tmpsrc.txt") <> "" Then
        Kill CurrentDbPath() + "tmpsrc.txt"
    End If
    Return

ExportSourceRecords:
    Select Case sSourceType
        Case "Table", "Query"
            DoCmd.TransferText acExportDelim, , sSourceName, CurrentDbPath()
+ "tmpsrc.txt", True
        Case "SQL"
            'Cannot TransferText an SQL Statement. So use an empty record
structure _
             and append source records to it then tranfer the table.
            Dim rst2 As Recordset
            Dim tdf2 As TableDef
            Dim fld As Field

            ' Clear temp table: sLclFileName
            db.Execute "Delete * From [" + sLclFileName + "];"
            Set tdf2 = db.TableDefs(sLclFileName)
            Set rst2 = tdf2.OpenRecordset(dbOpenDynaset)
            rst.MoveFirst
            'Dim ixcounter As Integer
            'ixcounter = 1
            Do Until rst.EOF
                'Debug.Print ixcounter
                rst2.AddNew
                On Error Resume Next
                For Each fld In rst.Fields
                    'Debug.Print fld.Name
                    rst2(fld.Name) = rst(fld.Name)
                Next fld
                On Error GoTo Error_Mergeit
                rst2.Update
                rst.MoveNext
                'ixcounter = ixcounter + 1
            Loop
            rst2.Close
            rst.Close

            'sQuery would contain either just the local table or other
tables used _
             with the local table to relate to each record that is part of
the _
             record set.  If sQuery is blank it will TransferText using the
_
             local table only.
            If Len(Trim(sQuery)) = 0 Then
                DoCmd.TransferText acExportDelim, , sLclFileName,
CurrentDbPath() + "tmpsrc.txt", True
            Else
                DoCmd.TransferText acExportDelim, , sQuery, CurrentDbPath()
+ "tmpsrc.txt", True
            End If
            'Debug.Print "done"
    End Select
    Return

ExtractWordDocument:
    Dim iMaxLength As Integer
    Dim iLength As Integer
    Dim sDocId As String

    sDocId = ""
    iMaxLength = 4
    iLength = Len(Trim(sDocumentNumber))

    If iLength > 0 And iLength <= iMaxLength Then
        sDocId = Trim(CStr(iMaxLength)) + _
                 Trim(CStr(iLength)) + _
                 Space(iMaxLength - iLength) + _
                 Trim(sDocumentNumber)
    Else
        Dim sErrMsg As String
        If iLength = 0 Then
            sErrMsg = "No document number was passed from the calling " + _
                       "procedure."
        Else
            sErrMsg = "The document number passed from the calling " + _
                       "procedure was too large(" + sDocumentNumber + ")."
        End If
        MsgBox sErrMsg + _
                "  Contact BATS at 783-6585.  This selection " + _
                "will be aborted." _
                , vbExclamation + vbOKOnly _
                , sAppName + " - Merge It Error"
        GoSub CleanUpSourceTextFile
        rst.Close
        MergeIt = False
    End If

    If MergeIt Then
        'Passing the information to frm_Lib_DocumentToWorkWith allows _
         that form to not be tied to any table, query, or SQL statement. _
         The form sets it record source based on what is passed to it. _
         Example: sPassing = "41   3tbl_TableName"

        Dim sPassing As String
        sPassing = sDocId + Trim(sDocumentTable)
        DoCmd.OpenForm "frm_Lib_DocumentToWorkWith", OpenArgs:=sPassing
    End If
    Return

PerformMerge:
    Dim ObjWord As Word.Document
    Dim iPrint As Integer

    iPrint = MsgBox("You can either print immediately, or " + _
...

read more »



Tue, 10 Sep 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Open word 97 doc with doc name as variable

2. Get the file name of an already open word doc

3. Printing multiple Word doc files without opening each in word

4. Importing files from a directory where the file names are stored in a table

5. BuiltInDocumentProperties from non-Word text files + testing if a DOC file is a Word file

6. Open a doc, grab a range without having the whole doc being tabled

7. How to pass parameter while openining ole embeded word doc from access table

8. Opening Word Doc from runtime Access

9. Opening Word doc from Access DB

10. using word to open an html file and save it as .doc

11. Insert RTF into open Word doc w/o temp files

12. Word doc/rtf files won't open from Explorer shortcuts

 

 
Powered by phpBB® Forum Software