App.Path in Word ?

[jandrade15]

Hello all-

Im running a macro in Word. It runs when you open the document and its a mail merge doc. I need it to find the db in the folder where the form is located...(App.Path in VB) I know its Application.Path in Word but I paced that in and im getting syntax error among others my code is below... can anyone help? Thanks!

Private Sub Document_Open()
CommandBars("Mail Merge").Visible = True
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        [COLOR=Red]" & Application.Path & "\Tracking Data.mdb", ConfirmConversions[/COLOR]:= _
        False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data [COLOR=Red]Source=" & Application.Path & ";Mode=Read;Extended Properties="""";Jet [/COLOR] OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engin" _
        , SQLStatement:="SELECT * FROM `FarmingTemp`", SQLStatement1:="", SubType _
        :=wdMergeSubTypeAccess
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub

[RobDog888]

I just realized that Application.Path is the path to Word and not the document path.
Use ActiveDocument.Path to get the directory where the document resides

Another variation: Word.Documents(1).Path

[jandrade15]

Ok.....Can u tell me where I would put the ActiveDocument.Path? I tried replacing it with the Application.Path and it didnt work..... there seems to be two instance where it needs the directory of the db ...once in the mail merge sequence and another in the sql ....... How could I structure this? Thanks again!

[RobDog888]

Try placing the Document(1).Path into a variable at the beginning of your procedure. Then replace the application.path with the var.

[RobDog888]

Can you re-paste in your procedure again, but when you do check the "Disable smilies in this post"?
Seems we have an issue with the double quotes and line continuations.

[jandrade15]

sure! Thanks!

Private Sub Document_Open()
CommandBars("Mail Merge").Visible = True
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Program Files\Tracking Data\Tracking Data.mdb", ConfirmConversions:= _
        False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Program Files\Tracking Data\Tracking Data.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engin" _
        , SQLStatement:="SELECT * FROM `FarmingTemp`", SQLStatement1:="", SubType _
        :=wdMergeSubTypeAccess
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub

[jandrade15]

The code above is the working code. Its hardcoded however. I want it to find the db in the app folder of the program......

[RobDog888]

I get an error that the string is longer then 255 characters. Do you get this error?

[jandrade15]

the one that was hardcoded ...(C:Programfiles etc....) doesnt do that.... But when i put the ActiveDocument.Path it will give me that error..... Should I upload the Word form and db?

[RobDog888]

Nah, its ok. I'm almost done. I took out some of the defaults that aren't needed.

[RobDog888]

Try this.

Private Sub Document_Open()
    Dim sPath As String
    CommandBars("Mail Merge").Visible = True
    sPath = ActiveDocument.Path & "\Tracking Data.mdb"
    MsgBox sPath
    ActiveDocument.MailMerge.OpenDataSource Name:=sPath, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password='';Data Source=" & sPath & _
    ";Mode=Read;", SQLStatement:="SELECT * FROM 'FarmingTemp'", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub

[jandrade15]

ok on that it gives me a message box telling me where the db is located. but doesnt continue the macro. ill attach the word form and the db so you can see what the default macro does..... it basically runs the mailmerge when you open the word template. like i said before its hardcoded to the c drive etc..... And i want it to find the db where ever it is located.....

What you have is on the right spot just want to automated so the box doesnt show and continues to run the macro... here is all the stuff...

Thanks a million for the help!

[RobDog888]

I put the message box in so you can verify the path. Is the path correct?
I'll take a look at you doc

[jandrade15]

yeah the path is correct! were right on just if the query or macro can continue were good!

[RobDog888]

Just comment out the msgbox and see if that does it.

[jandrade15]

When I take out the comment box it takes me to select the connection or db then the table etc.... the original macro I had took care of this it did everything automatically.......Did u get a chance to view the docs I uploaded?
Thanks again for you help!

[RobDog888]

Yes, it was doing that for mee too. I have it at home so I will take another look at it tonight.

[jandrade15]

Thanks again For your Help!!