Group:  Microsoft Access ยป microsoft.public.access.devtoolkits
Thread: Update Excel Spreadsheet from Access VBA

Geek News

Update Excel Spreadsheet from Access VBA
JimS 11/21/2005 8:03:02 PM
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?

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

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

Home | Search | Terms | Imprint Contact
Newsgroups Reader - provided by WiredBox.Net