Ms Access Word and Excel report problems

[spectrum]

I have a PC and a Laptop where the following code works okay. However another laptop, having the same Office 201 installation, service pack etc, fails to work. I have checked Word and Excel references in Access and they are ticked okay. A command to send report to PDF works okay.

Word

Code: Select all

 Dim stDocName As String
 Dim stFileName As String
 stDocName = "Queryform2"
 stFileName = "C:\" & stDocName & ".rtf"
 DoCmd.OutputTo acReport, stDocName, acFormatRTF, stFileName, True

Excel

Code: Select all

Dim stDocName As String
Dim stFileName As String
stDocName = "Queryform2"
stFileName = "C:\" & stDocName & ".xls"
DoCmd.OutputTo acOutputReport, "QueryForm2", acFormatXLS, stFileName, True, "", , acExportQualityPrint

PDF

Code: Select all

Dim stDocName As String
Dim stFileName As String
stDocName = "Queryform2"
stFileName = "C:\" & stDocName & ".pdf"
 DoCmd.OpenReport stDocName, acPreview, , Filter, acHidden
 DoCmd.OutputTo acReport, stDocName, acFormatPDF, stFileName, True
 DoCmd.Close acReport, stDocName

The Word and Excel code both show some activity of doing something, but fail to open and fail to leave reports on drive C:C:\" & stDocName

Another Excel report event also works as well as the PDF report. Any clues

Excel

Code: Select all

    Dim rs As DAO.Recordset
    Dim oXLApp As Excel.Application
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet
    If Me.LPP.RowSource = "" Then Exit Sub
    Set oXLApp = New Excel.Application
    Set oXLBook = oXLApp.Workbooks.Add
    Set oXLSheet = oXLBook.ActiveSheet
    Set rs = CurrentDb.OpenRecordset(Me.LPP.RowSource)
    oXLSheet.Range("B2").CopyFromRecordset rs
    oXLApp.Visible = True
    oXLApp.UserControl = True
    Set oXLBook = Nothing
    Set oXLApp = Nothing
    Set oXLSheet = Nothing
    

Many thanks

[HansV]

You only need a reference to the Excel object library for the last piece of code (with Dim oXLApp As Excel.Application etc.). Using OutputTo doesn't require setting a reference to the Word or Excel object libraries.

If you add error handling, does that provide any information?

Code: Select all

Sub Export2RTF()
    Dim stDocName As String
    Dim stFileName As String
    On Error GoTo ErrHandler
    stDocName = "Queryform2"
    stFileName = "C:\" & stDocName & ".rtf"
    DoCmd.OutputTo acReport, stDocName, acFormatRTF, stFileName, True
    Exit Sub

ErrHandler:
    MsgBox "Error: " & Err.Number & ": " & Err.Description, vbExclamation
End Sub

You could try exporting to a subfolder of C: instead of to the root C:\. On some installations, users aren't allowed to save to C:\.

[spectrum]

Thankyou Hans, I will try changing the file destination, and also check the event ErrHandler. Have a good weekend

[spectrum]

Thankyou Hans, you were correct!!

I created a sub directory, and it works. So strange as there seems no other restrictions are there to save files to the root directory. Anyway you solved it, many thanks again.