Link to home
Start Free TrialLog in
Avatar of kparrent
kparrent

asked on

Excel 2002, "Document Not Saved" after macro

Here's the situation:

I custom designed a process to streamline information from Word to Excel via macro.  The process was designed using Office 2010 and Office 2003.  At the time I was unaware she had Office 2002.

I went out and set it up, there were a few small errors with code compatibility of course, but there were some manual work arounds that we can do just fine.  

The way this macro works is the user fills out a form (form-fields) in Word, the user runs the macro (it was designed to detect if the workbook was open and handle it appropriately, but this feature was apparently not supported in 2002), opens the workbook, and adds data to anywhere from 3-7 different worksheets depending on the user input.  If the file does not exist they are attempting to write to does not exist, it was designed to create a new one from a template and save it as the specified name in the current path (but this also died with 2002).  

The user has reported that the first time the macro is run, it works great and encounters no problem.  However, after that it seems to be nearly all the time (if not all the time), if they run the macro again to add data to the same Excel file she cannot save the file.  She receives a "Document not saved" error, or something along those lines.  The project folder resides in a shared folder which is accessed by a total of two computers over a wireless network.  I have not been able to make it out to her location just yet since initial setup, so I am basing this purely off of what she has told me.  I will list below some of the troubleshooting we have done.

The user cannot do the following:

Save the file as a different name in the same folder
Save the file on a local, non shared directory with the same name
Could not save after copying the project directory from the shared folder, and opening the local copy

The file is not open on multiple computers simultaneously.  We tried installing Office XP SP3 with no luck.  The user is running AVG 9.0.7xx Free Edition.  The shared folder resides on her desktop, and the file name is in the format "Mailing Report mm-dd-yyyy" where mm-dd-yyyy represents an actual date.  The user is using Windows XP.

Thanks,

Kris
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

It sounds to me very much as though the workbook is not being closed after the first run and hence each subsequent time, it is being opened read-only. Given that you are automating Excel from Word, I would suspect you have unqualified references to Excel objects in your code (often this involves references to ActiveSheet, Selection or Range)
I also note that the things you say are not supported in 2002 certainly ought to be. Again, would have to see the code to comment as to the problem.
Avatar of kparrent
kparrent

ASKER

I'm not claiming this is the best written code ever.  I'm new to VBA and Office programming.  I'm sure it's sloppy :)

I thought it should work fine on 2002 as well.  It works great on my machine in both 2003 and 2010.  Might just be syntax differences?  
Sub sendToExcel()

'This program will take the values entered into the form fields
'of this Word template and manipulate the data into the
'respective places in Microsoft Excel.
'This program is the property of Kris Parrent and should not be
'used or altered without prior permission.
'Created March 7, 2010 by Kris Parrent
'Kris.Parrent@Valpo.edu

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasRunning As Boolean
Dim WorkbookOpen As Boolean
Dim WorkbookToWorkOn As String
Dim WorkbookName As String
Dim path As String

'Initialize
Set offld = ActiveDocument.FormFields
ExcelWasRunning = False

'Get File Path
path = ActiveDocument.FullName
path = Left(path, InStrRev(path, "\"))

'Set paths and WB name
WorkbookToWorkOn = path + "Mailing Report " + offld("mailDate").Result
WorkbookName = "Mailing Report " + offld("mailDate").Result + ".xls"

'Find out if Excel Running
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err Then
   ' Excel not open
   ExcelWasRunning = False
   Set oXL = New Excel.Application
   Err.Clear
   Set oWB = oXL.Workbooks.Open(filename:=WorkbookToWorkOn)
   
   ' If error, then WB DNE, Create it from Template.
   If Err Then
        createWorkbook (path)
        Err.Clear
   End If
   
Else: ExcelWasRunning = True
End If



'If Excel was running, find out if target WB open
If ExcelWasRunning Then
    WorkbookOpen = False
    For Each oWB In oXL.Workbooks
        If UCase(oWB.name) = UCase(WorkbookName) Then
            WorkbookOpen = True
        End If
    Next oWB
    If Not WorkbookOpen Then
        Set oWB = oXL.Workbooks.Open(filename:=WorkbookToWorkOn)
        ' If error, then WB DNE, Create it from Template.
        If Err Then
            createWorkbook (path)
            Err.Clear
        End If
    Else:
        oXL.Workbooks(WorkbookName).Activate
    End If
End If

'On Error GoTo Err_Handler

'Fill in the Job Production Sheet
fillJobProduction ("Parameter")


'Display Excel after changes made
oXL.Visible = True

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

Exit Sub

Err_Handler:
   MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
           "Error: " & Err.Number

End Sub

Public Function fillJobProduction(paramater As String)
    Dim oXL As Excel.Application
    Dim firstEmptyRow As Integer
    Dim areaCount As Integer
    Set oXL = GetObject(, "Excel.Application")
    
    
    Set offld = ActiveDocument.FormFields
    Row = "A"
    firstEmptyRow = 2
    Do While oXL.ActiveWorkbook.Worksheets("Job Production").Range("A" & firstEmptyRow) <> ""
      firstEmptyRow = firstEmptyRow + 1
    Loop
    
    oXL.ActiveWorkbook.Worksheets("Job Production").Range("A" & firstEmptyRow) = offld("jobNumber").Result
    oXL.ActiveWorkbook.Worksheets("Job Production").Range("B" & firstEmptyRow) = offld("customerName").Result
    oXL.ActiveWorkbook.Worksheets("Job Production").Range("D" & firstEmptyRow) = offld("areas").Result
    oXL.ActiveWorkbook.Worksheets("Job Production").Range("F" & firstEmptyRow) = "TBD"
    
    
    ' Find out how many areas each has
    areaCount = 0
    letter = "A"

NextLetter:
    If InStr(1, offld("areas").Result, letter, vbTextCompare) > 0 Then
        areaCount = areaCount + 1
    End If
    
        Select Case letter

          Case "A"
              letter = "B"
              GoTo NextLetter
          Case "B"
              letter = "C"
              GoTo NextLetter
          Case "C"
              letter = "D"
              GoTo NextLetter
          Case "D"
              letter = "E"
              GoTo NextLetter
          Case "E"
              letter = "J"
              GoTo NextLetter
          Case Else
              GoTo Continue
    End Select
    
Continue:
    oXL.ActiveWorkbook.Worksheets("Job Production").Range("E" & firstEmptyRow) = areaCount
    
    If UCase(offld("paperStock").Result) = UCase("Coated") Then
        oXL.ActiveWorkbook.Worksheets("Job Production").Range("G" & firstEmptyRow) = "C"
    Else: oXL.ActiveWorkbook.Worksheets("Job Production").Range("G" & firstEmptyRow) = ""
    End If
    
    oXL.ActiveWorkbook.Worksheets("Job Production").Range("A" & firstEmptyRow).Offset(1).EntireRow.Insert
    
    fillInvoice (firstEmptyRow)
    fillAreas (firstEmptyRow)

End Function

Public Function fillAreas(jobProductionRow As Integer)
    Dim oXL As Excel.Application
    Dim firstEmptyRow As Integer
    Dim currentSheet As String
    Dim Column As Long
    Dim entryNumber As Integer
    
    Set oXL = GetObject(, "Excel.Application")
    Set offld = ActiveDocument.FormFields
    currentSheet = "A"


NextSheet:
    ' Test / Fill Area currentSheet
    
    If InStr(1, offld("areas").Result, currentSheet, vbTextCompare) > 0 Then

        firstEmptyRow = 4
        'Ascii for A
        Column = 65
        Do While oXL.ActiveWorkbook.Worksheets("Area " & currentSheet).Range(Chr(Column + 1) & firstEmptyRow).Value <> ""
            firstEmptyRow = firstEmptyRow + 1
            If firstEmptyRow > 20 Then
                firstEmptyRow = 4
                Column = Column + 5
            End If
        Loop
        
        If Column = 65 Then
            entryNumber = firstEmptyRow
        ElseIf Column = 70 Then
            entryNumber = firstEmptyRow + 17
        Else: entryNumber = firstEmptyRow + 34
        End If
        
        
        'Number Column A
        If oXL.ActiveWorkbook.Worksheets("Area " & currentSheet).Range(Chr(Column) & firstEmptyRow).Value <> entryNumber - 3 Then
            oXL.ActiveWorkbook.Worksheets("Area " & currentSheet).Range(Chr(Column) & firstEmptyRow).Value = entryNumber - 3
        End If
        
        'Put Customer Name in Column B
        oXL.ActiveWorkbook.Worksheets("Area " & currentSheet).Range(Chr(Column + 1) & firstEmptyRow).Value = offld("customerName").Result
        
        'Fill Column C
        oXL.ActiveWorkbook.Worksheets("Area " & currentSheet).Range(Chr(Column + 2) & firstEmptyRow).Value = oXL.ActiveWorkbook.Worksheets("Job Production").Range("F" & jobProductionRow).Value
        
        'Check to see if coated or uncoated
        If UCase(offld("paperStock").Result) = UCase("Coated") Then
            oXL.ActiveWorkbook.Worksheets("Area " & currentSheet).Range(Chr(Column + 3) & firstEmptyRow).Value = "Coated"
        Else: oXL.ActiveWorkbook.Worksheets("Area " & currentSheet).Range(Chr(Column + 3) & firstEmptyRow).Value = "#50"
        End If
        
        ' Insert Blank Row
        ' oXL.ActiveWorkbook.Worksheets("Area " & currentSheet).Range("A" & firstEmptyRow).Offset(1).EntireRow.Insert
    End If
    
    Select Case currentSheet
          Case "A"
              currentSheet = "B"
              GoTo NextSheet
          Case "B"
              currentSheet = "C"
              GoTo NextSheet
          Case "C"
              currentSheet = "D"
              GoTo NextSheet
          Case "D"
              currentSheet = "E"
              GoTo NextSheet
          Case "E"
              currentSheet = "J"
              GoTo NextSheet
          Case Else
              GoTo finished
    End Select

finished:

End Function

Function createWorkbook(currentPath As String)

Dim oXL As Excel.Application
Dim currentSheet As String

Set offld = ActiveDocument.FormFields

'Open Template and Rename
Set oXL = GetObject(, "Excel.Application")
Set oWB = oXL.Workbooks.Open(currentPath + "Template")
oXL.Workbooks("Template").SaveAs (currentPath + "Mailing Report " + offld("mailDate").Result)

'Set Mailing Date
currentSheet = "A"

NextSheet:
oXL.ActiveWorkbook.Worksheets("Area " & currentSheet).Range("D2").Value = offld("mailDate").Result

Select Case currentSheet
    Case "A"
        currentSheet = "B"
        GoTo NextSheet
    Case "B"
        currentSheet = "C"
        GoTo NextSheet
    Case "C"
        currentSheet = "D"
        GoTo NextSheet
    Case "D"
        currentSheet = "E"
        GoTo NextSheet
    Case "E"
        currentSheet = "J"
        GoTo NextSheet
    Case Else
        GoTo finished
End Select
    
finished:
End Function

Function fillInvoice(firstEmptyRow As Integer)

Dim oXL As Excel.Application
Set oXL = GetObject(, "Excel.Application")
Set offld = ActiveDocument.FormFields

oXL.ActiveWorkbook.Worksheets("Invoice").Range("B" & firstEmptyRow).Value = offld("customerName").Result

oXL.ActiveWorkbook.Worksheets("Invoice").Range("A" & firstEmptyRow).Offset(1).EntireRow.Insert

End Function

Open in new window

Another strange thing.  These files have to be in the same folder in order for it to find the Excel file.  When they were both on her desktop, it wouldn't open up Excel.  I put them in a folder on the desktop and it worked right away.
In response to the thought of Excel not being closed:

I had this thought as well because it happened to me a few times in my beta stages of code testing.  This macro leaves Excel open after it runs so that the user may view the changes.  It must be closed manually.  I troubleshooted with her on the phone for a little while, and I had her close Excel all the way out and even open up task manager and look for the Excel process.  I had her check the for something along the lines of EXCEL because I believe that is what it's typically called, but it was not there.  Does anyone happen to know the 2002 process name?
The process is Excel.exe.
Do you mind if I amend the code? I think it will be a lot simpler if you pass a workbook object between the functions rather than using GetObject to grab a running application (which might not be the right one!) and then find the workbook.
Is your TEMPLATE workbook actually a template (xlt) file or is it a normal workbook that you use as a template?
Regards,
Rory
Rory,

Feel free to make any changes that you think may help.  This is my first project using VBA, so I'd be interested in the differences.  It is actually a .xls file (so not a true tempate).  

This is originally being run from a Word template (.dot).  On her machine though, it would not execute properly from the .dot file, but it would from a .doc created from the template.  It's all been very strange.

Thanks for the help!

Kris
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, I will give that a try.  I may have some questions about some reasoning behind the changes once I get a chance to look at it.

This is something I just noticed.  Let me ask if it would make a difference.  If I go into a file and I create a macro in Word 2010, then I open it in Word 2003 (or any version of Word for that matter), that macro I just created does not appear in the macro list.  If I then create a different macro in that same file in 2003 and go back and open it in 2010, 2010 only sees the one that was created in 2010.  I also had to copy and paste the code in on her machine because it wasn't showing up in 2002 when I sent her the file, although my colleague with the same version of Word was able to view it.  So it is very possible the file she is using has macros stored for 2002, 2003, and 2010, all referencing different versions of the Excel and Office libraries.  I'm sure this can't be a good thing, but would it be causing any problems?

Thanks again.
Rory,

I tried your code in Excel 2002 on Windows XP.  I am still getting a document not saved error.  I had to change

    Set offld = ThisDocument.FormFields

and

    path = ThisDocument.path & "\"

back to ActiveDocument in order for it to find the file, but those were the only modifications I made.  Back to the drawing board..
Here's the situation:

The macro can be run as many times as needed, and then saved.  As long as the workbook is not closed, the macro may be run again and the file can be saved.  As soon as the workbook is closed, changes to the file can no longer be saved, whether that be through a macro or manual editing.
Let me clarify, closed and reopened.  So anytime after the file is closed after the first time this macro is run on it, then changes can no longer be saved after it is reopened.
I have never come across, or heard of, the behaviour you describe with the macros but I would suggest that developing a production app in a beta version of Office is really not wise! ;)
Any chance you could post the workbook and document for me to test?
I realized that which is why I switched to 2003 after starting :)  

Sure, I have removed all company logos and irrelevant information from the files.  Should be the same functionality though.

Layout-Form-EE.dot
Template.xls
Thanks - it will probably be next week before I can set up a test machine with 2002 on it. Hope that's not too late?
Well, it is sort of a priority project.  But if it can't be done before then then that's how it's going to have to be.  It's better to be working late than not at all.  

I'll keep working on it in the mean time.  Thanks,

Kris
Well, that was annoying.

The coding is perfectly fine.  I went through the process of starting from the bare minimum.  Not calling any of the functions and just making a single change to the macro.  No luck.  I created a brand new blank document with just the form fields with the macro only making one change and not calling any of the functions.  No luck.  At which point, there was something fishy.

I created a blank workbook and named it according to the macro, the macro ran fine and was able to save and resave.  It was the codeless Excel workbook the whole time!  I just copied the sheets to a brand new workbook, and it works perfectly.  Ugh.  :)

Thanks for the help.  It is very appreciated.
The code did improve and helped me learn some VBA tricks.  Thanks!