Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
VBA errors in 2007 (works fine in 2003) | Excel Discussion (Misc queries) | |||
Visual Basic Function works fine in Excell 2003 but not in Excel 2 | Excel Discussion (Misc queries) | |||
Macro hangs up often but sometimes works fine | Excel Worksheet Functions | |||
VLOOKUP on separate file: Works fine for a while.... | Excel Worksheet Functions | |||
REF errors when opening excel in xp. works fine when opening wor. | Excel Discussion (Misc queries) |