Automating Excel from Access

[schaapiee]

I receive a file in excel; it needs to be automated. I created all the
code behind the 'workbook' to automate it [remove unneccesary columns,
rows, bad data, seperate out data into new sheets, calculate formulas,
rename the file etc..] But it seems like this is the longway of doing
things..

Is there an easier way to just import the original excel file into
Access, and then manipulate the data from there..?

Or maybe I can leave the file in .xls and write the equivalent VBA code
I wrote in Excel using an Access form interface instead, by using an
Excel ADO reference to accomplish the same things?

Let me know if you have any questions, or can help.
Thanks
~schaapiee

 

[Cindy M.]

Hi Schaapiee,

I receive a file in excel; it needs to be automated. I created all the
code behind the 'workbook' to automate it [remove unneccesary columns,
rows, bad data, seperate out data into new sheets, calculate formulas,
rename the file etc..] But it seems like this is the longway of doing
things..

Is there an easier way to just import the original excel file into
Access, and then manipulate the data from there..?

Or maybe I can leave the file in .xls and write the equivalent VBA code
I wrote in Excel using an Access form interface instead, by using an
Excel ADO reference to accomplish the same things?

It really depends on how the Excel file is constructed. If it's a
"well-formed" flat-file, Access can import it (try it, you'll find the
tool under the File menu). And yes, you can certainly use ADO (or even
DAO) to access the contents of an Excel workbook and process them - as
long as they give you a valid, two-dimensional table.

For ADO connection strings, see:
http://www.carlprothman.net/Default.aspx?tabid=81

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Iain Bishop]

Schaapiee,

I don't think you've given enough information about the problem. When you
say the Excel file 'needs to be automated', what are you trying to
accomplish exactly? Are you generating a report or are you cleaning the data
for import into Access? For example, if cleaning, it sounds like something
Access may be able to do more efficiently.

Regards
Iain
www.ibdesign.com.au

Hi Schaapiee,

I receive a file in excel; it needs to be automated. I created all the
code behind the 'workbook' to automate it [remove unneccesary columns,
rows, bad data, seperate out data into new sheets, calculate formulas,
rename the file etc..] But it seems like this is the longway of doing
things..

Is there an easier way to just import the original excel file into
Access, and then manipulate the data from there..?

Or maybe I can leave the file in .xls and write the equivalent VBA code
I wrote in Excel using an Access form interface instead, by using an
Excel ADO reference to accomplish the same things?

It really depends on how the Excel file is constructed. If it's a
"well-formed" flat-file, Access can import it (try it, you'll find the
tool under the File menu). And yes, you can certainly use ADO (or even
DAO) to access the contents of an Excel workbook and process them - as
long as they give you a valid, two-dimensional table.

For ADO connection strings, see:
http://www.carlprothman.net/Default.aspx?tabid=81

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)


This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[schaapiee]

I am weighing my options to accomplish both tasks in either XL only,
Access only, or a combination..

I started the process by writing VB script behind the XL file which
accomplished the tasks of 'cleaning it up, calculating formulas, and
generating new reports; this was set to autorun on open.

I have since then tried another option of importing the XL file into
Access, 'cleaned up', calculated formulas, and am in the process of
getting the table data into the various XL reports; this is all
accomplished with a form interface.

I hope this helps.
~schaapiee