CODEWITHSUNDEEP
vbams-access
divdeamor
divdeamor

Reputation: 99

Exporting MS access Report to MS Word using VBA

I am doing a simple vba script that looks for a file. If it exsits it deletes it. then it moves on to the DoCmd.OutputTo acOutputReport. Below is the an example of the actual code i am using. The problem i am running into is when i set the auto open to true. I want the file to open after it exports the report. However i am finding that for some reason the program stalls and will sit there for atleast 5 to 10 minutes then it will error out saying that the remote proceedure can not be completed. After the error the file opens anyway with no apparent errors... When i change the auto open to false. I am able to open the report in about 3 to 5 seconds later by manually opening it. Is there something i do not know or can not find that is causing this latancy issue?

Example code - Some things might be syntacticly wrong as i dont have the code infront of me. But it should still communicate what im doing.

Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim cnn As Access.Application

strFile = "C:Myfile.rtf"
If Len(Dir(strFile)) Then
Kill strFile
End If

'access connection stuff here

with cnn
     'dont display messages code
     'active connection to my database code
      DoCmd.OutputTo acOutputReport, strFile, acFormatRTF, OutputFile, True
      .Quit
End With

so again the issue i am having is that i can not seem to get the file to automatically open after access populates it with the report. I am simply trying to increase the preformance or find a work around if you know of any.

Upvotes: 2

Views: 16548

Answers (1)

grahamj42
grahamj42

Reputation: 2762

In an application which exports from Access 2007, I saved the export operation as an export specification and used :

CurrentProject.ImportExportSpecifications(NameOfSpecification).Execute False

I then used

Set AppWord = CreateObject(Class:="Word.Application") ' create an instance of Word

Set Doc = AppWord.Documents.Open(NameOfDocument)

AppWord.Visible = True                      ' instance is invisible by default

I tend to avoid DoCmd if there's a VBA way of doing it: VBA gives me more control.

Upvotes: 3

Related Questions