wdfilesaveas works the first time but not the second time

[Cindy]

I have an Access application which I use VBA to open a
Word document and insert data from the Access forms and
data from the db. The code then opens an Excel
spreadsheet and inserts data into it.

My problem:

The first time I start Access and make selections via the
forms. I click the command button to execute the code.
It inserts the data into the Word document, it then makes
the document Visible and Activates it. Once the Word
document is visible to the screen, the Save As dialog box
opens. It waits for a response on where to save the Word
document. Once the document is saved, the Excel
spreadsheet is made visible to the screen and the Save As
dialog box opens.

This works great the first time.

If you then make other selections on the Access forms and
click the command button to execute the code, it inserts
the new data into the Word document opens it and then
proceeds to insert data into the Excel spreadsheet and
then the Save As dialog box opens for the Excel
spreadsheet. I have stepped through the code for the Save
As dialog box for the Word document, but it will not
execute it. I have to shut down Access and reopen it for
the code to run correctly.

The Word document code which opens up the Save As dialog
box looks like this:

vDirLocation = "C:\Test\" 'will eventually pull from the db

vFileLocation = vContractID & " " & vOrderID & " " &
vCoAbbrev & " SubContract.doc"

With Dialogs(wdDialogFileSaveAs)
.Name = vDirLocation & vFileLocation
.Show
End With

The Excel spreadsheet code which opens up the Save As
dialog box looks like this:

With xlApp
.Visible = True
.Dialogs(xlDialogSaveAs).Show (vContractID & " " &
vOrderID & " " & vCoAbbrev & " Task Order" & ".xls")
End With

I am bewildered at this behavior. Also, how can I change
the directory in the Excel Save As dialog box.

Any help will be greatly appreciated.

Cindy

 

[Jean-Guy Marcil]

Cindy was telling us:
Cindy nous racontait que :

I have an Access application which I use VBA to open a
Word document and insert data from the Access forms and
data from the db. The code then opens an Excel
spreadsheet and inserts data into it.

My problem:

The first time I start Access and make selections via the
forms. I click the command button to execute the code.
It inserts the data into the Word document, it then makes
the document Visible and Activates it. Once the Word
document is visible to the screen, the Save As dialog box
opens. It waits for a response on where to save the Word
document. Once the document is saved, the Excel
spreadsheet is made visible to the screen and the Save As
dialog box opens.

This works great the first time.

If you then make other selections on the Access forms and
click the command button to execute the code, it inserts
the new data into the Word document opens it and then
proceeds to insert data into the Excel spreadsheet and
then the Save As dialog box opens for the Excel
spreadsheet. I have stepped through the code for the Save
As dialog box for the Word document, but it will not
execute it. I have to shut down Access and reopen it for
the code to run correctly.

The Word document code which opens up the Save As dialog
box looks like this:

vDirLocation = "C:\Test\" 'will eventually pull from the db

vFileLocation = vContractID & " " & vOrderID & " " &
vCoAbbrev & " SubContract.doc"

With Dialogs(wdDialogFileSaveAs)
.Name = vDirLocation & vFileLocation
.Show
End With

The Excel spreadsheet code which opens up the Save As
dialog box looks like this:

With xlApp
.Visible = True
.Dialogs(xlDialogSaveAs).Show (vContractID & " " &
vOrderID & " " & vCoAbbrev & " Task Order" & ".xls")
End With

I am bewildered at this behavior. Also, how can I change
the directory in the Excel Save As dialog box.

It may have to do with this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;189618

In particular, I notice that you do not qualify "Dialogs" with the Word
procedure, but you do with the Excel one (xlApp.Dialogs...).
You may have to qualify the Word Dialogs call.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Cindy]

I did not show qualifying "Dialogs" with the Word
procedure in what I posted but I did qualify it earlier in
the code.

Thanks for the thought.

Cindy

 

[Guest]

New Discovery

As I keep testing and trying different things, I've
noticed that when the first Word document is opened and
saved, if I leave the document active and not close it,
the next time I run my code a new Word document opens and
the Save As dialog box opens. It's only when I close the
Word document and then run the code that the new Word
document appears and the Save As dialog box does not.

Cindy