Solved

Rewrite Word Macro in Visual Basic

Posted on 2001-08-02
14
366 Views
Last Modified: 2013-11-25
I'm using Visual Basic 6 against an SQL Server 7 database.
One form contains an OLE object which when double-clicked, will open a word document (Microsoft Word 97) of a Bill.

I had written a macro in a Word template that did what I wanted but I'd like to write the logic on the VB side.

Basically, the idea is to allow the Users who don't have permission to edit the document ( ReadOnly = false), to save a copy of this file to a directory on their D:\ drive.  So the common dialog defaults to their D:\ drive.  If they do have permission, the SaveAs common dialog box will allow them to saveas to the current directory, on the network.

Can this be done?
The macro ran fine and did what I wanted but some things in VBA don't transfer over to VB completely.

For example, the error number 4172 was skipped when I stepped thru the code.
On the VB side, I'm checking a Boolean value for readonly:
If the value is false, allow the users to SaveAs to a newly created D: directory on their computer.
If the directory doesn't exist, create it and save the file.
If readonly is true, when the user clicks SaveAs from Word, the common dialog opens to the current directory and saves the file there, with changes.

Here's my macro:

Sub FileSaveAs()
On Error GoTo Save_Err
   
    BAMDir$ = "D:\BAMS"
    CurrentFile$ = ActiveDocument.Name
    Application.ChangeFileOpenDirectory BAMDir$
   
    ActiveDocument.SaveAs FileName:=CurrentFile$
    MsgBox "File Saved."
   
Save_End:
Exit Sub
   
Save_Err:
    If Err.Number = 4172 Then
    If MsgBox("The Directory " & BAMDir$ _
        & " Is Not On This System." & vbCrLf _
        & "We Suggest You Use This " _
        & "Directory For Your BAM Word Files." & vbCrLf _
        & "Would You Like To Create It Now? ", _
        vbYesNo, "Bill Tracking") = vbYes Then
           
                MkDir BAMDir$
               
                Resume
        Else
            Resume Save_End
        End If
    End If

End Sub

And here's what I have so far in VB:
(Error 4172 is not a valid error in VB, I guess)

Private Sub BamMSWord_Click()
Dim BAMDir As String
Dim CurrentFile As String

On Error GoTo Save_Err:

    Set MyWord = New Application
    MyWord.Documents.Open BamFileName, , BamReadOnly
    CurrentFile = BamFileName
   
    BAMDir = "D:\BAMS"
    If BamReadOnly Then
            MyWord.Documents.Open BamFileName, , BamReadOnly
    Else
        MyWord.Visible = True
        MyWord.Width = 600
        MyWord.Height = 440
        MyWord.Left = 0
        MyWord.Top = 0
   
        MyWord.ChangeFileOpenDirectory BAMDir
        MyWord.ActiveDocument.SaveAs CurrentFile
        MsgBox "File Saved."
   
    End If
Save_End:
Exit Sub
   
Save_Err:
    If Err.Number = 4172 Then
    If MsgBox("The Directory " & BAMDir _
        & " Is Not On This System." & vbCrLf _
        & "We Suggest That You Use This" _
        & "Directory For Your BAM Word Files." & vbCrLf _
        & "Would You Like To Create It Now?", _
        vbYesNo, "Bill Tracking") = vbYes Then
           
            MkDir BAMDir
            Resume
    Else
        Resume Save_End
    End If
    End If
   
End Sub

Is it possible to control the MS Word toolbars from VB like this?
Thanks in Advance
John
0
Comment
Question by:jtrapat1
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 6346393
Since macro code IS Visual Basic, you can drag it to a VB project and it will work without change...as long as all necessary references are includes.  The major one you need is the Word engine:  go to Project|References... then select "Microsoft Word 9.0 Object Library" (make sure it's checked), then [OK].

This pulls in all Word libraries then allows you to process the macro code on the loaded document...which may also need to be pulled into VB.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6346400
Sorry...I guess I didn't read far enough.  It appears you have most of that already.

To control things in Word, you may have to use VB to perform an AppActivate, then SendKeys.  The Word toolbars should be controlled through their corresponding keystrokes.
0
 

Author Comment

by:jtrapat1
ID: 6346438
rspahitz,

Thanks for the response.
Listen, I probably gave too much info; but I'll try what you suggested.

Basically, I'd like to change the File>SaveAs default directory in a Word Document, from Visual Basic, based on a boolean value, if possible.

Thanks
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6346474
So you want to change the equivalent of the Tools|Options|[File Locations]/"Documents" to D:\ ?

There may be a control for that through the Word object library, but I've never tried so I wouldn't know where to search for it.

Meanwhile, you could always SendKeys "%TO+^{Tab}%MD:\~~"

Which will send the following:

%T   - Alt-T (for Tools)
O    - O (for options)
+^{Tab} - for shift-tab to try to get to the File Locations tab
%M   - Alt-M (for Modify)
D:\  - desired path
~    - enter to accept this nwe location
~    - enter to close the options window

I haven't tried it, so I don't guarantee it, but it's a start.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6346478
So you want to change the equivalent of the Tools|Options|[File Locations]/"Documents" to D:\ ?

There may be a control for that through the Word object library, but I've never tried so I wouldn't know where to search for it.

Meanwhile, you could always SendKeys "%TO+^{Tab}%MD:\~~"

Which will send the following:

%T   - Alt-T (for Tools)
O    - O (for options)
+^{Tab} - for shift-tab to try to get to the File Locations tab
%M   - Alt-M (for Modify)
D:\  - desired path
~    - enter to accept this nwe location
~    - enter to close the options window

I haven't tried it, so I don't guarantee it, but it's worth a try.
0
 
LVL 1

Expert Comment

by:superchook
ID: 6347169
In later versions of word, the default documentpath is in the registry - you may like to change that key - to make life a  lot easier.

0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6347713
If yoy need to change the default directory...

Options.DefaultFilePath(Path:=wdDocumentsPath) = "E:\documents"
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:nigelrowe
ID: 6347726
Whoops, I've just noticed you have Word97, the above works for Word 2000. But I'm at work at the moment, I have Word 97 at home and I don't know if this will work in 97. Try anyway, you never know....
0
 

Author Comment

by:jtrapat1
ID: 6349150
Nigel,
Thanks for the help.
I tried what you suggested and it seems I'm going in the right direction.

However, I'm getting an error where I set the reference for the FileSaveAs option in the dialog box:
I declare it as
    Dim dlgFileSaveAs As Dialog
And when I set a reference to it, it is Nothing and errors out on this line:
    Set dlgFileSaveAs = Dialogs(wdDialogFileSaveAs)

Is there some library I need to add for this object?
When I step thru the code, I can change to the default directory correctly, but I don't know how to show the dialog to change the FileSaveAs option.

Private Sub BamMSWord_Click()
On Error GoTo Save_Err:

Dim BAMDir As String
Dim dlgFileSaveAs As Dialog
Dim lngResult As Long
Dim strOldPath As String

Set MyWord = New Application

If Not BamReadOnly Then
    MyWord.Documents.Open BamFileName, , BamReadOnly
    BAMDir = "D:\BAMS"
   
    MyWord.Visible = True
    MyWord.Width = 600
    MyWord.Height = 440
    MyWord.Left = 0
    MyWord.Top = 0
   
    strOldPath = Options.DefaultFilePath(Path:=wdDocumentsPath)
    Options.DefaultFilePath(Path:=wdDocumentsPath) = BAMDir
    Set dlgFileSaveAs = Dialogs(wdDialogFileSaveAs)
   
    With dlgFileSaveAs
        lngResult = .Display
    End With

    If lngResult <> vbCancel And StrComp(CurDir$, BAMDir) = 0 Then
        dlgFileSaveAs.Execute
    End If

    Options.DefaultFilePath(wdDocumentsPath) = strOldPath
           
Else
       
    MyWord.Documents.Open BamFileName

    MyWord.Visible = True
    MyWord.Width = 600
    MyWord.Height = 440
    MyWord.Left = 0
    MyWord.Top = 0
           
End If
   
Save_End:
Exit Sub
   
Save_Err:
    If Err.Number = 4172 Then
    If MsgBox("The Directory " & BAMDir _
        & " Is Not On This System." & vbCrLf _
        & "We Suggest That You Use This" _
        & "Directory For Your BAM Word Files." & vbCrLf _
        & "Would You Like To Create It Now?", _
        vbYesNo, "Bill Tracking") = vbYes Then
           
            MkDir BAMDir
            Resume
    Else
        Resume Save_End
    End If
    End If
   
End Sub

Thanks
John

0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6349227
I always use the MS CommonDialog control, look in the Project.Components and add to the project. Add the control to your project

Now all you have to do is(another simplified example)...

Private Sub Command1_Click()
    mySaveAs.Filter = "MS Word (*.doc)|*.doc"
    mySaveAs.DialogTitle = "Navigate to the directory"
    mySaveAs.ShowSave
End Sub
0
 

Author Comment

by:jtrapat1
ID: 6349298
Nigel,
Thanks.
In the above code, I've already opened MSWord from the VB app and am trying to change the default SaveAs directory.

If I use your common dialog example, can I still control (with VB code) the common dialog that pops up from Word?

Will this FileSaveAs routine replace the one in Word?

John
0
 

Author Comment

by:jtrapat1
ID: 6349435
Nigel,
Let me ask you this:
Because of the way my app is written, is it possible for me to pass a parameter from a VB program to an MSWord macro (written as a template to control the default directories in the File>SaveAs prompts).

The thing is, I need to check a value on the VB side to see if a user has access to modify the document:
If so, the word document will open as a copy to be modified, and when he wishes to SaveAs, the common dialog will open in the current directory, and the document, will be saved into that directory.

If the user does not have access to modify the data, but wishes to save a copy of the document, the document will open as Read-Only; and when the user clicks File>SaveAs, the common dialog will open and create(if it doesn't already exist) a directory on the user's D:\ drive, and save the file there.

I'd rather not write two templates.

There's probaby as easier way to to this, and I'm trying to explain it the best I can, so I apologize if it's not clear.

Thanks
John
0
 
LVL 3

Accepted Solution

by:
nigelrowe earned 200 total points
ID: 6355147
Hi John, just got back to work. You could use the MS Common dialog to prompt the user for a default directory to save to, then set the Word saveas default directory using something like...

Private Sub Command1_Click()
Dim the_path As String
    GetDefaultDirectory
    the_path = Left(CommonDialog1.FileName, Len(CommonDialog1.FileName) - Len(CommonDialog1.FileTitle) - 1)
    WordApp.Options.DefaultFilePath(Path:=wdDocumentsPath) = the_path
End Sub

Private Sub GetDefaultDirectory()
    CommonDialog1.ShowSave
End Sub

WordApp.Options.DefaultFilePath(Path:=wdDocumentsPath) = myCommDialog.Path
0
 

Author Comment

by:jtrapat1
ID: 6357090
Nigel,
Thanks for the help.

I was thinking of creating two word templates -
Both of them will have macros underneath which will prompt the user for the correct directory.
One that will be read-only in which the user will be prompted to SaveAs to his own D:\ drive
and another one in which it will be editable and saved into the current directory, along with any changes.

All of our Word documents are saved in a folder on the server.

My question is:
Do you know if there is any way to open word documents based on different templates?

Thanks
John
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now