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?-- Jim

Re: Update Excel Spreadsheet from Access VBA

"RobFMS" <Rob[ at ]FMS_FinancialModelingSpecialists.com>

11/22/2005 1:10:08 AM

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?

"JimS" <JimS[ at ]discussions.microsoft.com> wrote in message news:297CAA2C-BC17-4B56-B60C-AB335CE6BFC6[ at ]microsoft.com...

[Quoted Text]

>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?> -- > Jim

Re: Update Excel Spreadsheet from Access VBA

JimS

11/22/2005 12:42:03 PM

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? -- Jim

"RobFMS" wrote:

[Quoted Text]

> 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> > -- > FMS Professional Solutions Group> http://www.fmsinc.com/consulting> > Software Tools for .NET, SQL Server, Visual Basic & Access> http://www.fmsinc.com> > > "JimS" <JimS[ at ]discussions.microsoft.com> wrote in message > news:297CAA2C-BC17-4B56-B60C-AB335CE6BFC6[ at ]microsoft.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> > 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?> > -- > > Jim > > >

Re: Update Excel Spreadsheet from Access VBA

"RobFMS" <Rob[ at ]FMS_FinancialModelingSpecialists.com>

11/22/2005 4:48:26 PM

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.

"JimS" <JimS[ at ]discussions.microsoft.com> wrote in message news:858DE4D1-D26C-40DC-BCC3-0F615FB092CB[ at ]microsoft.com...

[Quoted Text]

> 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?> -- > Jim>>> "RobFMS" wrote:>>> 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>>>> -- >> FMS Professional Solutions Group>> http://www.fmsinc.com/consulting>>>> Software Tools for .NET, SQL Server, Visual Basic & Access>> http://www.fmsinc.com>>>>>> "JimS" <JimS[ at ]discussions.microsoft.com> wrote in message>> news:297CAA2C-BC17-4B56-B60C-AB335CE6BFC6[ at ]microsoft.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>> > 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?>> > -- >> > Jim>>>>>>

Re: Update Excel Spreadsheet from Access VBA

JimS

11/22/2005 6:51:21 PM

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.....

-- Jim

"RobFMS" wrote:

[Quoted Text]

> 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> > -- > FMS Professional Solutions Group> http://www.fmsinc.com/consulting> > Software Tools for .NET, SQL Server, Visual Basic & Access> http://www.fmsinc.com> > > "JimS" <JimS[ at ]discussions.microsoft.com> wrote in message > news:858DE4D1-D26C-40DC-BCC3-0F615FB092CB[ at ]microsoft.com...> > 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?> > -- > > Jim> >> >> > "RobFMS" wrote:> >> >> 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> >>> >> -- > >> FMS Professional Solutions Group> >> http://www.fmsinc.com/consulting> >>> >> Software Tools for .NET, SQL Server, Visual Basic & Access> >> http://www.fmsinc.com> >>> >>> >> "JimS" <JimS[ at ]discussions.microsoft.com> wrote in message> >> news:297CAA2C-BC17-4B56-B60C-AB335CE6BFC6[ at ]microsoft.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> >> > 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?> >> > -- > >> > Jim> >>> >>> >> > > >

Re: Update Excel Spreadsheet from Access VBA

davidp

11/23/2005 7:42:02 PM

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 dataOr 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 [ at ] davidp [ at ] usafmsat dot com

The problem is user permissions, I found that you must have minimum of poweruser access. Which relates to my post.

Re: Update Excel Spreadsheet from Access VBA

JimS

11/26/2005 3:49:01 AM

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.-- Jim

"davidp" wrote:

[Quoted Text]

> 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 [ at ] davidp [ at ] usafmsat dot > com> > The problem is user permissions, I found that you must have minimum of > poweruser access. Which relates to my post.> >

Re: Update Excel Spreadsheet from Access VBA

Secret Squirrel

11/30/2005 9:42:01 PM

Dave,I'm interseted in this as well. Would it be possible to get a copy of this as well?

Thanks

"davidp" wrote:

[Quoted Text]

> 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 [ at ] davidp [ at ] usafmsat dot > com> > The problem is user permissions, I found that you must have minimum of > poweruser access. Which relates to my post.> >