Problem with using exportasfixedformat from MS Access

[Richard.carroll]

Dear Visio Guy,

I am stumped.  I am invoking Visio from MS Access.

I am running VB code in reaction to a "click" event on an Access form. The code works fine until I get to the Visio section. In a nutshell, I am taking information from the user, using it to run a query, exporting that query to a spreadsheet, inserting a macro in that worksheet, creating another sheet from that, open a Visio file, which will use that latest spreadsheet (not coded yet) and creating a PDF from that Visio file. Since I am still creating this code, I am using my desktop as a file repository, but will change that later.

My problem is with the exportAsFixedFormat command. I am pretty sure that I have coded it correctly, but when my VB code hits that line, I get the following error.

Runtime Error '-2032465551 (86db0971)':
Access has been denied.

I must have something wrong with that line of code, but it escapes me. I even went to stand alone Visio, inserted this code in an event and it worked fine. In my program, you will see two other lines that use the "Saveas" command which I used for debugging to make sure I was not having user security problems. Those lines of code work fine.   I might be having "macro Security" issues, but if so, they are not evident.  The Visio trust center settings look correct to me, including the check box for trusting VBA code.

I would appreciate if someone could give me a hint where to go with this next.

My code follows:


Private Sub Command3_Click()
Dim Myresult As Boolean


' ==================== check that building is not null =======================
Myresult = IsNull(Building)
If Myresult = True Then
MsgBox "You must select a building"
GoTo End_of_Routine

End If

' ==================== check that building is not blank ====================
If Building = " " Then
MsgBox "You must select a building"
GoTo End_of_Routine

End If

If Building = "Ballston" Then
GoTo Check_Ballston_Floors
ElseIf Building = "Glebe" Then
GoTo Check_Glebe_Floors
MsgBox "Unknown Building " & Building
GoTo End_of_Routine

End If

Check_Ballston_Floors:
' ==================== check that we are only looking at Ballston 2-5 ====================
If ([Floor Number] > 1) And ([Floor Number] < 6) Then
GoTo Good_Parms
Else
MsgBox "Ballston Floor numbers must be 2 - 5"
GoTo End_of_Routine
End If

Check_Glebe_Floors:
' ==================== check that we are only looking at Glebe 4 - 11 ====================
If ([Floor Number] > 3) And ([Floor Number] < 12) Then
GoTo Good_Parms
Else
MsgBox "Glebe Floor numbers must be 4 - 11"
GoTo End_of_Routine
End If

Good_Parms:

' ==================== Run the query ====================
DoCmd.OpenQuery "Query Data", acViewNormal, acReadOnly

' ==================== create the spreadsheet ====================
DoCmd.TransferSpreadsheet acExport, 10, "Query Data", _
"D:\Documents and Settings\richard.carroll\Desktop\" & Building & " Floor " & [Floor Number] & ".xlsx", True

' ==================== Add an empty sheet for the macro to use ====================
DoCmd.TransferSpreadsheet acExport, 10, "Empty Query Data", _
"D:\Documents and Settings\richard.carroll\Desktop\" & Building & " Floor " & [Floor Number] & ".xlsx", True


Dim xlApp As Object ' object to hold Excel application
Dim xlWb As Object ' Object to hold Excel workbook
Dim xlModule As Object ' Object to hold Excel Macro for "module 1"
Dim sFile As String ' String to hold the working Excel file
Dim sMacro As String ' String to hold the Macro name "Consolidated Records" which is stored in "Module 1" of the working spreadsheet

Dim visapp As Object ' Object to hold Visio application
Dim docobj As Object ' Object to hold Visio document
Dim visfile As String ' String to hold the name of the input Visio file


sFile = "D:\Documents and Settings\richard.carroll\Desktop\" & Building & " Floor " & [Floor Number] & ".xlsx"
sMacro = "ConsolidateRecords"

' ==================== Invoke Excel ====================
Set xlApp = CreateObject("Excel.Application")

' ==================== Open my spreadsheet ====================
Set xlWb = xlApp.Workbooks.Open(sFile, True)

' ==================== Hide Excel ====================
xlApp.Visible = False

' ==================== Add the Macro to Spreadsheet ====================
Set xlModule = xlWb.vbproject.vbcomponents.Add(1)
xlApp.VBE.activevbproject.vbcomponents.Item("Module1").CodeModule.AddFromFile "D:\Documents and Settings\richard.carroll\Desktop\Consolidate_Macro .txt"

' ==================== Run the Macro ===================================
xlApp.Run sMacro

' ==================== Delete the Macro from Spreadsheet ====================
xlApp.VBE.activevbproject.vbcomponents.Remove vbcomponent:=xlApp.VBE.activevbproject.vbcomponent s.Item("Module1")

' ==================== Delete input worksheet (Query Data)leaving only newly created worksheet ====================
xlApp.DisplayAlerts = False
xlWb.worksheets(1).Delete

' ==================== Save the workshhet in the format that Visio expects ====================

xlWb.saveas "D:\Documents and Settings\richard.carroll\Desktop\" & Building & [Floor Number] & " data.xlsx"

' xlWb.Close (True)
xlApp.Quit ' ==================== Close Excel ====================
Set xlApp = Nothing


MsgBox "PDF for " & Building & " Floor " & [Floor Number] & " has been created" ' ==================== Tell the user ====================

Kill sFile ' ==================== Delete the working file ====================

' ==================== Build Drawing file name ====================
visfile = "D:\Documents and Settings\richard.carroll\Desktop\Glebe07 - test.vsd"

' ==================== Establish Visio as the application to open ====================
Set visapp = CreateObject("visio.application")

' ==================== open the drawing ====================
Set docobj = visapp.Documents.Open(visfile)
visapp.Visible = True

MsgBox docobj

' ==================== save as PDF ----------------------
  visapp.ActiveDocument.exportAsFixedFormat visFixedFormatPDF, "d:\Documents and Settings\richard.carroll\desktop\Glebe07 - test.pdf", _
  visDocExIntentScreen, visPrintCurrentPage
' visapp.ActiveDocument.saveas "d:\Documents and Settings\richard.carroll\desktop\Glebe99 - test.vsd" <================= This works
' visapp.ActiveDocument.saveas "d:\Documents and Settings\richard.carroll\desktop\Glebe07 - test.pdf" <================= This works

MsgBox "Stop Here"


visapp.Quit




End_of_Routine:



' DoCmd.Close acForm, "create floor form", acnosave ' ==================== Close the Access Form ====================
DoCmd.Close acQuery, "query data", acSaveNo
' DoCmd.Quit ' ==================== Shut down Access ====================
End Sub

[Jumpy]

Did you try to Save the Excel-worksheet as PDF from Access to try if this works?
Because maybe it's an Access problem, due to the fact, that Access seems not to support exportasfixedformat for itself.
Maybe you have to set a reference to the Visio-Library?

[Richard.carroll]

Yes, I saved the Excel file as a PDF with no problem.  I also saved the Visio drawing using the "Saveas" command.  I even saved the Visio file using the "saveas" command with a ".PDF" suffix to see if that was a problem.  All my saves are working except the "exportasfixedformat" save.

[Yacine]

Hi Richard,
"http://msdn.microsoft.com/en-us/library/ms409271.aspx" says that the feature needs an add-in.

You can save as a PDF or XPS file from a 2007 Microsoft Office system program only after you install an add-in. For more information, search for "Enable support for other file formats, such as PDF and XPS" on Office Online.

.
You show in your code that the saveas function works.
Try to record a macro from whitin visio in which you'll "save as PDF" and check the code. My visio got me a proper working exportasfixedformat call. What about your's?

[Richard.carroll]

I have Service Pack 2 of Office 2007 installed and the "save as PDF" feature is included.  If that was not the case, I would not have been able to save the Excel sheet earlier as a PDF, which I have done successfully, as a test. 

I am able to use my Exportasfixedformat code successfully when I run it from Visio VB, but my problem is when I run it from MS Access VB.

[Richard.carroll]

Despite the bogus "access denied" error, I finally got to the bottom of the issue.  As we suspected, the code was correct.  In order for it to run, I needed to click the check box for the Microsoft Visio 12.0 Type Library in the references line on the VB tools tab.  Interestingly, when I invoked Excel and ran Excel commands, I did not have to add the Excel library as a reference for Access.  Oh, well - live and learn.  The VB code now runs correctly.