Mark Breen
mark.breen at gmail.com
Tue Nov 23 09:02:09 CST 2004
All I am suggestions is that if you open a connection with the XL
workbook in question, you can take what you like, how you like, row by
row, col by col, as a range as gustav suggests, and taking his example
further, a complete sheet.
Depending on the volume and speed required, some of the options above
might not be relevant, but the may be options to consider.
With your programmatic skills, you can have the object model built in
30 minutes and then you can access (no pun intended) any data and put
it into MS Access where ever you like, specifying formats as you go.
Mark
On Tue, 23 Nov 2004 09:06:04 -0500, John W. Colby
<jwcolby at colbyconsulting.com> wrote:
> 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
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>