[AccessD] Linking to an excel spreadsheet

Mark,

I did the same kinds of thing down in Mexico.  A client ran a maquilladora
making clothing.  They were simply accustomed to Excel and able to
manipulate data in it for analysis so we always exported our data out to
Excel using similar concepts to what you are discussing.  Rarely poking a
cell at a time but often dropping the entire table out there.  Then my
friend would go in and format the data, manipulate it for summations etc.
all with the macro recorder on.  We would then in the end dump the data and
have Access run the macros that he created.  It worked very well!

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Breen
Sent: Tuesday, November 23, 2004 3:18 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Linking to an excel spreadsheet


Hello John,

It is quite easy to talk to Excel, but I guess that it depends on what you
need to achieve and which way you prefer to go about it.

I have just completed five days coding, on-site for a large European
retailer that are very, very security conscious.  Unfortunately for you, I
am not allowed to take work off site, believe it or not, I am not even
allowed on the Internet or email.  If it was not for that, I would just give
you the mdb.

In a nutshell, it prepares some data using normal queries.  Once ready, I
open an instance of Excel, add a sheet and then start poking the values into
excel.  The first two or three cells require that you get used to it, but
once they are done, you can then fill the whole sheet.  I am writing about
35000 cells of data from within nested loops that work down and across the
page.

>From that point, you can then programatically format the entire excel
sheet, including setting page setups etc.

I use the Macro Recorder to get the bones of the VBA that you need and then
bring it back into Access.

One thing that you have to be aware of is the object model in Excel.  

For example, the following code stumped me for a while yesterday

I have three variables 
objXLApp as Excel. Application
objXLBook as Excel.Workbook
objXLSht as Excel.WorkSheet

The following lines are how they are written in Access objXLSht.Range("A1",
"C3").Select objXLApp.Selection.Font.Bold = True

I would have presumed that it would have been objXLSht on the second line of
code.

Anyway, it is not an Excel table, but I may suit you to just programatically
pull the data in.

Best of luck,

Mark




On Mon, 22 Nov 2004 12:52:47 -0500, Colby, John <jcolby at dispec.com> wrote:
> Does anyone have code for linking to an excel spreadsheet?
> 
> If I manually create a link, the wizard finds all the fields, sets up 
> with the "first line has field names" etc.  Everything works fine.  
> However if the table is not linked already, I need to create a 
> tabledef, set the SourceTableName and Connect properties.  If I then 
> try to append the tabledef to db.tabledefs I get an error that no 
> fields are defined.  It is obvious that a LOT more is involved here.  
> It appears that I have to open the spreadsheet, get the first line, 
> create fields with the data in the first line, somehow figure out the 
> datatype of each column etc.  Is there a prebuilt code I can call to 
> do all this stuff?  If the wizard can do it, then the code behind the 
> wizard could do it for me.
> 
> Has anyone ever done this?
> 
> John W. Colby
> The DIS Database Guy
> 
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com