Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bill-preparation Word macros.

    We're trying to duplicate several Word Perfect macros which can cumulate daily data records of services rendered, sort by name, and ultimately prepare a bill for services rendered. Any suggestions about this, or about where we would find WP macro equivalencies? For example, WP uses SUBSTDATA(C:FILEMay22001.WPD) at the end of each data record file allow each to cumulate with the next during a merge. Any equivalent instruction in Word97 VBA?

    Thanks for any suggestions you might offer.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bill-preparation Word macros.

    Hi,

    While it looks as if you're storing the data in Word documents, and parsing it there- would it be better to store the data in Access- even to build the whole application in Access? It appears from your brief explanation that you are really trying to build a database type application in Word, where Access might be much more suitable.

    The sample "North Winds" database in Access has an invoice function which may be close to what you want.

    Even from Word VBA, it's possible to build commands to access the Access database.

    If you can explain a little more about what you're trying to do, we might be able to offer a little more help.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bill-preparation Word macros.

    I wrote me a little Word97/VBA utility that pops up a GUI screen for Client/Project/Task drop-down combo boxes, Start- and End-time. Remembers data across system crashes, stores data in a comm-delimited billing file which can be used almost anywhere. Email you a sample if you like.

  4. #4
    New Lounger
    Join Date
    Jul 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bill-preparation Word macros.

    Thanks, I'm at AdamsR@infinet.com. Sounds like you're doing approximately what I'm trying to do.

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bill-preparation Word macros.

    Access would accomplish this, but having an extra program booted would, I suspect, slow Dragon Naturally Speaking to a crawl. I'm trying to do this with Word alone to avoid having Word Perfect booted to do accountings along with Word (which also slows Dragron).

    My particular hurdle at present is that if I collect name, service, time and date fields in a secondary file for today, and then do the same with a separate secondary file for tomorrow and another the next day, WordPerfect enables Secondary files to be linked to the following secondary file so that by specifying say, April 1, we can immediately merge generate a secondary file containing all records from then until now.

    The old WP billing system combines all secondary files for the period in question, allows you to sort records by name and then to print an invoice all by macro.

    Any thoughts about whether this might sensibly be done in VBA-Word alone, or should we stay with WP until machine RAM gets bigger (Dragon slows with competition for 256K which maxes out our machines).

    Thanks again for your thoughts, which I've already found to be very helpful.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bill-preparation Word macros.

    Hi,

    I've used Word for some elementary data storage, but I'd still be suggesting that you use a real database for storing once you've gathered the data from Word forms. You don't need to have an Access application open- you can manipulate the database from within Word.

    Here's a quick & dirty example of reading a database and outputting to Word. It uses ADO, which means you need to have a reference set to Microsoft ActiveX Data objects- or you could use a similar approach with DAO:

    <pre>Public strSQL As String
    Public rstRecordSet As ADODB.Recordset
    Public cnnConnection As ADODB.Connection

    Sub ReadDB()

    Call setConnection

    strSQL = "select name, team from users"
    With rstRecordSet
    .Open Source:=strSQL, ActiveConnection:=cnnConnection, & _
    CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly
    Do While Not .EOF
    j = j + 1
    ActiveDocument.Range.InsertAfter .Fields("name") _
    & vbTab & .Fields("team") & vbCrLf
    .MoveNext
    Loop
    .Close
    End With

    Call closeConnection

    End Sub
    Public Sub setConnection()

    Set cnnConnection = New ADODB.Connection
    With cnnConnection
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "d:chresmyDB.MDB"
    End With

    Set rstRecordSet = New ADODB.Recordset

    End Sub

    Public Sub closeConnection()
    cnnConnection.Close
    Set cnnConnection = Nothing
    Set rstRecordSet = Nothing
    End Sub
    </pre>


    And the skeleton of a routine to open documents in a directory:

    <pre>Dim strName As String
    Dim strPath As String
    Dim docChecked As Document

    strPath = "c:myDirectory"
    strName = Dir(strPath & "n*.*")
    Do While strName <> ""
    Set docChecked = Documents.Open(strPath & strName, _
    False, True, False, "", "", False, "", "", _
    wdOpenFormatAuto)
    ' Extract info from your document
    docChecked.Close savechanges:=False

    strName = Dir
    Loop
    </pre>


    Just some thoughts to help you get started.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bill-preparation Word macros.

    If you're interested in doing this in Excel, I have an Excel 97 workbook specifically designed for generating bills for time and materials billing, and keeping track of payments received for same. E-mail me if you're interested in this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •