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
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?
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.
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.
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.
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.