Improve company productivity with a Business Account.Sign Up

x
?
Solved

Macro upgrade from word 2003 to word 2007

Posted on 2010-11-30
10
Medium Priority
?
352 Views
Last Modified: 2012-05-10
I have a customer who is upgrading from word 2003 to Word 2007.  They have a macro that writes a unique id and initials etc into the footer of the word document.

I can't seem to get this working.  I'm not a developer and ther person who wrote it orginally is no longer available.

i'm getting the following error message.

user defined type not defined.  Resolved this by adding in the reference "Microsoft DAO 3.51 Object Library.

Now getting the following error:

Complile error:  Invalid use of New keyword.

The following section is highlighted:

Function GetID() As String
On Error GoTo eh
    Dim cn As Connection, rs As Recordset
    Set cn = New Connection
    cn.Provider = "microsoft.jet.oledb.4.0"
    cn.ConnectionString = "\\fileserver\counter$\id.mdb"
    cn.Open
    Set rs = New Recordset
    rs.Open "SELECT * FROM idTable", cn, adOpenStatic, adLockOptimistic
    rs.AddNew
    rs.Update
    rs.MoveLast
    GetID = rs("ID")
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    Exit Function
eh:
    MsgBox "An error occured " & Err.Description, vbInformation
End Function
0
Comment
Question by:mccannit
10 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34237647
The code you posted has reference to an access database which is remotely connected into your Word application. Ensure that the path of the database is correctly identified.

Sincerely,
Ed
0
 
LVL 1

Author Comment

by:mccannit
ID: 34237714
The path to the access database is fine.

Set cn = New Connection is the line that gets highlighted.    
0
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 2000 total points
ID: 34237772
Try to use ADO reference rather than DAO.

Sincerely,
Ed
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
LVL 77

Expert Comment

by:GrahamSkan
ID: 34237777
It looks as if it is written for ADO, not DAO. Try a reference to the Microsoft Active Data Objects library instead.
0
 
LVL 1

Author Comment

by:mccannit
ID: 34238076
that seemed to do the trick however I'm getting another error message.

I had to remove the "on error goto" to actually see the error message.

Run-time error '6':

Overflow.

I've included the actual macro "new file".  It's highlighting

strFooterText = CStr(Format(CInt(strId), "0000000")) _
                & "/" & strUser & "/" & strRequestee & "/" & strCaseRef & "/" & strDate
Sub NewFile()
    Dim docType As Integer, strRequestee As String
    Dim strCaseRef As String, strFileName As String
    Dim strId As String, strFooterText As String
    Dim strUser As String, strDate As String
    Application.ScreenUpdating = False
    docType = MsgBox("Is this a typing file, if so click the yes button, " _
    & vbCrLf & "if not click the no button", vbYesNo + vbQuestion, "Typing document?")
    If docType = vbYes Then
        frmLocation.Show vbModal
        'MsgBox strLocation
        strRequestee = InputBox("Enter the initials of the author", "Author Initials")
        If strRequestee = "" Then
            MsgBox "You Cancelled", vbInformation
        Else
            strCaseRef = InputBox("Enter the case reference", "Case reference", "Case Reference")
            If strCaseRef = "" Then
                MsgBox "You Cancelled", vbInformation
            Else
                strUser = Application.UserInitials
                strDate = CStr(Format(Date, "dd/mm/yy"))
                frmTemplate.Show vbModal
                Documents.Add strTemplate, False, 0, True
                strFileName = strLocation
                strId = GetID()
                'MsgBox strId
                strFooterText = CStr(Format(CInt(strId), "0000000")) _
                & "/" & strUser & "/" & strRequestee & "/" & strCaseRef & "/" & strDate
                'MsgBox strFooterText
                strFileName = strId & "_" & strRequestee & "_" & strCaseRef & "_" & strUser
                ActiveWindow.ActivePane.View.Type = wdPrintView
                ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
                Selection.TypeText strFooterText
                Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
                Selection.WholeStory
                With Selection.Font
                    .Name = "Arial"
                    .Size = 8
                End With
                ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
                Application.ChangeFileOpenDirectory strLocation
                ActiveDocument.SaveAs strFileName
            End If
        End If
    Else
        'look up template folder
        frmBasicTemplates.Show vbModal
        Documents.Add strTemplate, False, 0, True
    End If
    Application.ScreenUpdating = True
    Exit Sub
    ActiveDocument.Close False
End Sub

Open in new window

0
 

Expert Comment

by:RomAniA12
ID: 34238133
You have a bunch of error's in your code that renders it useless..
0
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 2000 total points
ID: 34238165
I suspect that your connection to the DB is not properly established. Check it out.

Try to change this line in 25: strId = GetID() into
strId = 1

Run the macro and see what happens.

Sincerely,
Ed
0
 
LVL 1

Author Comment

by:mccannit
ID: 34238169
ok.  the code worded fine in word 2003.  Any suggestions on how to make this work on Word 2007?
0
 
LVL 1

Author Comment

by:mccannit
ID: 34238249
That worked a treat MINDSUPERB.  Thank you very much for all your help.
0
 
LVL 1

Author Closing Comment

by:mccannit
ID: 34238258
Thanks.
0

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

606 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