Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
Hello All,
I've got a routine which opens 40 workbooks - copies in some data - copies out some data - then closes each workbook saving changes. Each Save takes approx 40sec due to the size of each wkbk and many calculations involved. The macro therefore takes 25mins to run. Not worrying too much about the length of time, I went ahead and wrote the macro, as I thought I'd just run it in a second open instance of Excel and still be able to do other things in another instance. Problem is that the Copying & Pasting means that even if I'm working on SQL Server, whilst this macro is running, if I try to copy and paste in other applications it seems like the routine takes over the clipboard. Sometimes if I copy/paste in another app the routine will crash - again as I think the clipboard is causing a problem. Anyone ever come across this before and know a way around it. Any help much appreciated Jason (Using Excel 2007) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
You can assign data from one range to another directly, or store it in an
array until needed Sub test() Dim rSource As Range, rDest1 As Range Set rSource = ActiveWorkbook.Worksheets(1).Range("A1:B3") rSource.Value = "data" Set rDest = ActiveWorkbook.Worksheets(2).Range("D4") With rSource Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) End With ' copy data directly rDest.Value = rSource.Value ' or store in an array until for later use, say after source wb has closed arr = rSource.Value Set rDest = ActiveWorkbook.Worksheets(2).Range("H4") With rSource Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) End With rDest.Value = arr ActiveWorkbook.Worksheets(2).Activate End Sub Probably best not to copy more than say 20-50k cells this way in one go, with larger sizes do in multiple steps Regards, Peter T wrote in message ... Hello All, I've got a routine which opens 40 workbooks - copies in some data - copies out some data - then closes each workbook saving changes. Each Save takes approx 40sec due to the size of each wkbk and many calculations involved. The macro therefore takes 25mins to run. Not worrying too much about the length of time, I went ahead and wrote the macro, as I thought I'd just run it in a second open instance of Excel and still be able to do other things in another instance. Problem is that the Copying & Pasting means that even if I'm working on SQL Server, whilst this macro is running, if I try to copy and paste in other applications it seems like the routine takes over the clipboard. Sometimes if I copy/paste in another app the routine will crash - again as I think the clipboard is causing a problem. Anyone ever come across this before and know a way around it. Any help much appreciated Jason (Using Excel 2007) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
On 17 Apr, 14:21, "Peter T" <peter_t@discussions wrote:
You can assign data from one range to another directly, or store it in an array until needed Sub test() Dim rSource As Range, rDest1 As Range * * Set rSource = ActiveWorkbook.Worksheets(1).Range("A1:B3") * * rSource.Value = "data" * * Set rDest = ActiveWorkbook.Worksheets(2).Range("D4") * * With rSource * * * * Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) * * End With * * ' copy data directly * * rDest.Value = rSource.Value * * ' or store in an array until for later use, say after source wb has closed * * arr = rSource.Value * * Set rDest = ActiveWorkbook.Worksheets(2).Range("H4") * * With rSource * * * * Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) * * End With * * rDest.Value = arr * * ActiveWorkbook.Worksheets(2).Activate End Sub Probably best not to copy more than say 20-50k cells this way in one go, with larger sizes do in multiple steps Regards, Peter wrote in message ... Hello All, I've got a routine which opens 40 workbooks - copies in some data - copies out some data - then closes each workbook saving changes. Each Save takes approx 40sec due to the size of each wkbk and many calculations involved. The macro therefore takes 25mins to run. Not worrying too much about the length of time, I went ahead and wrote the macro, as I thought I'd just run it in a second open instance of Excel and still be able to do other things in another instance. Problem is that the Copying & Pasting means that even if I'm working on SQL Server, whilst this macro is running, if I try to copy and paste in other applications it seems like the routine takes over the clipboard. Sometimes if I copy/paste in another app the routine will crash - again as I think the clipboard is causing a problem. Anyone ever come across this before and know a way around it. Any help much appreciated Jason (Using Excel 2007)- Hide quoted text - - Show quoted text - Alright Peter Brilliant - plenty for me to work on: have you used both methods previously? Which do you prefer? Which do you use in your current work? Also do you know if I could theoretically run some code from one instance of Excel and then open a second hidden instance to do all the work in, and then once all 40 wkbks have been updated the hidden instance could be closed? Any help much appreciated, Jason. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
Brilliant - plenty for me to work on: have you used both
methods previously? Which do you prefer? Which do you use in your current work? By "both methods" I assume you mean rDest.Value = rsource.Value and rDest.Value = arr If I know the source range and destination range will both be available at the same time, there's no need to assign the values to the intermediate array. Otherwise use the temporary array, eg open one wb, assign the values to the array, close that wb then open the dest' wb. As I mentioned before, probably best not to try and do a massive amount of data in one go. Also do you know if I could theoretically run some code from one instance of Excel and then open a second hidden instance to do all the work in, and then once all 40 wkbks have been updated the hidden instance could be closed? Depends how you do it. If from Inst1 you create Inst2 but control all the code from Inst1 you are not gaining anything. However you could open a code file in created Inst2 that from its Open event starts a new routine called by an OnTime macro to do all your work then close the itself and the instance. Small thing to keep in mind, when you start Inst2 make it and the opened wb visible, then you can completely release all object references in Inst1, if you don't make visible the created instance will simply quit as soon as you destroy the last object pointers to it (or one of it's wb's). Regards, Peter T wrote in message ... On 17 Apr, 14:21, "Peter T" <peter_t@discussions wrote: You can assign data from one range to another directly, or store it in an array until needed Sub test() Dim rSource As Range, rDest1 As Range Set rSource = ActiveWorkbook.Worksheets(1).Range("A1:B3") rSource.Value = "data" Set rDest = ActiveWorkbook.Worksheets(2).Range("D4") With rSource Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) End With ' copy data directly rDest.Value = rSource.Value ' or store in an array until for later use, say after source wb has closed arr = rSource.Value Set rDest = ActiveWorkbook.Worksheets(2).Range("H4") With rSource Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) End With rDest.Value = arr ActiveWorkbook.Worksheets(2).Activate End Sub Probably best not to copy more than say 20-50k cells this way in one go, with larger sizes do in multiple steps Regards, Peter wrote in message ... Hello All, I've got a routine which opens 40 workbooks - copies in some data - copies out some data - then closes each workbook saving changes. Each Save takes approx 40sec due to the size of each wkbk and many calculations involved. The macro therefore takes 25mins to run. Not worrying too much about the length of time, I went ahead and wrote the macro, as I thought I'd just run it in a second open instance of Excel and still be able to do other things in another instance. Problem is that the Copying & Pasting means that even if I'm working on SQL Server, whilst this macro is running, if I try to copy and paste in other applications it seems like the routine takes over the clipboard. Sometimes if I copy/paste in another app the routine will crash - again as I think the clipboard is causing a problem. Anyone ever come across this before and know a way around it. Any help much appreciated Jason (Using Excel 2007)- Hide quoted text - - Show quoted text - Alright Peter Brilliant - plenty for me to work on: have you used both methods previously? Which do you prefer? Which do you use in your current work? Also do you know if I could theoretically run some code from one instance of Excel and then open a second hidden instance to do all the work in, and then once all 40 wkbks have been updated the hidden instance could be closed? Any help much appreciated, Jason. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
On 17 Apr, 16:34, "Peter T" <peter_t@discussions wrote:
Brilliant - plenty for me to work on: have you used both methods previously? Which do you prefer? Which do you use in your current work? By "both methods" I assume you mean rDest.Value = rsource.Value and rDest.Value = arr If I know the source range and destination range will both be available at the same time, there's no need to assign the values to the intermediate array. Otherwise use the temporary array, eg open one wb, assign the values to the array, close that wb then open the dest' wb. As I mentioned before, probably best not to try and do a massive amount of data in one go. Also do you know if I could theoretically run some code from one instance of Excel and then open a second hidden instance to do all the work in, and then once all 40 wkbks have been updated the hidden instance could be closed? Depends how you do it. If from Inst1 you create Inst2 but control all the code from Inst1 you are not gaining anything. However you could open a code file in created Inst2 that from its Open event starts a new routine called by an OnTime macro to do all your work then close the itself and the instance. Small thing to keep in mind, when you start Inst2 make it and the opened wb visible, then you can completely release all object references in Inst1, if you don't make visible the created instance will simply quit as soon as you destroy the last object pointers to it (or one of it's wb's). Regards, Peter T wrote in message ... On 17 Apr, 14:21, "Peter T" <peter_t@discussions wrote: You can assign data from one range to another directly, or store it in an array until needed Sub test() Dim rSource As Range, rDest1 As Range Set rSource = ActiveWorkbook.Worksheets(1).Range("A1:B3") rSource.Value = "data" Set rDest = ActiveWorkbook.Worksheets(2).Range("D4") With rSource Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) End With ' copy data directly rDest.Value = rSource.Value ' or store in an array until for later use, say after source wb has closed arr = rSource.Value Set rDest = ActiveWorkbook.Worksheets(2).Range("H4") With rSource Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) End With rDest.Value = arr ActiveWorkbook.Worksheets(2).Activate End Sub Probably best not to copy more than say 20-50k cells this way in one go, with larger sizes do in multiple steps Regards, Peter wrote in message ... Hello All, I've got a routine which opens 40 workbooks - copies in some data - copies out some data - then closes each workbook saving changes. Each Save takes approx 40sec due to the size of each wkbk and many calculations involved. The macro therefore takes 25mins to run. Not worrying too much about the length of time, I went ahead and wrote the macro, as I thought I'd just run it in a second open instance of Excel and still be able to do other things in another instance. Problem is that the Copying & Pasting means that even if I'm working on SQL Server, whilst this macro is running, if I try to copy and paste in other applications it seems like the routine takes over the clipboard. Sometimes if I copy/paste in another app the routine will crash - again as I think the clipboard is causing a problem. Anyone ever come across this before and know a way around it. Any help much appreciated Jason (Using Excel 2007)- Hide quoted text - - Show quoted text - Alright Peter Brilliant - plenty for me to work on: have you used both methods previously? Which do you prefer? Which do you use in your current work? Also do you know if I could theoretically run some code from one instance of Excel and then open a second hidden instance to do all the work in, and then once all 40 wkbks have been updated the hidden instance could be closed? Any help much appreciated, Jason.- Hide quoted text - - Show quoted text - Thanks for all the help Peter - I'm working on this project. For my purposes I don't need the second instance to be visible once the routine has completed so should be ok leaving the visible property as False (unless you know of other reasons I should make the app visible). I'll add a small routine in at the end of the macro which sends me an email so that I know all has completed. Regards Jason. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
wrote in message Thanks for all the help Peter - I'm working on this project. For my purposes I don't need the second instance to be visible once the routine has completed so should be ok leaving the visible property as False (unless you know of other reasons I should make the app visible). I'll add a small routine in at the end of the macro which sends me an email so that I know all has completed. I have never thought of having my app send me an email to tell me all is done. Brilliant:-) Regards, Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
On 21 Apr, 11:44, "Peter T" <peter_t@discussions wrote:
wrote in message Thanks for all the help Peter - I'm working on this project. For my purposes I don't need the second instance to be visible once the routine has completed so should be ok leaving the visible property as False (unless you know of other reasons I should make the app visible). I'll add a small routine in at the end of the macro which sends me an email so that I know all has completed. I have never thought of having my app send me an email to tell me all is done. Brilliant:-) Regards, Peter T Hello Peter, Think an easier method than trying to send an email is just to make the application visible once all the code has run. One thing I've just encountered is that my program also uses the method PasteSpecial xlValues over some quite large ranges - is there an easy way to avoid the clipboard in these instances? Regards Jason. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
I thought the whole idea was to avoide the clipboard, following just for
ideas Sub MakeSampledata() Dim rng As Range, rCol As Range Set rng = ActiveSheet.Range("B2:J1000") For i = 1 To rng.Columns.Count rng.Columns(i).Value = i Next Call Test End Sub Sub Test() Dim nCol As Long Dim rSource As Range, rDest As Range, rCol As Range Dim wb As Workbook Set rSource = ActiveSheet.Range("B2").CurrentRegion Workbooks.Add Set rDest = ActiveSheet.Range("C3") With rSource Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) End With For Each rCol In rSource.Columns nCol = nCol + 1 rDest.Columns(nCol).Value = rCol.Value Next End Sub Regards, Peter T wrote in message ... On 21 Apr, 11:44, "Peter T" <peter_t@discussions wrote: wrote in message Thanks for all the help Peter - I'm working on this project. For my purposes I don't need the second instance to be visible once the routine has completed so should be ok leaving the visible property as False (unless you know of other reasons I should make the app visible). I'll add a small routine in at the end of the macro which sends me an email so that I know all has completed. I have never thought of having my app send me an email to tell me all is done. Brilliant:-) Regards, Peter T Hello Peter, Think an easier method than trying to send an email is just to make the application visible once all the code has run. One thing I've just encountered is that my program also uses the method PasteSpecial xlValues over some quite large ranges - is there an easy way to avoid the clipboard in these instances? Regards Jason. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
On 23 Apr, 17:54, "Peter T" <peter_t@discussions wrote:
I thought the whole idea was to avoide the clipboard, following just for ideas Sub MakeSampledata() Dim rng As Range, rCol As Range Set rng = ActiveSheet.Range("B2:J1000") For i = 1 To rng.Columns.Count rng.Columns(i).Value = i Next Call Test End Sub Sub Test() Dim nCol As Long Dim rSource As Range, rDest As Range, rCol As Range Dim wb As Workbook * * Set rSource = ActiveSheet.Range("B2").CurrentRegion * * Workbooks.Add * * Set rDest = ActiveSheet.Range("C3") * * With rSource * * * * Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) * * End With * * For Each rCol In rSource.Columns * * * * nCol = nCol + 1 * * * * rDest.Columns(nCol).Value = rCol.Value * * Next End Sub Regards, Peter T wrote in message ... On 21 Apr, 11:44, "Peter T" <peter_t@discussions wrote: wrote in message Thanks for all the help Peter - I'm working on this project. For my purposes I don't need the second instance to be visible once the routine has completed so should be ok leaving the visible property as False (unless you know of other reasons I should make the app visible). I'll add a small routine in at the end of the macro which sends me an email so that I know all has completed. I have never thought of having my app send me an email to tell me all is done. Brilliant:-) Regards, Peter T Hello Peter, Think an easier method than trying to send an email is just to make the application visible once all the code has run. One thing I've just encountered is that my program also uses the method PasteSpecial xlValues over some quite large ranges - is there an easy way to avoid the clipboard in these instances? Regards Jason.- Hide quoted text - - Show quoted text - thanks Peter - my confusion. I'd tested your initial code and it worked perfectly in replacing rng1.copy/rng2.paste. For some reason I thought I'd need something more from you to replace rng1.copy/rng2.pastespecial xlvalues .....but discovered last night that your initial code does this aswell. thanks again for all the help jason |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
On 24 Apr, 09:27, wrote:
On 23 Apr, 17:54, "Peter T" <peter_t@discussions wrote: I thought the whole idea was to avoide the clipboard, following just for ideas Sub MakeSampledata() Dim rng As Range, rCol As Range Set rng = ActiveSheet.Range("B2:J1000") For i = 1 To rng.Columns.Count rng.Columns(i).Value = i Next Call Test End Sub Sub Test() Dim nCol As Long Dim rSource As Range, rDest As Range, rCol As Range Dim wb As Workbook * * Set rSource = ActiveSheet.Range("B2").CurrentRegion * * Workbooks.Add * * Set rDest = ActiveSheet.Range("C3") * * With rSource * * * * Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) * * End With * * For Each rCol In rSource.Columns * * * * nCol = nCol + 1 * * * * rDest.Columns(nCol).Value = rCol.Value * * Next End Sub Regards, Peter T wrote in message ... On 21 Apr, 11:44, "Peter T" <peter_t@discussions wrote: wrote in message Thanks for all the help Peter - I'm working on this project. For my purposes I don't need the second instance to be visible once the routine has completed so should be ok leaving the visible property as False (unless you know of other reasons I should make the app visible). I'll add a small routine in at the end of the macro which sends me an email so that I know all has completed. I have never thought of having my app send me an email to tell me all is done. Brilliant:-) Regards, Peter T Hello Peter, Think an easier method than trying to send an email is just to make the application visible once all the code has run. One thing I've just encountered is that my program also uses the method PasteSpecial xlValues over some quite large ranges - is there an easy way to avoid the clipboard in these instances? Regards Jason.- Hide quoted text - - Show quoted text - thanks Peter - my confusion. I'd tested your initial code and it worked perfectly in replacing rng1.copy/rng2.paste. For some reason I thought I'd need something more from you to replace rng1.copy/rng2.pastespecial xlvalues .....but discovered last night that your initial code does this aswell. thanks again for all the help jason- Hide quoted text - - Show quoted text - Ended up with this in App1: '================== Dim apXL As Excel.Application Dim wbXL As Excel.Workbook Dim rSource As Range Dim rDest1 As Range Sub OpenEXLAndWB() Set apXL = New Excel.Application apXL.Visible = True apXL.Workbooks.Open "C:\Excel Experiment 0.1\Data Feed.xlsm" Set apXL = Nothing End Sub '========================== and then in the workbook Data Feed I've got this event proc: '========================== Private Sub Workbook_Open() Dim ActionTime As Date ActionTime = Now + TimeValue("00:00:10") Application.OnTime ActionTime, "CopyData" End Sub '========================== the CopyData macro looks like this: '========================== Private wbDestination As Workbook Private rSource As Range Private rDest As Range Sub CopyData() Application.ScreenUpdating = False Set wbDestination = Workbooks.Open("C:\Excel Experiment 0.1\Destination.xlsx") ' copy data directly Set rSource = ThisWorkbook.Worksheets(1).Range("B3:D5") Set rDest = wbDestination.Worksheets(1).Range("B3") With rSource Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) End With rDest.Value = rSource.Value Set rSource = Nothing Set rDest = Nothing wbDestination.Close True Set wbDestination = Nothing Application.ScreenUpdating = True End Sub '========================= the above is a model really for a much bigger routine: what do you think? any major problems / improvements? any help much appreciated Jason |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
That looks fine. I might change a few things but only as a matter of style
and organization, on that basis some brief comments - If the objective is to batch process (as you said in the OP) I might arrange something like this Sub A() set wbS = ThisWorkbook get an array or collection of wb names to open here disable screenpdating on error resume next for i = 1 to ... set wbD = nothing set wbD = workbooks(arr(i)) if not wb is nothing then If CopyData(wbS, wbD) = False then write log failed to copy to wbD else ' report failed to open the wb end if next re-enable screenupdating clean up advise all done End sub Function CopyData(wbSource as workbook, wbDest as Workbook) as boolean On error goto errH copy stuff CopyData = True Exit Function errH: End sub It's not necessary to explicity destroy object variables declared at procedure level as you are doing unless you need to for other reasons (like in my example above, but no harm to do so. You said something about doing a lot of calculations in each wb, depending on what it might be worth disbaling calculation and doing a recalc just before the save (but reset in the clean up). Regards, Peter T wrote in message ... Ended up with this in App1: '================== Dim apXL As Excel.Application Dim wbXL As Excel.Workbook Dim rSource As Range Dim rDest1 As Range Sub OpenEXLAndWB() Set apXL = New Excel.Application apXL.Visible = True apXL.Workbooks.Open "C:\Excel Experiment 0.1\Data Feed.xlsm" Set apXL = Nothing End Sub '========================== and then in the workbook Data Feed I've got this event proc: '========================== Private Sub Workbook_Open() Dim ActionTime As Date ActionTime = Now + TimeValue("00:00:10") Application.OnTime ActionTime, "CopyData" End Sub '========================== the CopyData macro looks like this: '========================== Private wbDestination As Workbook Private rSource As Range Private rDest As Range Sub CopyData() Application.ScreenUpdating = False Set wbDestination = Workbooks.Open("C:\Excel Experiment 0.1\Destination.xlsx") ' copy data directly Set rSource = ThisWorkbook.Worksheets(1).Range("B3:D5") Set rDest = wbDestination.Worksheets(1).Range("B3") With rSource Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) End With rDest.Value = rSource.Value Set rSource = Nothing Set rDest = Nothing wbDestination.Close True Set wbDestination = Nothing Application.ScreenUpdating = True End Sub '========================= the above is a model really for a much bigger routine: what do you think? any major problems / improvements? any help much appreciated Jason |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
On 26 Apr, 10:19, "Peter T" <peter_t@discussions wrote:
That looks fine. I might change a few things but only as a matter of style and organization, on that basis some brief comments - If the objective is to batch process (as you said in the OP) I might arrange something like this Sub A() set wbS = ThisWorkbook get an array or collection of wb names to open here disable screenpdating on error resume next for i = 1 to ... set wbD = nothing set wbD = workbooks(arr(i)) if not wb is nothing then If CopyData(wbS, wbD) = False then * *write log failed to copy to wbD else ' report failed to open the wb end if next re-enable screenupdating clean up advise all done End sub Function CopyData(wbSource as workbook, wbDest as Workbook) as boolean On error goto errH copy stuff CopyData = True Exit Function errH: End sub It's not necessary to explicity destroy object variables declared at procedure level as you are doing unless you need to for other reasons (like in my example above, but no harm to do so. You said something about doing a lot of calculations in each wb, depending on what it might be worth disbaling calculation and doing a recalc just before the save (but reset in the clean up). Regards, Peter T wrote in message ... Ended up with this in App1: '================== Dim apXL As Excel.Application Dim wbXL As Excel.Workbook Dim rSource As Range Dim rDest1 As Range Sub OpenEXLAndWB() * * Set apXL = New Excel.Application * * apXL.Visible = True * * apXL.Workbooks.Open "C:\Excel Experiment 0.1\Data Feed.xlsm" * * Set apXL = Nothing End Sub '========================== and then in the workbook Data Feed I've got this event proc: '========================== Private Sub Workbook_Open() Dim ActionTime As Date ActionTime = Now + TimeValue("00:00:10") Application.OnTime ActionTime, "CopyData" End Sub '========================== the CopyData macro looks like this: '========================== Private wbDestination As Workbook Private rSource As Range Private rDest As Range Sub CopyData() Application.ScreenUpdating = False * * Set wbDestination = Workbooks.Open("C:\Excel Experiment 0.1\Destination.xlsx") * * * * ' copy data directly * * * * Set rSource = ThisWorkbook.Worksheets(1).Range("B3:D5") * * * * Set rDest = wbDestination.Worksheets(1).Range("B3") * * * * * * With rSource * * * * * * * * Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) * * * * * * End With * * * * * * rDest.Value = rSource.Value * * * * Set rSource = Nothing * * * * Set rDest = Nothing * * * * wbDestination.Close True * * Set wbDestination = Nothing Application.ScreenUpdating = True End Sub '========================= the above is a model really for a much bigger routine: what do you think? any major problems / improvements? any help much appreciated Jason Thanks for all the advice Peter - much appreciated; I'll be in touch in terms of whether it finally succeeds or hits problems J |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
On 27 Apr, 10:21, wrote:
On 26 Apr, 10:19, "Peter T" <peter_t@discussions wrote: That looks fine. I might change a few things but only as a matter of style and organization, on that basis some brief comments - If the objective is to batch process (as you said in the OP) I might arrange something like this Sub A() set wbS = ThisWorkbook get an array or collection of wb names to open here disable screenpdating on error resume next for i = 1 to ... set wbD = nothing set wbD = workbooks(arr(i)) if not wb is nothing then If CopyData(wbS, wbD) = False then * *write log failed to copy to wbD else ' report failed to open the wb end if next re-enable screenupdating clean up advise all done End sub Function CopyData(wbSource as workbook, wbDest as Workbook) as boolean On error goto errH copy stuff CopyData = True Exit Function errH: End sub It's not necessary to explicity destroy object variables declared at procedure level as you are doing unless you need to for other reasons (like in my example above, but no harm to do so. You said something about doing a lot of calculations in each wb, depending on what it might be worth disbaling calculation and doing a recalc just before the save (but reset in the clean up). Regards, Peter T wrote in message .... Ended up with this in App1: '================== Dim apXL As Excel.Application Dim wbXL As Excel.Workbook Dim rSource As Range Dim rDest1 As Range Sub OpenEXLAndWB() * * Set apXL = New Excel.Application * * apXL.Visible = True * * apXL.Workbooks.Open "C:\Excel Experiment 0.1\Data Feed.xlsm" * * Set apXL = Nothing End Sub '========================== and then in the workbook Data Feed I've got this event proc: '========================== Private Sub Workbook_Open() Dim ActionTime As Date ActionTime = Now + TimeValue("00:00:10") Application.OnTime ActionTime, "CopyData" End Sub '========================== the CopyData macro looks like this: '========================== Private wbDestination As Workbook Private rSource As Range Private rDest As Range Sub CopyData() Application.ScreenUpdating = False * * Set wbDestination = Workbooks.Open("C:\Excel Experiment 0.1\Destination.xlsx") * * * * ' copy data directly * * * * Set rSource = ThisWorkbook.Worksheets(1).Range("B3:D5") * * * * Set rDest = wbDestination.Worksheets(1).Range("B3") * * * * * * With rSource * * * * * * * * Set rDest = rDest.Resize(.Rows.Count, .Columns.Count) * * * * * * End With * * * * * * rDest.Value = rSource.Value * * * * Set rSource = Nothing * * * * Set rDest = Nothing * * * * wbDestination.Close True * * Set wbDestination = Nothing Application.ScreenUpdating = True End Sub '========================= the above is a model really for a much bigger routine: what do you think? any major problems / improvements? any help much appreciated Jason Thanks for all the advice Peter - much appreciated; I'll be in touch in terms of whether it finally succeeds or hits problems J- Hide quoted text - - Show quoted text - So far the results are amazing - when I was using Copy/paste in a single instnace of Excel the running time was 30mins - now running it without the clipboard, in a second instance of excel the running time is around 5mins ! ....not sure where the saving has been made, but thanks again. J |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Routine Interfering with Clipboard
Sounds like a worthwhile gain and thanks for the feedback. I suspect though
this has more to do with re-working the copy/paste method so as not use the clipboard (and perhaps in chunks?) rather than doing the work in a separate instance. Could you give some details of the real life data and size you are dealing with. Also what version, is any recalc involved and if so is that (dis)enabled throughout. If Excel 2007 did you previously do similar in an earlier version and if so any significant differences (with the original method). Regards, Peter T wrote in message ... So far the results are amazing - when I was using Copy/paste in a single instnace of Excel the running time was 30mins - now running it without the clipboard, in a second instance of excel the running time is around 5mins ! ....not sure where the saving has been made, but thanks again. J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Puzzled about javascript interfering with functions and formatting | Excel Discussion (Misc queries) | |||
Changing Cell Properties Interfering with Clipboard | Excel Programming | |||
Transfer clipboard from Task pane clipboard(office?) content to Excel (windows?) clipboard? | Excel Programming | |||
Office XP SP3 interfering w/ Excel | Excel Discussion (Misc queries) | |||
Custom Function interfering with vba | Excel Programming |