 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Developer Toolkits / November 2005Update Excel Spreadsheet from Access VBA
I want to read in an Excel spreadsheet (multiple sheets from a workbook), update some of the cells, then transfer that range of cells back to the spreadsheet. I don't mind treating the whole sheet as a table, but the ADO Update method doesn't work and I can't find any other ways other than "export spreadsheet" method, which requires transferring the whole sheet at once. Strategies?  SignatureJim Jim Sounds like you need to perform some Excel Automation. Is the worksheets in some particlar ordering that you know what rows and/or columns you need to touch? Have you ever performed Excel Automation? Here's a sampling: Tip #2: Excel Automation: More than just a formula http://www.fmsinc.com/free/tips.html#ExcelautomationVBA Rob Mastrostefano SignatureFMS Professional Solutions Group http://www.fmsinc.com/consulting Software Tools for .NET, SQL Server, Visual Basic & Access http://www.fmsinc.com >I want to read in an Excel spreadsheet (multiple sheets from a workbook), > update some of the cells, then transfer that range of cells back to the [quoted text clipped - 4 lines] > > Strategies? Thank you for responding. I appreciate your interest. I've read up on using VBA within Excel, but I don't have access to the Excel Spreadsheet to insert VBA code (what I assume you refer to....) I have read-write access to it, and I am familiar with its structure. I need to work with a range that is NOT the entire sheet, and is carefully structured. I need to update about three cells per row, and there are 99 rows of interest in each sheet, 20 sheets per workbook, 10 woorkbooks. I'm using Access to load up the contents of those cells, then later to update one column per row. In the meantime, a user must use the spreadsheet to post his/her results within the same range (not simultaneously). Any references and/or strategies? SignatureJim > Jim > [quoted text clipped - 17 lines] > > > > Strategies? Jim You don't insert the VBA code into Excel, you create the code in Access and have Access establish its connection to Excel. The sample link below should give you a little sampling of the coding. Use google to give a search for "Excel Automation" Take a look at what you can find. If you have any direct questions about some of what you find, let me know. Rob Mastrostefano SignatureFMS Professional Solutions Group http://www.fmsinc.com/consulting Software Tools for .NET, SQL Server, Visual Basic & Access http://www.fmsinc.com > Thank you for responding. I appreciate your interest. > [quoted text clipped - 39 lines] >> > >> > Strategies? OK, I set up a reference in Access to the Excel automation class library. Then, I used the classes, methods, etc to open and update the spreadsheet. I.E. workbooks.open "blah"... worksheet("Summary").Range("A14").Value = "Boo" Workbooks.close . . . Works fine, even updates the spreadsheet...but it then gives me an error saying the cell(s) are protected. I used the "unprotect" method on it before updating, and nothing changed. The odd thing is that it updated the cell(s) despite the error message....go figure..... SignatureJim > Jim > [quoted text clipped - 52 lines] > >> > > >> > Strategies? JimS, I done exactly what you're looking for couple of years ago and will be doing in the near future. From Access, you can open any workbook and select any worksheet to pull the data Or you could put all of the different workbooks into a single folder and let Access scroll through everything. It will take me couple of hours to write it. If interested. Also under (module section) Tools ->References must have excel selected. otherwise it you can't get it to work. email me @ davidp @ usafmsat dot com The problem is user permissions, I found that you must have minimum of poweruser access. Which relates to my post. Turns out the issue I was having was the owner of the workbooks had riddled them with event procedures. I disabled event processing and all went just ducky. I used the tools you described, David. Thank you. SignatureJim > JimS, > [quoted text clipped - 12 lines] > The problem is user permissions, I found that you must have minimum of > poweruser access. Which relates to my post. Dave, I'm interseted in this as well. Would it be possible to get a copy of this as well? Thanks > JimS, > [quoted text clipped - 12 lines] > The problem is user permissions, I found that you must have minimum of > poweruser access. Which relates to my post. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.