[AccessD] Linking to an excel spreadsheet

Hello John,

Would it not give you more control to do it this way?  It is a little
more complex, but if gives you total control.

Especially if you use the tabledefs collection that Gustav describes
above.  That sounds interesting.

Best of Luck

Mark


On Tue, 23 Nov 2004 07:15:17 -0500, John W. Colby
<jwcolby at colbyconsulting.com> wrote:
> 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
> 
> 
> --
> _______________________________________________
> 
> 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>