Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
Hi,
I'm running Office 2016 on Windows 7 (64 bit OS with 8gb RAM), and I've got a fairly large VBA macro that I run for my departments workload management tool. I've got 8 global variables, a main module, and 7 other sub-routines are called as the overall macro is processed. We've noticed, though, that after the macro runs, Excel is not very responsive, and oftentimes the dropdown submenus from the ribbon are not functional. If the workbook is closed and reopened, then everything works fine again. I stepped through the macro with the Locals window open to see what happened with my variables, and they all get cleared. And at the end of the project I clear the global variables. Does anyone have any thoughts as to what may be going on here? I think I've provided the necessary info; please let me know if you need more. Thank you. Frank |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
"Phrank" wrote in message ... Hi, I'm running Office 2016 on Windows 7 (64 bit OS with 8gb RAM), and I've got a fairly large VBA macro that I run for my departments workload management tool. I've got 8 global variables, a main module, and 7 other sub-routines are called as the overall macro is processed. We've noticed, though, that after the macro runs, Excel is not very responsive, and oftentimes the dropdown submenus from the ribbon are not functional. If the workbook is closed and reopened, then everything works fine again. I stepped through the macro with the Locals window open to see what happened with my variables, and they all get cleared. And at the end of the project I clear the global variables. Does anyone have any thoughts as to what may be going on here? I think I've provided the necessary info; please let me know if you need more. Thank you. Without knowing what your what your macro does can only guess, what does a "large" macro mean? You say closing the workbook restores resources, did the macro do anything that added to the size of this workbook in memory, usedrange or formats perhaps. Add a new sheet to the workbook, then delete the others one at a time, checking memory each time (give it a while each time), then close the workbook. Those 8 global variables, what are they? Simple data variables are trivial, but big arrays or collections, or objects possibly with circular references might not be. FWIW if those variables are only required for the duration of the macro, chances are most of them can be removed from module level and passed between routines. Regards, Peter T |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
On Fri, 12 Feb 2016 16:55:00 -0000, "Peter T"
wrote: "Phrank" wrote in message .. . Hi, I'm running Office 2016 on Windows 7 (64 bit OS with 8gb RAM), and I've got a fairly large VBA macro that I run for my departments workload management tool. I've got 8 global variables, a main module, and 7 other sub-routines are called as the overall macro is processed. We've noticed, though, that after the macro runs, Excel is not very responsive, and oftentimes the dropdown submenus from the ribbon are not functional. If the workbook is closed and reopened, then everything works fine again. I stepped through the macro with the Locals window open to see what happened with my variables, and they all get cleared. And at the end of the project I clear the global variables. Does anyone have any thoughts as to what may be going on here? I think I've provided the necessary info; please let me know if you need more. Thank you. Without knowing what your what your macro does can only guess, what does a "large" macro mean? You say closing the workbook restores resources, did the macro do anything that added to the size of this workbook in memory, usedrange or formats perhaps. Add a new sheet to the workbook, then delete the others one at a time, checking memory each time (give it a while each time), then close the workbook. Those 8 global variables, what are they? Simple data variables are trivial, but big arrays or collections, or objects possibly with circular references might not be. FWIW if those variables are only required for the duration of the macro, chances are most of them can be removed from module level and passed between routines. Regards, Peter T Thanks Peter. The large macro is in the main workbook, and it goes out and opens three other workbooks on our SharePoint network and copies (i.e., updates) all of the data from one sheet in the network file into a sheet in the main workbook. The copy code is the most efficient I've found (see below). It doesn't add a new sheet or delete sheets, and it does close the other workbooks. There is part of the routine that maintains formats (row highlights) in the main workbook to the updates. With wksSource .Cells.Copy wksTarget.Cells(1) End With The 8 global variables are simply strings for network pathways and filenames. And those variables are already passed between routines. The filesize is just shy of 10mb, and it doesn't bloat. Have you (or anyone) had any experience with MS Office (Excel) 2013 or 2016? We don't recall having these issues with Office 2010. Thanks for taking the time to think about this. It's frustrating for our users. Frank |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
"Phrank" wrote in message On Fri, 12 Feb 2016 16:55:00 -0000, "Peter T" wrote: "Phrank" wrote in message . .. Hi, I'm running Office 2016 on Windows 7 (64 bit OS with 8gb RAM), and I've got a fairly large VBA macro that I run for my departments workload management tool. I've got 8 global variables, a main module, and 7 other sub-routines are called as the overall macro is processed. We've noticed, though, that after the macro runs, Excel is not very responsive, and oftentimes the dropdown submenus from the ribbon are not functional. If the workbook is closed and reopened, then everything works fine again. I stepped through the macro with the Locals window open to see what happened with my variables, and they all get cleared. And at the end of the project I clear the global variables. Does anyone have any thoughts as to what may be going on here? I think I've provided the necessary info; please let me know if you need more. Thank you. Without knowing what your what your macro does can only guess, what does a "large" macro mean? You say closing the workbook restores resources, did the macro do anything that added to the size of this workbook in memory, usedrange or formats perhaps. Add a new sheet to the workbook, then delete the others one at a time, checking memory each time (give it a while each time), then close the workbook. Those 8 global variables, what are they? Simple data variables are trivial, but big arrays or collections, or objects possibly with circular references might not be. FWIW if those variables are only required for the duration of the macro, chances are most of them can be removed from module level and passed between routines. Regards, Peter T Thanks Peter. The large macro is in the main workbook, and it goes out and opens three other workbooks on our SharePoint network and copies (i.e., updates) all of the data from one sheet in the network file into a sheet in the main workbook. The copy code is the most efficient I've found (see below). It doesn't add a new sheet or delete sheets, and it does close the other workbooks. There is part of the routine that maintains formats (row highlights) in the main workbook to the updates. With wksSource .Cells.Copy wksTarget.Cells(1) End With It may or may not be efficent depending on all sorts of things about both the source and target sheets and workbooks. Eg Names, CFs, formulas with dependancies etc, and not least large unnecessary size if copying the entire sheet. The most efficient way if you only need the data would be rngTarget.Value = rngSource.Value (assumes rngTarget is sized to rngSource and rngTarget is the minimum size that contains the data) Though even then with a very large sheet it might be worth doing in chunks. You might try clearing the clipboard with app.cutcopymode = false, though probably won't make any difference. It sounds like you could replicate your macro manually. If you haven't already try, and compare if any difference with doing it programatically. For the copy part, rather than the whole sheet select A1 then Shift-Ctrl-End, Copy, then Paste The 8 global variables are simply strings for network pathways and filenames. And those variables are already passed between routines. That's nothing, but any unclosed "connections" ? The filesize is just shy of 10mb, and it doesn't bloat. Have you (or anyone) had any experience with MS Office (Excel) 2013 or 2016? We don't recall having these issues with Office 2010. All versions to 2013 but not 2016. Regards, Peter T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
The large macro is in the main workbook, and it goes
out and opens three other workbooks on our SharePoint network and copies (i.e., updates) all of the data from one sheet in the network file into a sheet in the main workbook. The copy code is the most efficient I've found (see below). It doesn't add a new sheet or delete sheets, and it does close the other workbooks. There is part of the routine that maintains formats (row highlights) in the main workbook to the updates. Just curious why you don't use ADODB so you don't have to open the workbooks to retrieve your data? Just read the data into a recordset and 'dump' it directly into wksTarget! With wksSource .Cells.Copy wksTarget.Cells(1) End With Well this looks to be a very familiar 'style'!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
On Fri, 12 Feb 2016 23:08:34 -0000, "Peter T"
wrote: "Phrank" wrote in message On Fri, 12 Feb 2016 16:55:00 -0000, "Peter T" wrote: "Phrank" wrote in message ... Hi, I'm running Office 2016 on Windows 7 (64 bit OS with 8gb RAM), and I've got a fairly large VBA macro that I run for my departments workload management tool. I've got 8 global variables, a main module, and 7 other sub-routines are called as the overall macro is processed. We've noticed, though, that after the macro runs, Excel is not very responsive, and oftentimes the dropdown submenus from the ribbon are not functional. If the workbook is closed and reopened, then everything works fine again. I stepped through the macro with the Locals window open to see what happened with my variables, and they all get cleared. And at the end of the project I clear the global variables. Does anyone have any thoughts as to what may be going on here? I think I've provided the necessary info; please let me know if you need more. Thank you. Without knowing what your what your macro does can only guess, what does a "large" macro mean? You say closing the workbook restores resources, did the macro do anything that added to the size of this workbook in memory, usedrange or formats perhaps. Add a new sheet to the workbook, then delete the others one at a time, checking memory each time (give it a while each time), then close the workbook. Those 8 global variables, what are they? Simple data variables are trivial, but big arrays or collections, or objects possibly with circular references might not be. FWIW if those variables are only required for the duration of the macro, chances are most of them can be removed from module level and passed between routines. Regards, Peter T Thanks Peter. The large macro is in the main workbook, and it goes out and opens three other workbooks on our SharePoint network and copies (i.e., updates) all of the data from one sheet in the network file into a sheet in the main workbook. The copy code is the most efficient I've found (see below). It doesn't add a new sheet or delete sheets, and it does close the other workbooks. There is part of the routine that maintains formats (row highlights) in the main workbook to the updates. With wksSource .Cells.Copy wksTarget.Cells(1) End With It may or may not be efficent depending on all sorts of things about both the source and target sheets and workbooks. Eg Names, CFs, formulas with dependancies etc, and not least large unnecessary size if copying the entire sheet. The most efficient way if you only need the data would be rngTarget.Value = rngSource.Value (assumes rngTarget is sized to rngSource and rngTarget is the minimum size that contains the data) Though even then with a very large sheet it might be worth doing in chunks. You might try clearing the clipboard with app.cutcopymode = false, though probably won't make any difference. It sounds like you could replicate your macro manually. If you haven't already try, and compare if any difference with doing it programatically. For the copy part, rather than the whole sheet select A1 then Shift-Ctrl-End, Copy, then Paste The 8 global variables are simply strings for network pathways and filenames. And those variables are already passed between routines. That's nothing, but any unclosed "connections" ? The filesize is just shy of 10mb, and it doesn't bloat. Have you (or anyone) had any experience with MS Office (Excel) 2013 or 2016? We don't recall having these issues with Office 2010. All versions to 2013 but not 2016. Regards, Peter T Thanks Peter. I actually clearcontents on the target sheet before bringing the data over from the source sheet, so will that affect the rngTarget.Value = rngSource.Value code? And the only connections I have are when the other three workbooks are opened, but they all get closed. I don't think this is necessarily connected with Office 2016, as it seemed to start with Office 2013. Our company just upgraded to Office 365 (with Office 2013), and when these issues started, our IT wanted to try upgrading me to Office 2016 to see if that resolved the issues. It didn't. I still can't help but think it has something to do with the changes to Office 2013/2016, because as I said, we don't recall there being these issues with Office 2010. But I'm trying to cover all the bases. Thank you! Frank. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
On Fri, 12 Feb 2016 19:56:14 -0500, GS wrote:
The large macro is in the main workbook, and it goes out and opens three other workbooks on our SharePoint network and copies (i.e., updates) all of the data from one sheet in the network file into a sheet in the main workbook. The copy code is the most efficient I've found (see below). It doesn't add a new sheet or delete sheets, and it does close the other workbooks. There is part of the routine that maintains formats (row highlights) in the main workbook to the updates. Just curious why you don't use ADODB so you don't have to open the workbooks to retrieve your data? Just read the data into a recordset and 'dump' it directly into wksTarget! With wksSource .Cells.Copy wksTarget.Cells(1) End With Well this looks to be a very familiar 'style'!<g Hi Garry. Yeah, I may very well have gotten that copy/paste code from you. I've been using it for quite some time. When you talk about the ADODB, do you mean a direct connection to that workbook? If so, I'm interested, but I'm wondering how to get just the data from a specific worksheet this way. Could you explain a little more, please? Thank you! Frank |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
On Fri, 12 Feb 2016 19:56:14 -0500, GS wrote:
The large macro is in the main workbook, and it goes out and opens three other workbooks on our SharePoint network and copies (i.e., updates) all of the data from one sheet in the network file into a sheet in the main workbook. The copy code is the most efficient I've found (see below). It doesn't add a new sheet or delete sheets, and it does close the other workbooks. There is part of the routine that maintains formats (row highlights) in the main workbook to the updates. Just curious why you don't use ADODB so you don't have to open the workbooks to retrieve your data? Just read the data into a recordset and 'dump' it directly into wksTarget! With wksSource .Cells.Copy wksTarget.Cells(1) End With Well this looks to be a very familiar 'style'!<g Hi Garry. Yeah, I may very well have gotten that copy/paste code from you. I've been using it for quite some time. When you talk about the ADODB, do you mean a direct connection to that workbook? If so, I'm interested, but I'm wondering how to get just the data from a specific worksheet this way. Could you explain a little more, please? Thank you! Frank Have a look here for a tutorial... http://www.appspro.com/conference/Da...rogramming.zip -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
"Phrank" wrote in message On Fri, 12 Feb 2016 23:08:34 -0000, "Peter T" wrote: "Phrank" wrote in message On Fri, 12 Feb 2016 16:55:00 -0000, "Peter T" wrote: "Phrank" wrote in message m... Hi, I'm running Office 2016 on Windows 7 (64 bit OS with 8gb RAM), and I've got a fairly large VBA macro that I run for my departments workload management tool. I've got 8 global variables, a main module, and 7 other sub-routines are called as the overall macro is processed. We've noticed, though, that after the macro runs, Excel is not very responsive, and oftentimes the dropdown submenus from the ribbon are not functional. If the workbook is closed and reopened, then everything works fine again. I stepped through the macro with the Locals window open to see what happened with my variables, and they all get cleared. And at the end of the project I clear the global variables. Does anyone have any thoughts as to what may be going on here? I think I've provided the necessary info; please let me know if you need more. Thank you. Without knowing what your what your macro does can only guess, what does a "large" macro mean? You say closing the workbook restores resources, did the macro do anything that added to the size of this workbook in memory, usedrange or formats perhaps. Add a new sheet to the workbook, then delete the others one at a time, checking memory each time (give it a while each time), then close the workbook. Those 8 global variables, what are they? Simple data variables are trivial, but big arrays or collections, or objects possibly with circular references might not be. FWIW if those variables are only required for the duration of the macro, chances are most of them can be removed from module level and passed between routines. Regards, Peter T Thanks Peter. The large macro is in the main workbook, and it goes out and opens three other workbooks on our SharePoint network and copies (i.e., updates) all of the data from one sheet in the network file into a sheet in the main workbook. The copy code is the most efficient I've found (see below). It doesn't add a new sheet or delete sheets, and it does close the other workbooks. There is part of the routine that maintains formats (row highlights) in the main workbook to the updates. With wksSource .Cells.Copy wksTarget.Cells(1) End With It may or may not be efficent depending on all sorts of things about both the source and target sheets and workbooks. Eg Names, CFs, formulas with dependancies etc, and not least large unnecessary size if copying the entire sheet. The most efficient way if you only need the data would be rngTarget.Value = rngSource.Value (assumes rngTarget is sized to rngSource and rngTarget is the minimum size that contains the data) Though even then with a very large sheet it might be worth doing in chunks. You might try clearing the clipboard with app.cutcopymode = false, though probably won't make any difference. It sounds like you could replicate your macro manually. If you haven't already try, and compare if any difference with doing it programatically. For the copy part, rather than the whole sheet select A1 then Shift-Ctrl-End, Copy, then Paste The 8 global variables are simply strings for network pathways and filenames. And those variables are already passed between routines. That's nothing, but any unclosed "connections" ? The filesize is just shy of 10mb, and it doesn't bloat. Have you (or anyone) had any experience with MS Office (Excel) 2013 or 2016? We don't recall having these issues with Office 2010. All versions to 2013 but not 2016. Regards, Peter T Thanks Peter. I actually clearcontents on the target sheet before bringing the data over from the source sheet, so will that affect the rngTarget.Value = rngSource.Value code? The above will write the "values" that exist in rngSource to all equivalent cells in rngSource. It's similar to doing PasteSpecial / values, if anything more efficiently because it doesn't use the clipboard behind the scenes. Any formats in rngTarget will remain unchanged. So in answer to your question, no, though conceivably clearing contents might be related if the sheet had a lof of data and/or formats. No formulas or formats are copied of course, But if you need either of those try the PasteSpecial method with vlaues or formulas as appropriate, then formats. And the only connections I have are when the other three workbooks are opened, but they all get closed. I don't think this is necessarily connected with Office 2016, as it seemed to start with Office 2013. Our company just upgraded to Office 365 (with Office 2013), and when these issues started, our IT wanted to try upgrading me to Office 2016 to see if that resolved the issues. It didn't. I still can't help but think it has something to do with the changes to Office 2013/2016, because as I said, we don't recall there being these issues with Office 2010. But I'm trying to cover all the bases. Thank you! From what I understand of your macro it's pretty straightforward. Have you tried replicating the macro manually as I suggested last time? Another thing to try, put a copy of the Sharepoint workbook on your machine, open it manually, and your adapt your macro to do everything as before but from the already open workbook. Another thing you might try, move your entire macro code to another workbook and run it from there. I assume the only difference would be to change any ThisWorkbook to Workbooks("name"). After if you've still got the problem alternatively close the code workbook and the target workbook with the copied data, IOW closing which workbook restored resources. Each new Excel version seems to introduce a combination of new issues and some fixes. By far the biggest change with 2013 is the change from MDI to SDI, but I don't relate anything you've described to some other new issue in 2013. Regards, Peter T Frank. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
On Sat, 13 Feb 2016 18:52:43 -0000, "Peter T"
wrote: "Phrank" wrote in message On Fri, 12 Feb 2016 23:08:34 -0000, "Peter T" wrote: "Phrank" wrote in message On Fri, 12 Feb 2016 16:55:00 -0000, "Peter T" wrote: "Phrank" wrote in message om... Hi, I'm running Office 2016 on Windows 7 (64 bit OS with 8gb RAM), and I've got a fairly large VBA macro that I run for my departments workload management tool. I've got 8 global variables, a main module, and 7 other sub-routines are called as the overall macro is processed. We've noticed, though, that after the macro runs, Excel is not very responsive, and oftentimes the dropdown submenus from the ribbon are not functional. If the workbook is closed and reopened, then everything works fine again. I stepped through the macro with the Locals window open to see what happened with my variables, and they all get cleared. And at the end of the project I clear the global variables. Does anyone have any thoughts as to what may be going on here? I think I've provided the necessary info; please let me know if you need more. Thank you. Without knowing what your what your macro does can only guess, what does a "large" macro mean? You say closing the workbook restores resources, did the macro do anything that added to the size of this workbook in memory, usedrange or formats perhaps. Add a new sheet to the workbook, then delete the others one at a time, checking memory each time (give it a while each time), then close the workbook. Those 8 global variables, what are they? Simple data variables are trivial, but big arrays or collections, or objects possibly with circular references might not be. FWIW if those variables are only required for the duration of the macro, chances are most of them can be removed from module level and passed between routines. Regards, Peter T Thanks Peter. The large macro is in the main workbook, and it goes out and opens three other workbooks on our SharePoint network and copies (i.e., updates) all of the data from one sheet in the network file into a sheet in the main workbook. The copy code is the most efficient I've found (see below). It doesn't add a new sheet or delete sheets, and it does close the other workbooks. There is part of the routine that maintains formats (row highlights) in the main workbook to the updates. With wksSource .Cells.Copy wksTarget.Cells(1) End With It may or may not be efficent depending on all sorts of things about both the source and target sheets and workbooks. Eg Names, CFs, formulas with dependancies etc, and not least large unnecessary size if copying the entire sheet. The most efficient way if you only need the data would be rngTarget.Value = rngSource.Value (assumes rngTarget is sized to rngSource and rngTarget is the minimum size that contains the data) Though even then with a very large sheet it might be worth doing in chunks. You might try clearing the clipboard with app.cutcopymode = false, though probably won't make any difference. It sounds like you could replicate your macro manually. If you haven't already try, and compare if any difference with doing it programatically. For the copy part, rather than the whole sheet select A1 then Shift-Ctrl-End, Copy, then Paste The 8 global variables are simply strings for network pathways and filenames. And those variables are already passed between routines. That's nothing, but any unclosed "connections" ? The filesize is just shy of 10mb, and it doesn't bloat. Have you (or anyone) had any experience with MS Office (Excel) 2013 or 2016? We don't recall having these issues with Office 2010. All versions to 2013 but not 2016. Regards, Peter T Thanks Peter. I actually clearcontents on the target sheet before bringing the data over from the source sheet, so will that affect the rngTarget.Value = rngSource.Value code? The above will write the "values" that exist in rngSource to all equivalent cells in rngSource. It's similar to doing PasteSpecial / values, if anything more efficiently because it doesn't use the clipboard behind the scenes. Any formats in rngTarget will remain unchanged. So in answer to your question, no, though conceivably clearing contents might be related if the sheet had a lof of data and/or formats. No formulas or formats are copied of course, But if you need either of those try the PasteSpecial method with vlaues or formulas as appropriate, then formats. And the only connections I have are when the other three workbooks are opened, but they all get closed. I don't think this is necessarily connected with Office 2016, as it seemed to start with Office 2013. Our company just upgraded to Office 365 (with Office 2013), and when these issues started, our IT wanted to try upgrading me to Office 2016 to see if that resolved the issues. It didn't. I still can't help but think it has something to do with the changes to Office 2013/2016, because as I said, we don't recall there being these issues with Office 2010. But I'm trying to cover all the bases. Thank you! From what I understand of your macro it's pretty straightforward. Have you tried replicating the macro manually as I suggested last time? Another thing to try, put a copy of the Sharepoint workbook on your machine, open it manually, and your adapt your macro to do everything as before but from the already open workbook. Another thing you might try, move your entire macro code to another workbook and run it from there. I assume the only difference would be to change any ThisWorkbook to Workbooks("name"). After if you've still got the problem alternatively close the code workbook and the target workbook with the copied data, IOW closing which workbook restored resources. Each new Excel version seems to introduce a combination of new issues and some fixes. By far the biggest change with 2013 is the change from MDI to SDI, but I don't relate anything you've described to some other new issue in 2013. Regards, Peter T Thank you Peter! I'll give those ideas a try when I'm back at work this week. I'll post back when/if I find anything. Much appreciated. Frank |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
On Sat, 13 Feb 2016 12:55:19 -0500, GS wrote:
On Fri, 12 Feb 2016 19:56:14 -0500, GS wrote: The large macro is in the main workbook, and it goes out and opens three other workbooks on our SharePoint network and copies (i.e., updates) all of the data from one sheet in the network file into a sheet in the main workbook. The copy code is the most efficient I've found (see below). It doesn't add a new sheet or delete sheets, and it does close the other workbooks. There is part of the routine that maintains formats (row highlights) in the main workbook to the updates. Just curious why you don't use ADODB so you don't have to open the workbooks to retrieve your data? Just read the data into a recordset and 'dump' it directly into wksTarget! With wksSource .Cells.Copy wksTarget.Cells(1) End With Well this looks to be a very familiar 'style'!<g Hi Garry. Yeah, I may very well have gotten that copy/paste code from you. I've been using it for quite some time. When you talk about the ADODB, do you mean a direct connection to that workbook? If so, I'm interested, but I'm wondering how to get just the data from a specific worksheet this way. Could you explain a little more, please? Thank you! Frank Have a look here for a tutorial... http://www.appspro.com/conference/Da...rogramming.zip Hi Garry, Wow! I've used ODBC a couple times before (with the help of someone much more experienced), but I believe with this tutorial and some more Googling, I should be able to figure this out and apply it. VERY interesting and helpful. Thank you! Frank |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible resources issue after running macro
On Sat, 13 Feb 2016 12:55:19 -0500, GS wrote:
On Fri, 12 Feb 2016 19:56:14 -0500, GS wrote: The large macro is in the main workbook, and it goes out and opens three other workbooks on our SharePoint network and copies (i.e., updates) all of the data from one sheet in the network file into a sheet in the main workbook. The copy code is the most efficient I've found (see below). It doesn't add a new sheet or delete sheets, and it does close the other workbooks. There is part of the routine that maintains formats (row highlights) in the main workbook to the updates. Just curious why you don't use ADODB so you don't have to open the workbooks to retrieve your data? Just read the data into a recordset and 'dump' it directly into wksTarget! With wksSource .Cells.Copy wksTarget.Cells(1) End With Well this looks to be a very familiar 'style'!<g Hi Garry. Yeah, I may very well have gotten that copy/paste code from you. I've been using it for quite some time. When you talk about the ADODB, do you mean a direct connection to that workbook? If so, I'm interested, but I'm wondering how to get just the data from a specific worksheet this way. Could you explain a little more, please? Thank you! Frank Have a look here for a tutorial... http://www.appspro.com/conference/Da...rogramming.zip Hi Garry, Wow! I've used ODBC a couple times before (with the help of someone much more experienced), but I believe with this tutorial and some more Googling, I should be able to figure this out and apply it. VERY interesting and helpful. Thank you! Frank You're welcome! That info should provide everything you need for accomplishing the task described here. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running total macro issue | Excel Programming | |||
Excel 2007 running out of resources | Excel Discussion (Misc queries) | |||
Running low on resources | Excel Discussion (Misc queries) | |||
Running 2 projects at same time issue | Excel Programming | |||
Excel macro migration resources | Excel Programming |