VB code works from Dek top fails on server

I put together some code to save the first 11 tabs of the report as html and the last tab as text. The code works from my desk top but does not worl on the server. Does anyone know what the problem might be. Here is the code

Option Explicit

Sub shoot_to_text()

On Error Resume Next

    Dim Doc As Document
    Dim slocation   As String
    Dim smonth      As String
    Dim Rep As Report
    Dim i As Integer
    
    Set Doc = ActiveDocument
    
    slocation = "\\server\Archive\national_sales\"
    smonth = Format(Date - 27, "mmyyyy")

    Doc.Refresh
        
    Doc.ExportSheetsAsHtml slocation & "Direct", "Definitions|Summary|Rank MTD|Rank Prev Day|Region/VP Sales|Region/DSM|DSM/Store|DSM/Rep|Stores (inc Kiosks)|Reps with Errors|Fix Errors", , , , , , , , , boNoCheck, boHTMLSectionBySection
     
    For i = 12 To Doc.Reports.Count
        Set Rep = Doc.Reports.Item(i)
        Rep.ExportAsText (slocation & Rep.Name & ".txt")
    Next i

    
End Sub

msarkies :us: (BOB member since 2002-08-29)

What do you mean by “from the server” (BCA job, logged in manually to BCA server, running full-client report from web)? What do you mean by “does not work” exactly? What error do you receive?


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Sorry I was not clear. This is a full client report sent manually to the BCA. I do not receive an error I believe because my error handler says ON Error Resume Next. The report just runs for a long time on the BCA and finishes with no out put to html or text.


msarkies :us: (BOB member since 2002-08-29)

Debugging VBA problems that happen while running on a BCA server is problematic, since you have no interaction. A suggestion is to add logging functionality to your error handling routines. When an error is triggered, write something out to a text file in a persistent location that you can review later.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

  1. The first thing you should do is to open your document in full client on the same server that’s running BCA. Once open, go to the vba editor page and choose Debug, Compile Project from the menu. Any errors encountered in this way will give you a clue why the macro doesn’t do what you want when scheduled through BCA.

  2. Once you’ve cleared out any errors encountered as described above, then you should modify your code just a little. BCA doesn’t like a few commands, and one of them is “Set objvar = ActiveDocument” and then using the objvar in the code. NO THIS IS NOT LOGICAL! But that’s what we’ve learned through experience. A simple solution is to simply enclose the main portion of your procedure that references the ActiveDocument in a “With ActiveDocument” and “End With” construct.

Good luck!


cre907 :us: (BOB member since 2003-07-15)

Good stuff cre! Thanks for posting your experiences and tips.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)