John W. Colby
jwcolby at colbyconsulting.com
Tue Nov 23 08:06:04 CST 2004
Well... You mean to import one field at a time? I am trying to get data in
to Access from Excel.
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 8:08 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
>
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com