|
sheets(N*).printout problem
why do I get erro #9 @ printing?
i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
sheets(N*).printout problem
Error 9 as you put it can mean many things, one of the most common would be
if the worksheet you are referencing did not exist, are you sure the workbook/worksheet you are calling to print exists or is vivible? Regards, The Code Cage Team www.thecodecage.com/forumz "Paulo" wrote: why do I get erro #9 @ printing? i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
sheets(N*).printout problem
Usint the worksheet index, i.e. Worksheets(4) requires that four worksheets
be in the work book. If you had four sheets and deleted sheet 1, 2 or 3, you might think that Sheet4 is the same as Worksheets(4), but it is not. The index number runs from left to right for the sheet tabs as they appear in the window, including any hidden sheets. If you have Sheet1(visible), Sheet2(visible), Sheet3(hidden), and Sheet4(visible) in that order, then Sheet4 is Worksheets(4). But if Sheet3 had been deleted instead of hidden, then Sheet4 becomes worksheets(3). And using Worksheets(4).PrintOut would produce error 9, "Paulo" wrote: why do I get erro #9 @ printing? i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
sheets(N*).printout problem
thank you for the ilustrative explanation. but none of the them happen in my
case. I have 100 workbooks with the same structure. 6 sheest, none have macro, so the probably dont have hidden sheets. other than The error happends in the printout part, all the rest of the code goes smoothly. if i were mistaken on the Sheets(n*) , i woul have Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" on the wrong sheet, and that didnt happen. i have changed this lines to doublecheck - but no joy, erro #9 ;) Worksheets("CAL.ECON.").PrintOut MsgBox "Vire A página" Worksheets("DETALHADA").PrintOut Worksheets("CAL.ECON.").Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True "Paulo" wrote: why do I get erro #9 @ printing? i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
sheets(N*).printout problem
Where you have these lines:
''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut For testingk, modify to this: ''Workbooks(Caminho).Save MsgBox ActiveWorkbook.Name 'Printing Worksheets(4).PrintOut If the workbook name displayed in the message box is not the one with the sheet to copy then you will need to activate the correct workbook. "Paulo" wrote: thank you for the ilustrative explanation. but none of the them happen in my case. I have 100 workbooks with the same structure. 6 sheest, none have macro, so the probably dont have hidden sheets. other than The error happends in the printout part, all the rest of the code goes smoothly. if i were mistaken on the Sheets(n*) , i woul have Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" on the wrong sheet, and that didnt happen. i have changed this lines to doublecheck - but no joy, erro #9 ;) Worksheets("CAL.ECON.").PrintOut MsgBox "Vire A página" Worksheets("DETALHADA").PrintOut Worksheets("CAL.ECON.").Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True "Paulo" wrote: why do I get erro #9 @ printing? i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
sheets(N*).printout problem
I am not sure if I am able to express my self correctly.
''Workbooks(Caminho). is working fine. I have been able to open "caminho", unprotect Sheet(4), change range value @ sheet(4), protect sheet(4), ,and save the workbook . when it gets to the printing part, that is literaly after all this other steps that I just named, I get the error #9. "JLGWhiz" wrote: Where you have these lines: ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut For testingk, modify to this: ''Workbooks(Caminho).Save MsgBox ActiveWorkbook.Name 'Printing Worksheets(4).PrintOut If the workbook name displayed in the message box is not the one with the sheet to copy then you will need to activate the correct workbook. "Paulo" wrote: thank you for the ilustrative explanation. but none of the them happen in my case. I have 100 workbooks with the same structure. 6 sheest, none have macro, so the probably dont have hidden sheets. other than The error happends in the printout part, all the rest of the code goes smoothly. if i were mistaken on the Sheets(n*) , i woul have Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" on the wrong sheet, and that didnt happen. i have changed this lines to doublecheck - but no joy, erro #9 ;) Worksheets("CAL.ECON.").PrintOut MsgBox "Vire A página" Worksheets("DETALHADA").PrintOut Worksheets("CAL.ECON.").Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True "Paulo" wrote: why do I get erro #9 @ printing? i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
sheets(N*).printout problem
Error 9 occurs when the compiler cannot find the object that your code tells
it to look for, in this case, worksheets(4). The only reason that it would not find worksheets(4) is that the active workbook does not contain a worksheets(4). In your code you have this snippet: Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Which appears to open other workbooks. If I am correct, then the last workbook opened will be the active workbook, and might not be the workbook that you are trying to print from. All of the other thingss that you say are working occur in your code before that that particular snippet, so it could be the cause of the problem. The message box insert was meant to verify that the correct workbook is the active workbook at the time the PrintOut command is executed. There is, otherwise, no reason why the sheet should not print. "Paulo" wrote: I am not sure if I am able to express my self correctly. ''Workbooks(Caminho). is working fine. I have been able to open "caminho", unprotect Sheet(4), change range value @ sheet(4), protect sheet(4), ,and save the workbook . when it gets to the printing part, that is literaly after all this other steps that I just named, I get the error #9. "JLGWhiz" wrote: Where you have these lines: ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut For testingk, modify to this: ''Workbooks(Caminho).Save MsgBox ActiveWorkbook.Name 'Printing Worksheets(4).PrintOut If the workbook name displayed in the message box is not the one with the sheet to copy then you will need to activate the correct workbook. "Paulo" wrote: thank you for the ilustrative explanation. but none of the them happen in my case. I have 100 workbooks with the same structure. 6 sheest, none have macro, so the probably dont have hidden sheets. other than The error happends in the printout part, all the rest of the code goes smoothly. if i were mistaken on the Sheets(n*) , i woul have Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" on the wrong sheet, and that didnt happen. i have changed this lines to doublecheck - but no joy, erro #9 ;) Worksheets("CAL.ECON.").PrintOut MsgBox "Vire A página" Worksheets("DETALHADA").PrintOut Worksheets("CAL.ECON.").Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True "Paulo" wrote: why do I get erro #9 @ printing? i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
sheets(N*).printout problem
Paulo is adding the microsoft library reference ATPVBAEN.XLA which is the VBA
call for the add-in ANALASYS TOOLPAKm but again should not make a difference, why not try running the code to print Sheet 4 on its own after all the above code has run, just call another sub with sheet4 printout in it and see what happens. Regards, The Code Cage Team www.thecodecage.com/forumz "JLGWhiz" wrote: Error 9 occurs when the compiler cannot find the object that your code tells it to look for, in this case, worksheets(4). The only reason that it would not find worksheets(4) is that the active workbook does not contain a worksheets(4). In your code you have this snippet: Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Which appears to open other workbooks. If I am correct, then the last workbook opened will be the active workbook, and might not be the workbook that you are trying to print from. All of the other thingss that you say are working occur in your code before that that particular snippet, so it could be the cause of the problem. The message box insert was meant to verify that the correct workbook is the active workbook at the time the PrintOut command is executed. There is, otherwise, no reason why the sheet should not print. "Paulo" wrote: I am not sure if I am able to express my self correctly. ''Workbooks(Caminho). is working fine. I have been able to open "caminho", unprotect Sheet(4), change range value @ sheet(4), protect sheet(4), ,and save the workbook . when it gets to the printing part, that is literaly after all this other steps that I just named, I get the error #9. "JLGWhiz" wrote: Where you have these lines: ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut For testingk, modify to this: ''Workbooks(Caminho).Save MsgBox ActiveWorkbook.Name 'Printing Worksheets(4).PrintOut If the workbook name displayed in the message box is not the one with the sheet to copy then you will need to activate the correct workbook. "Paulo" wrote: thank you for the ilustrative explanation. but none of the them happen in my case. I have 100 workbooks with the same structure. 6 sheest, none have macro, so the probably dont have hidden sheets. other than The error happends in the printout part, all the rest of the code goes smoothly. if i were mistaken on the Sheets(n*) , i woul have Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" on the wrong sheet, and that didnt happen. i have changed this lines to doublecheck - but no joy, erro #9 ;) Worksheets("CAL.ECON.").PrintOut MsgBox "Vire A página" Worksheets("DETALHADA").PrintOut Worksheets("CAL.ECON.").Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True "Paulo" wrote: why do I get erro #9 @ printing? i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
sheets(N*).printout problem
I have tried printiout somewher else out of this code. works just fine.
i also noticed that after callin ATPVBAEM , when i debug print Application.ActiveWindow.Caption it tells me that I am @ ATPVBAEM .xla i am guessing I have to call back the "caminho" workbook. "The Code Cage Team" wrote: Paulo is adding the microsoft library reference ATPVBAEN.XLA which is the VBA call for the add-in ANALASYS TOOLPAKm but again should not make a difference, why not try running the code to print Sheet 4 on its own after all the above code has run, just call another sub with sheet4 printout in it and see what happens. Regards, The Code Cage Team www.thecodecage.com/forumz "JLGWhiz" wrote: Error 9 occurs when the compiler cannot find the object that your code tells it to look for, in this case, worksheets(4). The only reason that it would not find worksheets(4) is that the active workbook does not contain a worksheets(4). In your code you have this snippet: Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Which appears to open other workbooks. If I am correct, then the last workbook opened will be the active workbook, and might not be the workbook that you are trying to print from. All of the other thingss that you say are working occur in your code before that that particular snippet, so it could be the cause of the problem. The message box insert was meant to verify that the correct workbook is the active workbook at the time the PrintOut command is executed. There is, otherwise, no reason why the sheet should not print. "Paulo" wrote: I am not sure if I am able to express my self correctly. ''Workbooks(Caminho). is working fine. I have been able to open "caminho", unprotect Sheet(4), change range value @ sheet(4), protect sheet(4), ,and save the workbook . when it gets to the printing part, that is literaly after all this other steps that I just named, I get the error #9. "JLGWhiz" wrote: Where you have these lines: ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut For testingk, modify to this: ''Workbooks(Caminho).Save MsgBox ActiveWorkbook.Name 'Printing Worksheets(4).PrintOut If the workbook name displayed in the message box is not the one with the sheet to copy then you will need to activate the correct workbook. "Paulo" wrote: thank you for the ilustrative explanation. but none of the them happen in my case. I have 100 workbooks with the same structure. 6 sheest, none have macro, so the probably dont have hidden sheets. other than The error happends in the printout part, all the rest of the code goes smoothly. if i were mistaken on the Sheets(n*) , i woul have Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" on the wrong sheet, and that didnt happen. i have changed this lines to doublecheck - but no joy, erro #9 ;) Worksheets("CAL.ECON.").PrintOut MsgBox "Vire A página" Worksheets("DETALHADA").PrintOut Worksheets("CAL.ECON.").Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True "Paulo" wrote: why do I get erro #9 @ printing? i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
sheets(N*).printout problem
Paulo, that is your problem, you are trying to print sheet4 of the xla
(add-in) which doesn't exist so you would have to do something like: Workbooks("Camino").Activate before the printout line! -- Regards, The Code Cage Team www.thecodecage.com/forumz "Paulo" wrote: I have tried printiout somewher else out of this code. works just fine. i also noticed that after callin ATPVBAEM , when i debug print Application.ActiveWindow.Caption it tells me that I am @ ATPVBAEM .xla i am guessing I have to call back the "caminho" workbook. "The Code Cage Team" wrote: Paulo is adding the microsoft library reference ATPVBAEN.XLA which is the VBA call for the add-in ANALASYS TOOLPAKm but again should not make a difference, why not try running the code to print Sheet 4 on its own after all the above code has run, just call another sub with sheet4 printout in it and see what happens. Regards, The Code Cage Team www.thecodecage.com/forumz "JLGWhiz" wrote: Error 9 occurs when the compiler cannot find the object that your code tells it to look for, in this case, worksheets(4). The only reason that it would not find worksheets(4) is that the active workbook does not contain a worksheets(4). In your code you have this snippet: Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Which appears to open other workbooks. If I am correct, then the last workbook opened will be the active workbook, and might not be the workbook that you are trying to print from. All of the other thingss that you say are working occur in your code before that that particular snippet, so it could be the cause of the problem. The message box insert was meant to verify that the correct workbook is the active workbook at the time the PrintOut command is executed. There is, otherwise, no reason why the sheet should not print. "Paulo" wrote: I am not sure if I am able to express my self correctly. ''Workbooks(Caminho). is working fine. I have been able to open "caminho", unprotect Sheet(4), change range value @ sheet(4), protect sheet(4), ,and save the workbook . when it gets to the printing part, that is literaly after all this other steps that I just named, I get the error #9. "JLGWhiz" wrote: Where you have these lines: ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut For testingk, modify to this: ''Workbooks(Caminho).Save MsgBox ActiveWorkbook.Name 'Printing Worksheets(4).PrintOut If the workbook name displayed in the message box is not the one with the sheet to copy then you will need to activate the correct workbook. "Paulo" wrote: thank you for the ilustrative explanation. but none of the them happen in my case. I have 100 workbooks with the same structure. 6 sheest, none have macro, so the probably dont have hidden sheets. other than The error happends in the printout part, all the rest of the code goes smoothly. if i were mistaken on the Sheets(n*) , i woul have Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" on the wrong sheet, and that didnt happen. i have changed this lines to doublecheck - but no joy, erro #9 ;) Worksheets("CAL.ECON.").PrintOut MsgBox "Vire A página" Worksheets("DETALHADA").PrintOut Worksheets("CAL.ECON.").Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True "Paulo" wrote: why do I get erro #9 @ printing? i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
sheets(N*).printout problem
It sometimes takes a while for the message to get through.
"Paulo" wrote: I have tried printiout somewher else out of this code. works just fine. i also noticed that after callin ATPVBAEM , when i debug print Application.ActiveWindow.Caption it tells me that I am @ ATPVBAEM .xla i am guessing I have to call back the "caminho" workbook. "The Code Cage Team" wrote: Paulo is adding the microsoft library reference ATPVBAEN.XLA which is the VBA call for the add-in ANALASYS TOOLPAKm but again should not make a difference, why not try running the code to print Sheet 4 on its own after all the above code has run, just call another sub with sheet4 printout in it and see what happens. Regards, The Code Cage Team www.thecodecage.com/forumz "JLGWhiz" wrote: Error 9 occurs when the compiler cannot find the object that your code tells it to look for, in this case, worksheets(4). The only reason that it would not find worksheets(4) is that the active workbook does not contain a worksheets(4). In your code you have this snippet: Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Which appears to open other workbooks. If I am correct, then the last workbook opened will be the active workbook, and might not be the workbook that you are trying to print from. All of the other thingss that you say are working occur in your code before that that particular snippet, so it could be the cause of the problem. The message box insert was meant to verify that the correct workbook is the active workbook at the time the PrintOut command is executed. There is, otherwise, no reason why the sheet should not print. "Paulo" wrote: I am not sure if I am able to express my self correctly. ''Workbooks(Caminho). is working fine. I have been able to open "caminho", unprotect Sheet(4), change range value @ sheet(4), protect sheet(4), ,and save the workbook . when it gets to the printing part, that is literaly after all this other steps that I just named, I get the error #9. "JLGWhiz" wrote: Where you have these lines: ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut For testingk, modify to this: ''Workbooks(Caminho).Save MsgBox ActiveWorkbook.Name 'Printing Worksheets(4).PrintOut If the workbook name displayed in the message box is not the one with the sheet to copy then you will need to activate the correct workbook. "Paulo" wrote: thank you for the ilustrative explanation. but none of the them happen in my case. I have 100 workbooks with the same structure. 6 sheest, none have macro, so the probably dont have hidden sheets. other than The error happends in the printout part, all the rest of the code goes smoothly. if i were mistaken on the Sheets(n*) , i woul have Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" on the wrong sheet, and that didnt happen. i have changed this lines to doublecheck - but no joy, erro #9 ;) Worksheets("CAL.ECON.").PrintOut MsgBox "Vire A página" Worksheets("DETALHADA").PrintOut Worksheets("CAL.ECON.").Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True "Paulo" wrote: why do I get erro #9 @ printing? i have a other sub that runs the same line in the local woorkbook and ptints just fine. I thank in advance Sub Imprimidor() ' On Error GoTo Fim Dim x As Long Dim Y As Long Dim Formul As String Dim Seq As Long Dim Adit As String Dim Clie As String Dim Caminho As String Y = Range("E1") For x = Y To 27 Seq = Cells(x, "a").Value Adit = Cells(x, "b").Value Clie = Cells(x, "c").Value Range("D" & x).Value = "ok" Caminho = "\\Servidor\dados\ADITIVOS\" & Clie & "\(" _ & Seq & ") " & Clie & " aditivo " & Adit & " -TC- 1008 - N.xls" Debug.Print Caminho Workbooks.Open Caminho ActiveWorkbook.UnprotectSharing If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess 'If ActiveWorkbook.ProtectStructure = True Then Sheets(4).Unprotect "CECAPF" Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)" Workbooks.Open(Filename:= _ "C:\Arquivos de programas\Microsoft Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _ ).RunAutoMacros Which:=xlAutoOpen Calculate 'Sheet1.Range("c2").Select ''Workbooks(Caminho).Save 'Printing Worksheets(4).PrintOut MsgBox "Vire A página" Worksheets(2).PrintOut Worksheets(4).Protect "CECAPF" Worksheets(1).Range("c2").Select 'Workbooks(Caminho).Save Workbooks(Caminho).Close SaveChanges:=True Next end sub |
| All times are GMT +1. The time now is 03:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com