Working with Excel

[The hallEffect]

I am replacing a "house of cards" set of linked Excel
files with an Access data processing engine. There are
two sets of highly-formatted Excel files (one set for
input, the other set for output) that I want to read from
and write to using Access. For several reasons, the
inputs and outputs need to remain in the highly-formatted
Excel files.

[group of .xls files] -> [data processing in Access] ->
[group of .xls files]

I want to reach into the Excel input files to grab data
from them, manipulate that data in Access, and write out
the data to already existing Excel output files.

I'd appreciate any thoughts on the most elegant way to
accomplish my task.

Thanks

 

[John Nurick]

There's no really elegant way of doing this. Access's standard import,
link and export facilities require the data in Excel to be laid out as a
plain table. Depending on the precise requirements one (or both) of
these may do the job:

1) Create additional worksheets in the workbooks, each sheet containing
an unformatted table of data. Access reads from and writes to these. The
highly-formatted sheets contain formulas that pick up values from the
"data" sheets.

2) Use Automation and have VBA code running in Access retrieve data from
and write it to individual cells or named ranges in Excel (or code
running in Excel that sends and retrieves data to and from Access). The
links below should get you started.

But if you possibly can, use Access as the data store. Otherwise you'll
still have a house of cards, even if it's not quite so tall<g>.

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

http://support.microsoft.com/?id=131583

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476
Using Microsoft Access as an Automation Server
http://support.microsoft.com/?id=147816
http://support.microsoft.com/?id=210111 (Access 2000 and later)

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859


ACC2000: How to Use Automation to Fill a List Box (Q210145)
http://support.microsoft.com/?id=210145
This article shows you how to use Automation to populate a list box
with values from a Microsoft Excel worksheet

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235

I am replacing a "house of cards" set of linked Excel
files with an Access data processing engine. There are
two sets of highly-formatted Excel files (one set for
input, the other set for output) that I want to read from
and write to using Access. For several reasons, the
inputs and outputs need to remain in the highly-formatted
Excel files.

[group of .xls files] -> [data processing in Access] ->
[group of .xls files]

I want to reach into the Excel input files to grab data
from them, manipulate that data in Access, and write out
the data to already existing Excel output files.

I'd appreciate any thoughts on the most elegant way to
accomplish my task.

Thanks

 

[The hallEffect]

-----Original Message-----
There's no really elegant way of doing this. Access's standard import,
link and export facilities require the data in Excel to be laid out as a
plain table. Depending on the precise requirements one (or both) of
these may do the job:

1) Create additional worksheets in the workbooks, each sheet containing
an unformatted table of data. Access reads from and writes to these. The
highly-formatted sheets contain formulas that pick up values from the
"data" sheets.

2) Use Automation and have VBA code running in Access retrieve data from
and write it to individual cells or named ranges in Excel (or code
running in Excel that sends and retrieves data to and from Access). The
links below should get you started.

But if you possibly can, use Access as the data store. Otherwise you'll
still have a house of cards, even if it's not quite so

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

http://support.microsoft.com/?id=131583

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476
Using Microsoft Access as an Automation Server
http://support.microsoft.com/?id=147816
http://support.microsoft.com/?id=210111 (Access 2000 and later)

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859


ACC2000: How to Use Automation to Fill a List Box (Q210145)
http://support.microsoft.com/?id=210145
This article shows you how to use Automation to populate a list box
with values from a Microsoft Excel worksheet

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235

I am replacing a "house of cards" set of linked Excel
files with an Access data processing engine. There are
two sets of highly-formatted Excel files (one set for
input, the other set for output) that I want to read from
and write to using Access. For several reasons, the
inputs and outputs need to remain in the highly- formatted
Excel files.

[group of .xls files] -> [data processing in Access] ->
[group of .xls files]

I want to reach into the Excel input files to grab data
from them, manipulate that data in Access, and write out
the data to already existing Excel output files.

I'd appreciate any thoughts on the most elegant way to
accomplish my task.

Thanks

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Thanks for your reply, John.

My main challenge in the update of the output files. I
can import the source data using Transfer.Spreadsheet and
I am happy with the results of that. I can manipulate the
data in Access fine. The elusive piece is writing a
specific value to a specific cell (or range of values to
a range of cells) in a specific worksheet in an already
existing Excel file.

How you do dat?

Thanks

 

[The hallEffect]

-----Original Message-----
There's no really elegant way of doing this. Access's standard import,
link and export facilities require the data in Excel to be laid out as a
plain table. Depending on the precise requirements one (or both) of
these may do the job:

1) Create additional worksheets in the workbooks, each sheet containing
an unformatted table of data. Access reads from and writes to these. The
highly-formatted sheets contain formulas that pick up values from the
"data" sheets.

2) Use Automation and have VBA code running in Access retrieve data from
and write it to individual cells or named ranges in Excel (or code
running in Excel that sends and retrieves data to and from Access). The
links below should get you started.

But if you possibly can, use Access as the data store. Otherwise you'll
still have a house of cards, even if it's not quite so

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

http://support.microsoft.com/?id=131583

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476
Using Microsoft Access as an Automation Server
http://support.microsoft.com/?id=147816
http://support.microsoft.com/?id=210111 (Access 2000 and later)

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859


ACC2000: How to Use Automation to Fill a List Box (Q210145)
http://support.microsoft.com/?id=210145
This article shows you how to use Automation to populate a list box
with values from a Microsoft Excel worksheet

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235

I am replacing a "house of cards" set of linked Excel
files with an Access data processing engine. There are
two sets of highly-formatted Excel files (one set for
input, the other set for output) that I want to read from
and write to using Access. For several reasons, the
inputs and outputs need to remain in the highly- formatted
Excel files.

[group of .xls files] -> [data processing in Access] ->
[group of .xls files]

I want to reach into the Excel input files to grab data
from them, manipulate that data in Access, and write out
the data to already existing Excel output files.

I'd appreciate any thoughts on the most elegant way to
accomplish my task.

Thanks

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

I see now that I may have misled you. The formatting has
to do with the presentation of the cells, not the data
itself. All the actual data I'm concerned with is plain
alphanumeric stuff in the default font.
I simply need to place the values I come up with in
Access into specific cells of specific worksheets in
already existing Excel files, and I can't figure out how
to do that.

 

[John Nurick]

You'll need to use Automation, as described in the links in my previous
message.