Posted on 2010-01-06
Medium Priority
Last Modified: 2012-05-08
I have a math test all set up and is attached as a WORD document.  It has 15 questions, each followed by an empty area where students can input work.  After the work area is 4 multiple choice checkboxes where the students can pick the answer that matches their work.  Right now the correct answers are selected, but you can change the selections to test the macro below.  The test has to be located in the C:\ directory for the macro below to find it.

I have an excel file attached which has a macro that goes out and reads the checkboxes and compares them to a table in the Excel file with the correct answers.  The maco fills in a SCORES column for each of 15 questions.  If the student inputs the correct answers the score is 1, if not the score is 0, a SUM function calculates a grade.  If the student inputs more than one checkbox answer, that question is scored as a 0.

The problem is weird.  If I run the macro once, it works.  If I run it again I get an error 462 "The remote server machine does not exist or is unavailable".  Every odd time I run it, it runs.  Every even time I run it it fails.  If I run the macro, hit the reset in the RUN menu, and run it, it runs every time.

What is going on?
Question by:onlinemathteacher
  • 2
LVL 10

Accepted Solution

bromy2004 earned 375 total points
ID: 26197420
On your macro, change ActiveDocument to appWord

It couldn't find what the "ActiveDocument" was.

See attached code
Sub readtest()
   Dim TestPath As String
   Dim appWord As Word.Application, docWord As Word.Document
   Dim ffcb As FormField 'formfield checkbox
   Dim bValue As Boolean
   Dim ffdd As FormField 'formfield dropdown
   Dim strValue As String
   Dim iListIndex As Integer
   Dim CheckboxName As String
   Dim TestProblem As Integer
   Dim BoxNumber As Integer
   Dim cbCounter As Integer
   Dim cbCounterStr As String
   Dim Msg As String
   Dim Score As Integer
   Dim CellRow As Integer
   Dim CellCol As Integer
   Dim CheckboxSum As Integer
   Dim StudentBox As Integer
   Dim CorrectAns As Integer
   Dim doc As Word.Document

   Set appWord = New Word.Application
   appWord.Visible = True
   TestPath = "C:\"
   Set docWord = appWord.Documents.Open(TestPath & "student-ct1.doc")
   Application.EnableEvents = False
   'MsgBox "doc opened"
   'now read the pull downs and checkboxes
   Set ffdd = docWord.FormFields("Dropdown1")
   iListIndex = ffdd.DropDown.Value
   strValue = ffdd.DropDown.ListEntries(iListIndex).Name
   'MsgBox strValue
   For TestProblem = 1 To 15
     CellRow = TestProblem + 4
     For BoxNumber = 1 To 4
       'Read the problem's checkboxes
         cbCounter = ((TestProblem - 1)) * 4 + BoxNumber
         cbCounterStr = cbCounter
         CheckboxName = "Check" & cbCounterStr
         Set ffcb = ActiveDocument.FormFields(CheckboxName)
         bValue = ffcb.CheckBox.Value
         If bValue = False Then StudentBox = 0
         If bValue = True Then StudentBox = 1
       'Read the corresponding correct answer
         CellCol = BoxNumber + 2
         CorrectAns = Cells(CellRow, CellCol).Value
       'If one of more boxes disagree the score is 0 for this problem
         'Msg = "Problem " & TestProblem & "Box " & BoxNumber & " Student Ans " & StudentBox & "Correct Ans " & CorrectAns
         'MsgBox Msg
         If StudentBox <> CorrectAns Then
           Cells(CellRow, 8) = 0
           BoxNumber = 4
           Cells(CellRow, 8) = 1
         End If
   Application.EnableEvents = True
   Set docWord = Nothing
   Set appWord = Nothing
End Sub

Open in new window


Author Comment

ID: 26199279
I did not get it to work changing activedocument to appWord, but did if I changed it to docWord.
Going to test it some more, may have another question....

A little confused about why the DIM statements set variables to FORMFIELD but in the text they are used as FORMFIELDS.  The macro does not work if they are used as FORMFIELD in the body; errors.

Author Closing Comment

ID: 31673779
Seems like a slight mod to the suggested solution works.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question