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
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
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?
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
ASKER
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.
ASKER
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?
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
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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..
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..
ASKER
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.
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.
ASKER
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?
Any chance you could post the workbook and document for me to test?
ASKER
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
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?
ASKER
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
I'll keep working on it in the mean time. Thanks,
Kris
ASKER
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 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.
ASKER
The code did improve and helped me learn some VBA tricks. Thanks!
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.