P
PoK
when i run the code that is in excel through for the first time - it
works fine. if i try to run it through a second time without first
shutting down the excel sheet and reopening it i get:
Run-time error'-2147023174 (800706ba)':
Automation error
the RPC error server is unavailable.
if i hit debug - it is bombing here : Word.ActiveWindow.PrintOut
i could understand if it didnt open word up - but words is open with
the document slected when it errors.
quick intro:
user enters a part number in the entry box. clicks find files button
- it searchs through numerous folders and approx 35,000 files for
files that match that part number and presents them on the main screen
as links. user clicks in a check box next to the link and enters a
number in the # of copies box and the program prints out the
corresponding documents (.doc - .xls - .cdr - .pdf) and the number of
copies the users and entered fine the first time. its if the users
need the files for a different part and enters it in and try's to run
it right after the first one it bombs.
code in there clears everything out also after its done priting so
theres nothing left on the screen except the normal format and emtpy
box's.
here are snipets of the code i have:
Private Sub CommandButton1_Click()
Dim hold As String
Dim PNLength As String
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim lAnswer As Long
Dim llanswer As Long
Dim lllanswer As Long
*****under the find files buton*****
PNLength = Len(Sheet1.Cells(6, 6).Value)
If PNLength < 5 Then
lllanswer = MsgBox("You must enter 5 or more characters in the
search box to do a search...", vbOK, "ATTENTION : ERROR MESSAGE")
Else
hold = Sheet1.Cells(6, 6).Value + ".doc"
If hold <> ".doc" Then
With Application.FileSearch
.NewSearch
.LookIn = "S:\PART_INFO_FILES\PART_INFO\DATA_SHEETS"
.SearchSubFolders = True
.FileName = hold
.MatchTextExactly = True
.Execute
For I = 1 To .FoundFiles.Count
Sheet1.Cells(14 + I, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
.FoundFiles(I)
Next I
If .FoundFiles.Count = 0 Then
Sheet1.Cells(15, 2).Value = "No Matching Files Found"
End If
End With
Else
Sheet1.Cells(15, 2).Value = "YOU DID NOT ENTER A VALID PART
NUMBER"
End If
*****under the print button***** for 1 of the numerous check boxes
Check = 0
lAnswer = MsgBox("Are the amount of copies you need correct (the
Default is 1)?? If not, hit the cancel button now and correct them
to your desired amount.", vbOKCancel, "Printing files from user's
search.
If lAnswer = vbOK Then
g = (CheckBox1.Value + CheckBox2.Value + CheckBox3.Value +
CheckBox4.Value + CheckBox5.Value + CheckBox6.Value + CheckBox7.Value
+ CheckBox8.Value + CheckBox9.Value + CheckBox10.Value +
CheckBox11.Value + CheckBox12.Value + CheckBox13.Value +
CheckBox14.Value + CheckBox15.Value + CheckBox16.Value +
CheckBox17.Value + CheckBox18.Value + CheckBox19.Value +
CheckBox20.Value + CheckBox21.Value + CheckBox22.Value +
CheckBox23.Value + CheckBox24.Value + CheckBox25.Value +
CheckBox26.Value + CheckBox27.Value + CheckBox28.Value +
CheckBox29.Value + CheckBox30.Value)
If g = 0 Then
llanswer = MsgBox("You must check at least 1 checkbox,
corresponding to a file to print, before continuing...", vbOK,
"ATTENTION: **ERROR MESSAGE**")
Else
If CheckBox1.Value = True And Len(Sheet1.Cells(15, 1).Value) > 0 Then
Check = 1
PNValue = Sheet1.Cells(15, 11).Value
If PNValue < 1 Then
lllanswer = MsgBox("You must enter a number for the #
of copies that is greater than 0", vbOK, "ATTENTION : ERROR MESSAGE")
Check = 0
Else
Sheet1.Cells(15, 1).Select
Selection.Hyperlinks(1).Follow NewWindow:=False,
AddHistory:=True
Application.WindowState = xlMaximized
c = Sheet1.Cells(15, 11).Value
cc = 0
Do Until c = cc
cc = cc + 1
Word.ActiveWindow.PrintOut
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Loop
Word.Documents.Close
End If
Else
End If
*****at the end of the first 10 checkbox which are all word is
this*****
If Check > 0 Then
Word.Application.Quit
Else
End If
*****for the checkbox i also have this*****
Private Sub CheckBox1_Click()
If Sheet1.Cells(15, 1).Value = "" And CheckBox1.Value = True Then
CheckBox1.Value = False
End If
If CheckBox1.Value = True And Sheet1.Cells(15, 11).Value >= 0 Then
Sheet1.Cells(15, 11).Value = 1
End If
End Sub
ANY IDEAS WHY IT RUNS FINE THROUGH ALL THE DOCUMENTS - OPENS PRINTS
AND CLOSES ALL THE APPLICATIONS FOR EACH OF THE DOCUMENTS AND THEN
ERRORS OUT IF ITS TRIED TO RUN AGAIN WITHOUT CLOSING THEN REOPENING
THE EXCEL SHEET ?
works fine. if i try to run it through a second time without first
shutting down the excel sheet and reopening it i get:
Run-time error'-2147023174 (800706ba)':
Automation error
the RPC error server is unavailable.
if i hit debug - it is bombing here : Word.ActiveWindow.PrintOut
i could understand if it didnt open word up - but words is open with
the document slected when it errors.
quick intro:
user enters a part number in the entry box. clicks find files button
- it searchs through numerous folders and approx 35,000 files for
files that match that part number and presents them on the main screen
as links. user clicks in a check box next to the link and enters a
number in the # of copies box and the program prints out the
corresponding documents (.doc - .xls - .cdr - .pdf) and the number of
copies the users and entered fine the first time. its if the users
need the files for a different part and enters it in and try's to run
it right after the first one it bombs.
code in there clears everything out also after its done priting so
theres nothing left on the screen except the normal format and emtpy
box's.
here are snipets of the code i have:
Private Sub CommandButton1_Click()
Dim hold As String
Dim PNLength As String
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim lAnswer As Long
Dim llanswer As Long
Dim lllanswer As Long
*****under the find files buton*****
PNLength = Len(Sheet1.Cells(6, 6).Value)
If PNLength < 5 Then
lllanswer = MsgBox("You must enter 5 or more characters in the
search box to do a search...", vbOK, "ATTENTION : ERROR MESSAGE")
Else
hold = Sheet1.Cells(6, 6).Value + ".doc"
If hold <> ".doc" Then
With Application.FileSearch
.NewSearch
.LookIn = "S:\PART_INFO_FILES\PART_INFO\DATA_SHEETS"
.SearchSubFolders = True
.FileName = hold
.MatchTextExactly = True
.Execute
For I = 1 To .FoundFiles.Count
Sheet1.Cells(14 + I, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
.FoundFiles(I)
Next I
If .FoundFiles.Count = 0 Then
Sheet1.Cells(15, 2).Value = "No Matching Files Found"
End If
End With
Else
Sheet1.Cells(15, 2).Value = "YOU DID NOT ENTER A VALID PART
NUMBER"
End If
*****under the print button***** for 1 of the numerous check boxes
Check = 0
lAnswer = MsgBox("Are the amount of copies you need correct (the
Default is 1)?? If not, hit the cancel button now and correct them
to your desired amount.", vbOKCancel, "Printing files from user's
search.
If lAnswer = vbOK Then
g = (CheckBox1.Value + CheckBox2.Value + CheckBox3.Value +
CheckBox4.Value + CheckBox5.Value + CheckBox6.Value + CheckBox7.Value
+ CheckBox8.Value + CheckBox9.Value + CheckBox10.Value +
CheckBox11.Value + CheckBox12.Value + CheckBox13.Value +
CheckBox14.Value + CheckBox15.Value + CheckBox16.Value +
CheckBox17.Value + CheckBox18.Value + CheckBox19.Value +
CheckBox20.Value + CheckBox21.Value + CheckBox22.Value +
CheckBox23.Value + CheckBox24.Value + CheckBox25.Value +
CheckBox26.Value + CheckBox27.Value + CheckBox28.Value +
CheckBox29.Value + CheckBox30.Value)
If g = 0 Then
llanswer = MsgBox("You must check at least 1 checkbox,
corresponding to a file to print, before continuing...", vbOK,
"ATTENTION: **ERROR MESSAGE**")
Else
If CheckBox1.Value = True And Len(Sheet1.Cells(15, 1).Value) > 0 Then
Check = 1
PNValue = Sheet1.Cells(15, 11).Value
If PNValue < 1 Then
lllanswer = MsgBox("You must enter a number for the #
of copies that is greater than 0", vbOK, "ATTENTION : ERROR MESSAGE")
Check = 0
Else
Sheet1.Cells(15, 1).Select
Selection.Hyperlinks(1).Follow NewWindow:=False,
AddHistory:=True
Application.WindowState = xlMaximized
c = Sheet1.Cells(15, 11).Value
cc = 0
Do Until c = cc
cc = cc + 1
Word.ActiveWindow.PrintOut
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Loop
Word.Documents.Close
End If
Else
End If
*****at the end of the first 10 checkbox which are all word is
this*****
If Check > 0 Then
Word.Application.Quit
Else
End If
*****for the checkbox i also have this*****
Private Sub CheckBox1_Click()
If Sheet1.Cells(15, 1).Value = "" And CheckBox1.Value = True Then
CheckBox1.Value = False
End If
If CheckBox1.Value = True And Sheet1.Cells(15, 11).Value >= 0 Then
Sheet1.Cells(15, 11).Value = 1
End If
End Sub
ANY IDEAS WHY IT RUNS FINE THROUGH ALL THE DOCUMENTS - OPENS PRINTS
AND CLOSES ALL THE APPLICATIONS FOR EACH OF THE DOCUMENTS AND THEN
ERRORS OUT IF ITS TRIED TO RUN AGAIN WITHOUT CLOSING THEN REOPENING
THE EXCEL SHEET ?