[AccessD] [dba-VB] TransferSpreadsheet, TransferText

Here are some code fragments to read a simple Excel flat file into an Access
table. If you need to read "random" cells from a spreadsheet I can post code
for that too if it would help.
Jim Hale

 'This loads the source data
'opens the ARdata table
DoCmd.RunSQL "DELETE tblARdata.* FROM tblARdata;"

Set rstBase(3) = db.OpenRecordset("tblARdata", dbOpenTable)
.Worksheets("ARData").Select
.Range("A2").Select 'select first cell of first record to read

Do While Not IsEmpty(ActiveCell)
    rstBase(3).AddNew 'create records in output table
    rstBase(3).Fields("descr") = .ActiveCell.Offset(0, 0)
    rstBase(3).Fields("AR_co1") = .ActiveCell.Offset(0, 1)
    rstBase(3).Fields("AR_co2") = .ActiveCell.Offset(0, 2)
    If Not IsNull(rstBase(3).Fields("descr")) = True Then rstBase(3).Update
    .ActiveCell.Offset(1, 0).Select 'find first cell of next record
Loop

-----Original Message-----
From: StaRKeY [mailto:starkey at wanadoo.nl]
Sent: Friday, November 28, 2003 10:48 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] [dba-VB] TransferSpreadsheet, TransferText


Hi Paul,

sorry no code but in VB you should use the Objectmodel of Excel. Create a
new instance and set the worksheet to the file. Then using the objectmodel
you're able to read out the specific values and append them to a table using
a recordset/SQL. I assume the sheet will always look the same or is used
only once.


Regards,
Eric STarkenburg

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
paul.hartland at fsmail.net
Sent: vrijdag 28 november 2003 13:07
To: dba-vb
Cc: accessd
Subject: [AccessD] [dba-VB] TransferSpreadsheet, TransferText


To all,
I'm writing a VB application which I need to import an Excel spreadsheet
and/or a text file into a database.....Is there a VB command similar to this
or does someone have some sample code that I could see to get me started...
Thanks in advance
Paul Hartland
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 28-11-2003
Tested on: 28-11-2003 17:48:26
avast! is copyright (c) 2000-2003 ALWIL Software.
http://www.avast.com



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com