Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] loop, problem of 101'st iteration
hello,
i wrote such a code: Sub All_creation() Application.ScreenUpdating = False Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Dim PPObj As Object Set PPObj = CreateObject("PowerPoint.application") Sheets("ID").Cells(43, 11).Value = 3 a = Range("M50").Value For i = 1 To a 'a = 140 (LICZBA ITERACJI) Sheets("ID").Cells(50, 11).Value = i Range("L50").Formula = "=LEFT(INDEX(etykiety!R4C7:R300C7,RC[-1],1),5)" Range("M50").Formula = "=ROWS(etykiety!R4C7:R300C7)-COUNTBLANK(etykiety!R4C7:R300C7)" Range("N50").Formula = "=IF(R50C12="""","""",INDEX(R3C2:R602C2,MATCH(MID( LEFT(R50C12,4)&""0"",1,5),R3C6:R602C6,0),1))" Range("O50").Value = "0" Range("P50").Formula = "=IF(R50C12="""","""",MID(R50C12,5,1))" Sheets("ID").Cells(3, 8).Value = Sheets("ID").Cells(50, 15).Value 'dopelnienie Sheets("ID").Cells(3, 13).Value = Sheets("ID").Cells(50, 14).Value 'main Sheets("ID").Cells(3, 14).Value = Sheets("ID").Cells(50, 16).Value 'klasa Calculate '### PRN ### With PPObj ' .presentations.Add .presentations.Open Filename:="F:\Analizy ISI\pl\AnBrMan.ppt" .Visible = True .Run "AnBrMan.ppt!UpdateMode" .presentations("AnBrMan.ppt").Save With PPObj.ActivePresentation.PrintOptions .PrintInBackground = msoTrue .RangeType = ppPrintAll .Collate = msoTrue .PrintColorType = ppPrintColor .ActivePrinter = "Adobe PDF" End With PPObj.ActivePresentation.PrintOut PrintToFile:="F:\Analizy ISI\pl\files\F-pdf\K\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn" .presentations("AnBrMan.ppt").Close End With Next i Application.ScreenUpdating = True End Sub key parameter in module is 'a' (it is number of iteration in loop) unfortunately when 'i' equals 101 a module stops with error (error concerns module in powerpoint document): ..Run "AnBrMan.ppt!UpdateMode" best regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] loop, problem of 101'st iteration
Have you run this with a set to a smaller number? Does it run, and do
everything that you expect it to? Does it work when a is 99, 100, and 101? I'm just confirming that it's at exactly a=101 that there is a problem, and not just at a figure somewhere near there. If I read your message (and the macro) right, then a PowerPoint macro gets called inside the loop, and that's where the Excel macro is stopping. Does the PowerPoint macro use the variable i, or the worksheet range Sheets("ID").Cells(50, 11), or any other counter that's driven by the loops? If it does, then does it do something with that number that might be a problem when it hits a 3 digit number? When Excel starts the PowerPoint macro running, does Excel wait for that to finish before running the next code line (.presentations("AnBrMan.ppt").Save)? If not, it could be related to that - PowerPoint attempting to save while a PP macro is running. I'm not sure why it would wait until loop 101 to complain, though. If you manually set sheet ID Cell K50 (is that the one that i gets written to?) to 101, is everything OK in the spreadsheet? Does i=101 push any filenames or vba strings over 255 characters? In Excel 2003, there are still some issues in VBA with long strings, I don't know about Excel 2007. You're also printing to a pdf file each loop. There's nothing to make it pause after sending the print job, so you might be hitting a limitation on the print spooler when too many jobs are spooled. But I can't see why that would make it stop at the PowerPoint macro line, so I doubt that's the problem. -- If a post answers your question, please mark it as the answer. "God Itself" wrote: hello, i wrote such a code: Sub All_creation() Application.ScreenUpdating = False Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Dim PPObj As Object Set PPObj = CreateObject("PowerPoint.application") Sheets("ID").Cells(43, 11).Value = 3 a = Range("M50").Value For i = 1 To a 'a = 140 (LICZBA ITERACJI) Sheets("ID").Cells(50, 11).Value = i Range("L50").Formula = "=LEFT(INDEX(etykiety!R4C7:R300C7,RC[-1],1),5)" Range("M50").Formula = "=ROWS(etykiety!R4C7:R300C7)-COUNTBLANK(etykiety!R4C7:R300C7)" Range("N50").Formula = "=IF(R50C12="""","""",INDEX(R3C2:R602C2,MATCH(MID( LEFT(R50C12,4)&""0"",1,5),R3C6:R602C6,0),1))" Range("O50").Value = "0" Range("P50").Formula = "=IF(R50C12="""","""",MID(R50C12,5,1))" Sheets("ID").Cells(3, 8).Value = Sheets("ID").Cells(50, 15).Value 'dopelnienie Sheets("ID").Cells(3, 13).Value = Sheets("ID").Cells(50, 14).Value 'main Sheets("ID").Cells(3, 14).Value = Sheets("ID").Cells(50, 16).Value 'klasa Calculate '### PRN ### With PPObj ' .presentations.Add .presentations.Open Filename:="F:\Analizy ISI\pl\AnBrMan.ppt" .Visible = True .Run "AnBrMan.ppt!UpdateMode" .presentations("AnBrMan.ppt").Save With PPObj.ActivePresentation.PrintOptions .PrintInBackground = msoTrue .RangeType = ppPrintAll .Collate = msoTrue .PrintColorType = ppPrintColor .ActivePrinter = "Adobe PDF" End With PPObj.ActivePresentation.PrintOut PrintToFile:="F:\Analizy ISI\pl\files\F-pdf\K\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn" .presentations("AnBrMan.ppt").Close End With Next i Application.ScreenUpdating = True End Sub key parameter in module is 'a' (it is number of iteration in loop) unfortunately when 'i' equals 101 a module stops with error (error concerns module in powerpoint document): ..Run "AnBrMan.ppt!UpdateMode" best regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] loop, problem of 101'st iteration
Hi,
1. yes, it is always after 100 loops, even i start loop from 1 to 80 and then second loop from 1 to 50 then i get problem at 21'st in 2nd loop 2. there are no common variables. macro in powerpoint is as follows: Sub UpdateMode() Dim lCtrA As Integer Dim oPres As Object 'Presentation Dim oSld As Slide Set oPres = ActivePresentation With oPres ' Process shapes on the slides For Each oSld In .Slides Call UpdtLinks(oSld) Next ' Process shapes on the slides masters If Val(Application.Version) 9 Then 'For versions 2002 and later with multiple master support For lCtrA = 1 To .Designs.Count If .Designs(lCtrA).HasTitleMaster Then Call UpdtLinks(.Designs(lCtrA).TitleMaster) Else Call UpdtLinks(.Designs(lCtrA).SlideMaster) End If Next Else ' Version 97/2000 Call UpdtLinks(.SlideMaster) If .HasTitleMaster Then Call UpdtLinks(.TitleMaster) End If End If End With End Sub Sub UpdtLinks(oSlideOrMaster As Object) Dim oShp As PowerPoint.Shape For Each oShp In oSlideOrMaster.Shapes If oShp.Type = msoLinkedOLEObject Then 'update links oShp.LinkFormat.Update End If Next oShp End Sub 3. any application does not wait for another. but i'm only 99% sure 4. no function returns more than 255 character result. spreadsheet seems to be ok i'll paste error msg today evening regards Użytkownik "Cringing Dragon" napisał w wiadomości ... Have you run this with a set to a smaller number? Does it run, and do everything that you expect it to? Does it work when a is 99, 100, and 101? I'm just confirming that it's at exactly a=101 that there is a problem, and not just at a figure somewhere near there. If I read your message (and the macro) right, then a PowerPoint macro gets called inside the loop, and that's where the Excel macro is stopping. Does the PowerPoint macro use the variable i, or the worksheet range Sheets("ID").Cells(50, 11), or any other counter that's driven by the loops? If it does, then does it do something with that number that might be a problem when it hits a 3 digit number? When Excel starts the PowerPoint macro running, does Excel wait for that to finish before running the next code line (.presentations("AnBrMan.ppt").Save)? If not, it could be related to that - PowerPoint attempting to save while a PP macro is running. I'm not sure why it would wait until loop 101 to complain, though. If you manually set sheet ID Cell K50 (is that the one that i gets written to?) to 101, is everything OK in the spreadsheet? Does i=101 push any filenames or vba strings over 255 characters? In Excel 2003, there are still some issues in VBA with long strings, I don't know about Excel 2007. You're also printing to a pdf file each loop. There's nothing to make it pause after sending the print job, so you might be hitting a limitation on the print spooler when too many jobs are spooled. But I can't see why that would make it stop at the PowerPoint macro line, so I doubt that's the problem. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] loop, problem of 101'st iteration
Error (during 101 loop) sounds:
Run-time error '-2147467259 (8004005)': Automation Error Undefined error ' translation from Polish and when debug is pressed following part of code (given in first message in this topic) is marked: PPObj.ActivePresentation.PrintOut PrintToFile:="F:\AnalizyISI\pl\files\F-pdf\K\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn" regards Użytkownik "God Itself" napisał w wiadomości ... Hi, 1. yes, it is always after 100 loops, even i start loop from 1 to 80 and then second loop from 1 to 50 then i get problem at 21'st in 2nd loop 2. there are no common variables. macro in powerpoint is as follows: Sub UpdateMode() Dim lCtrA As Integer Dim oPres As Object 'Presentation Dim oSld As Slide Set oPres = ActivePresentation With oPres ' Process shapes on the slides For Each oSld In .Slides Call UpdtLinks(oSld) Next ' Process shapes on the slides masters If Val(Application.Version) 9 Then 'For versions 2002 and later with multiple master support For lCtrA = 1 To .Designs.Count If .Designs(lCtrA).HasTitleMaster Then Call UpdtLinks(.Designs(lCtrA).TitleMaster) Else Call UpdtLinks(.Designs(lCtrA).SlideMaster) End If Next Else ' Version 97/2000 Call UpdtLinks(.SlideMaster) If .HasTitleMaster Then Call UpdtLinks(.TitleMaster) End If End If End With End Sub Sub UpdtLinks(oSlideOrMaster As Object) Dim oShp As PowerPoint.Shape For Each oShp In oSlideOrMaster.Shapes If oShp.Type = msoLinkedOLEObject Then 'update links oShp.LinkFormat.Update End If Next oShp End Sub 3. any application does not wait for another. but i'm only 99% sure 4. no function returns more than 255 character result. spreadsheet seems to be ok i'll paste error msg today evening regards Użytkownik "Cringing Dragon" napisał w wiadomości ... Have you run this with a set to a smaller number? Does it run, and do everything that you expect it to? Does it work when a is 99, 100, and 101? I'm just confirming that it's at exactly a=101 that there is a problem, and not just at a figure somewhere near there. If I read your message (and the macro) right, then a PowerPoint macro gets called inside the loop, and that's where the Excel macro is stopping. Does the PowerPoint macro use the variable i, or the worksheet range Sheets("ID").Cells(50, 11), or any other counter that's driven by the loops? If it does, then does it do something with that number that might be a problem when it hits a 3 digit number? When Excel starts the PowerPoint macro running, does Excel wait for that to finish before running the next code line (.presentations("AnBrMan.ppt").Save)? If not, it could be related to that - PowerPoint attempting to save while a PP macro is running. I'm not sure why it would wait until loop 101 to complain, though. If you manually set sheet ID Cell K50 (is that the one that i gets written to?) to 101, is everything OK in the spreadsheet? Does i=101 push any filenames or vba strings over 255 characters? In Excel 2003, there are still some issues in VBA with long strings, I don't know about Excel 2007. You're also printing to a pdf file each loop. There's nothing to make it pause after sending the print job, so you might be hitting a limitation on the print spooler when too many jobs are spooled. But I can't see why that would make it stop at the PowerPoint macro line, so I doubt that's the problem. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] loop, problem of 101'st iteration
OK. Let's see if we can narrow it down by a process of elimination.
As it's stopping on the PrintOut line, can you do the following: Declare a string variable (I'll use strFile). On the line just before the PrintOut line, define that variable as strFile = "F:\AnalizyISI\pl\files\F-pdf\K\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn" (ie exactly what the PrintToFile is printing). Change the PrintToFile so it uses that variable. I don't know how well you know the VB editor, so I apologise if I'm telling you stuff you already know. In the Visual Basic editor in Excel, set a Watch to break on i=101, and also set a normal watch on the strFile. Then run the macro (if you haven't used Watch before, see VBE help. What that will do is break the code when you get to i = 101. When it stops, step through the code ([F8] key). Keep an eye on the watched string variable. Step all the way through. When you get to the line that defines strFile, how does it change? Does it still look like a reasonable file name, or has it been truncated or done something else odd? If it looks OK, keep going. When you get to the PrintOut line (where I assume it will fail), what error does it give? It'll probably be the same "undefined error", which isn't very helpful. If that doesn't give you any clues, then try adding a line after the For statement that's If i99 Then Application.ScreenUpdating = True so that you can have a look at what's going on in the spreadsheet at that point. The re-run (with the watch), when it stops at 1=101 then have a good look at the spreadsheet, especially at ID!H3 to P3, and step through the macro. As it does it's calculations - do any of those cells change to unexpected values? I'm not sure how this would make it stop at i=101 when it runs until then, but I'm pretty sure you're right that the Excel macro will not wait for the PowerPoint macro to finish before it continues. So the PowerPoint macro could still be running when you try to print the presentation. That could cause a conflict - PowerPoint is busy when the PrintOut command is sent. Also, just confirm that your "For i = 1 to a" is in the right place in you macro. "a" is what's in ID!M50, but then just after your "For", you redefine M50 on the active sheet (I assume that's sheet ID, but it might not be, so if it's not then this comment is irrelevant) to be the number of non-blank cells in a range on another sheet. So should that definition of "a" happen BEFORE your For statement instead of after it? -- If a post answers your question, please mark it as the answer. "God Itself" wrote: Error (during 101 loop) sounds: Run-time error '-2147467259 (8004005)': Automation Error Undefined error ' translation from Polish and when debug is pressed following part of code (given in first message in this topic) is marked: PPObj.ActivePresentation.PrintOut PrintToFile:="F:\AnalizyISI\pl\files\F-pdf\K\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn" regards Użytkownik "God Itself" napisał w wiadomości ... Hi, 1. yes, it is always after 100 loops, even i start loop from 1 to 80 and then second loop from 1 to 50 then i get problem at 21'st in 2nd loop 2. there are no common variables. macro in powerpoint is as follows: Sub UpdateMode() Dim lCtrA As Integer Dim oPres As Object 'Presentation Dim oSld As Slide Set oPres = ActivePresentation With oPres ' Process shapes on the slides For Each oSld In .Slides Call UpdtLinks(oSld) Next ' Process shapes on the slides masters If Val(Application.Version) 9 Then 'For versions 2002 and later with multiple master support For lCtrA = 1 To .Designs.Count If .Designs(lCtrA).HasTitleMaster Then Call UpdtLinks(.Designs(lCtrA).TitleMaster) Else Call UpdtLinks(.Designs(lCtrA).SlideMaster) End If Next Else ' Version 97/2000 Call UpdtLinks(.SlideMaster) If .HasTitleMaster Then Call UpdtLinks(.TitleMaster) End If End If End With End Sub Sub UpdtLinks(oSlideOrMaster As Object) Dim oShp As PowerPoint.Shape For Each oShp In oSlideOrMaster.Shapes If oShp.Type = msoLinkedOLEObject Then 'update links oShp.LinkFormat.Update End If Next oShp End Sub 3. any application does not wait for another. but i'm only 99% sure 4. no function returns more than 255 character result. spreadsheet seems to be ok i'll paste error msg today evening regards Użytkownik "Cringing Dragon" napisał w wiadomości ... Have you run this with a set to a smaller number? Does it run, and do everything that you expect it to? Does it work when a is 99, 100, and 101? I'm just confirming that it's at exactly a=101 that there is a problem, and not just at a figure somewhere near there. If I read your message (and the macro) right, then a PowerPoint macro gets called inside the loop, and that's where the Excel macro is stopping. Does the PowerPoint macro use the variable i, or the worksheet range Sheets("ID").Cells(50, 11), or any other counter that's driven by the loops? If it does, then does it do something with that number that might be a problem when it hits a 3 digit number? When Excel starts the PowerPoint macro running, does Excel wait for that to finish before running the next code line (.presentations("AnBrMan.ppt").Save)? If not, it could be related to that - PowerPoint attempting to save while a PP macro is running. I'm not sure why it would wait until loop 101 to complain, though. If you manually set sheet ID Cell K50 (is that the one that i gets written to?) to 101, is everything OK in the spreadsheet? Does i=101 push any filenames or vba strings over 255 characters? In Excel 2003, there are still some issues in VBA with long strings, I don't know about Excel 2007. You're also printing to a pdf file each loop. There's nothing to make it pause after sending the print job, so you might be hitting a limitation on the print spooler when too many jobs are spooled. But I can't see why that would make it stop at the PowerPoint macro line, so I doubt that's the problem. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] loop, problem of 101'st iteration
God Itself -
Excel's Help for Tools | Options | Calculation says "Unless you specify otherwise, Excel stops after 100 iterations ..." - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "God Itself" wrote in message ... hello, i wrote such a code: Sub All_creation() Application.ScreenUpdating = False Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Dim PPObj As Object Set PPObj = CreateObject("PowerPoint.application") Sheets("ID").Cells(43, 11).Value = 3 a = Range("M50").Value For i = 1 To a 'a = 140 (LICZBA ITERACJI) Sheets("ID").Cells(50, 11).Value = i Range("L50").Formula = "=LEFT(INDEX(etykiety!R4C7:R300C7,RC[-1],1),5)" Range("M50").Formula = "=ROWS(etykiety!R4C7:R300C7)-COUNTBLANK(etykiety!R4C7:R300C7)" Range("N50").Formula = "=IF(R50C12="""","""",INDEX(R3C2:R602C2,MATCH(MID( LEFT(R50C12,4)&""0"",1,5),R3C6:R602C6,0),1))" Range("O50").Value = "0" Range("P50").Formula = "=IF(R50C12="""","""",MID(R50C12,5,1))" Sheets("ID").Cells(3, 8).Value = Sheets("ID").Cells(50, 15).Value 'dopelnienie Sheets("ID").Cells(3, 13).Value = Sheets("ID").Cells(50, 14).Value 'main Sheets("ID").Cells(3, 14).Value = Sheets("ID").Cells(50, 16).Value 'klasa Calculate '### PRN ### With PPObj ' .presentations.Add .presentations.Open Filename:="F:\Analizy ISI\pl\AnBrMan.ppt" .Visible = True .Run "AnBrMan.ppt!UpdateMode" .presentations("AnBrMan.ppt").Save With PPObj.ActivePresentation.PrintOptions .PrintInBackground = msoTrue .RangeType = ppPrintAll .Collate = msoTrue .PrintColorType = ppPrintColor .ActivePrinter = "Adobe PDF" End With PPObj.ActivePresentation.PrintOut PrintToFile:="F:\Analizy ISI\pl\files\F-pdf\K\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn" .presentations("AnBrMan.ppt").Close End With Next i Application.ScreenUpdating = True End Sub key parameter in module is 'a' (it is number of iteration in loop) unfortunately when 'i' equals 101 a module stops with error (error concerns module in powerpoint document): .Run "AnBrMan.ppt!UpdateMode" best regards |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] loop, problem of 101'st iteration
Thanks for help.
i tracked macro in break mode. everything seems to be ok.. strFile looks reasonable (=101.PRN) but there is something wrong with powerpoint application. when i try to get into powerpoin VB editor i get msg: out of memory, what means: Out of memory (Error 7) More memory was required than is available, or a 64K segment boundary was encountered. This error has the following causes and solutions: a.. You have too many applications, documents, or source files open. Close any unnecessary applications, documents, or source files that are open. b.. You have a module or procedure that's too large. Break large modules or procedures into smaller ones. This doesn't save memory, but it can prevent hitting 64K segment boundaries. c.. You are running Microsoft Windows in standard mode. Restart Microsoft Windows in enhanced mode. d.. You are running Microsoft Windows in enhanced mode, but have run out of virtual memory. Increase virtual memory by freeing some disk space, or at least ensure that some space is available. e.. You have terminate-and-stay-resident programs running. Eliminate terminate-and-stay-resident programs. f.. You have many device drivers loaded. Eliminate unnecessary device drivers. g.. You have run out of space for Public variables. Reduce the number of Public variables. For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh). anyway i have also problem with closing powerpoint document and/or application (i can only kill this process) when i open AnBrMan.ppt again then there is no problem to open powerpoint VB Editor there is also one clue thatthere must be problem with powerpoint (not excel).. if i start loop from 20 then i get errorif i =121.. so error occurs always after 100 loops.. Użytkownik "Cringing Dragon" napisał w wiadomości ... OK. Let's see if we can narrow it down by a process of elimination. As it's stopping on the PrintOut line, can you do the following: Declare a string variable (I'll use strFile). On the line just before the PrintOut line, define that variable as strFile = "F:\AnalizyISI\pl\files\F-pdf\K\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn" (ie exactly what the PrintToFile is printing). Change the PrintToFile so it uses that variable. I don't know how well you know the VB editor, so I apologise if I'm telling you stuff you already know. In the Visual Basic editor in Excel, set a Watch to break on i=101, and also set a normal watch on the strFile. Then run the macro (if you haven't used Watch before, see VBE help. What that will do is break the code when you get to i = 101. When it stops, step through the code ([F8] key). Keep an eye on the watched string variable. Step all the way through. When you get to the line that defines strFile, how does it change? Does it still look like a reasonable file name, or has it been truncated or done something else odd? If it looks OK, keep going. When you get to the PrintOut line (where I assume it will fail), what error does it give? It'll probably be the same "undefined error", which isn't very helpful. If that doesn't give you any clues, then try adding a line after the For statement that's If i99 Then Application.ScreenUpdating = True so that you can have a look at what's going on in the spreadsheet at that point. The re-run (with the watch), when it stops at 1=101 then have a good look at the spreadsheet, especially at ID!H3 to P3, and step through the macro. As it does it's calculations - do any of those cells change to unexpected values? I'm not sure how this would make it stop at i=101 when it runs until then, but I'm pretty sure you're right that the Excel macro will not wait for the PowerPoint macro to finish before it continues. So the PowerPoint macro could still be running when you try to print the presentation. That could cause a conflict - PowerPoint is busy when the PrintOut command is sent. Also, just confirm that your "For i = 1 to a" is in the right place in you macro. "a" is what's in ID!M50, but then just after your "For", you redefine M50 on the active sheet (I assume that's sheet ID, but it might not be, so if it's not then this comment is irrelevant) to be the number of non-blank cells in a range on another sheet. So should that definition of "a" happen BEFORE your For statement instead of after it? -- If a post answers your question, please mark it as the answer. "God Itself" wrote: Error (during 101 loop) sounds: Run-time error '-2147467259 (8004005)': Automation Error Undefined error ' translation from Polish and when debug is pressed following part of code (given in first message in this topic) is marked: PPObj.ActivePresentation.PrintOut PrintToFile:="F:\AnalizyISI\pl\files\F-pdf\K\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _ Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn" regards Użytkownik "God Itself" napisał w wiadomości ... Hi, 1. yes, it is always after 100 loops, even i start loop from 1 to 80 and then second loop from 1 to 50 then i get problem at 21'st in 2nd loop 2. there are no common variables. macro in powerpoint is as follows: Sub UpdateMode() Dim lCtrA As Integer Dim oPres As Object 'Presentation Dim oSld As Slide Set oPres = ActivePresentation With oPres ' Process shapes on the slides For Each oSld In .Slides Call UpdtLinks(oSld) Next ' Process shapes on the slides masters If Val(Application.Version) 9 Then 'For versions 2002 and later with multiple master support For lCtrA = 1 To .Designs.Count If .Designs(lCtrA).HasTitleMaster Then Call UpdtLinks(.Designs(lCtrA).TitleMaster) Else Call UpdtLinks(.Designs(lCtrA).SlideMaster) End If Next Else ' Version 97/2000 Call UpdtLinks(.SlideMaster) If .HasTitleMaster Then Call UpdtLinks(.TitleMaster) End If End If End With End Sub Sub UpdtLinks(oSlideOrMaster As Object) Dim oShp As PowerPoint.Shape For Each oShp In oSlideOrMaster.Shapes If oShp.Type = msoLinkedOLEObject Then 'update links oShp.LinkFormat.Update End If Next oShp End Sub 3. any application does not wait for another. but i'm only 99% sure 4. no function returns more than 255 character result. spreadsheet seems to be ok i'll paste error msg today evening regards Użytkownik "Cringing Dragon" napisał w wiadomości ... Have you run this with a set to a smaller number? Does it run, and do everything that you expect it to? Does it work when a is 99, 100, and 101? I'm just confirming that it's at exactly a=101 that there is a problem, and not just at a figure somewhere near there. If I read your message (and the macro) right, then a PowerPoint macro gets called inside the loop, and that's where the Excel macro is stopping. Does the PowerPoint macro use the variable i, or the worksheet range Sheets("ID").Cells(50, 11), or any other counter that's driven by the loops? If it does, then does it do something with that number that might be a problem when it hits a 3 digit number? When Excel starts the PowerPoint macro running, does Excel wait for that to finish before running the next code line (.presentations("AnBrMan.ppt").Save)? If not, it could be related to that - PowerPoint attempting to save while a PP macro is running. I'm not sure why it would wait until loop 101 to complain, though. If you manually set sheet ID Cell K50 (is that the one that i gets written to?) to 101, is everything OK in the spreadsheet? Does i=101 push any filenames or vba strings over 255 characters? In Excel 2003, there are still some issues in VBA with long strings, I don't know about Excel 2007. You're also printing to a pdf file each loop. There's nothing to make it pause after sending the print job, so you might be hitting a limitation on the print spooler when too many jobs are spooled. But I can't see why that would make it stop at the PowerPoint macro line, so I doubt that's the problem. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Frustrating Boolean/loop iteration problem | Excel Programming | |||
Frustrating Boolean/loop iteration problem | Excel Programming | |||
Advancing to the next iteration of a loop? | Excel Programming | |||
Iteration loop | Excel Programming | |||
Iteration loop | Excel Programming |