[AccessD] Importing Multple Excel Worksheets

John,

Run the append query directly against the spreadsheet.  I create the query
in a string with the file path in the variable me.txtFilePath which comes
from the dialog form. 

Here is an example.

strSQL = "INSERT INTO tblImportTemp ( fldOrID, fldOrAssayID, fldTarget,
fldReplicate, " _
                & "fldDrugConcentration, fldModifier,
fldModifierConcentration, fldResult )" _
                & "SELECT [for Access$].OR_ID, [for Access$].Assay_ID, [for
Access$].Target, " _
                & "[for Access$].Replicate, [for Access$].[Conc(uM)],
IIf(IsNull([for Access$].[Modifier]),""No_Mod"",[for Access$].[Modifier]) AS
lModifier, " _
                & "IIf(IsNull([for Access$].[Modifier_Conc]),0,[for
Access$].[Modifier_Conc]) AS lModifier_Conc, [for Access$].Percent " _
                & "FROM [for Access$] IN '" & Me.txtFilePath & "'[Excel 5.0;
];"

There are several immediate If statements to make sure null fields have
something in them.

Doug

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Saturday, September 10, 2005 10:40 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Importing Multple Excel Worksheets


Doug,

How do you do this?  Do you link the spreadsheet to the database first? Does
your query use an IN clause to treat the spreadsheet as a database somehow?

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 Doug Murphy
Sent: Saturday, September 10, 2005 12:54 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Importing Multple Excel Worksheets


Rocky,

If the spreadsheet has a fixed format you can do this with queries.  I have
an app that a customer uses for this very purpose.  I have it set up with
the file explorer dialog so they can point to the file and bring their data
into a temp table where we do some sanity checks on the data and then parse
it out to the appropriate tables through some more queries.  If your
interested let me know off line and I'll see if I can pull out the query and
dialog part and send over.

Doug

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin -
Beach Access Software
Sent: Saturday, September 10, 2005 7:41 AM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Importing Multple Excel Worksheets


Dear List:

I need to convert client data on a regular basis from an Excel workbook
containing five worksheets.  I need to link or import the sheets separately,
of course, but neither the link wizard nor the import wizard seem to allow
you to specify the worksheet.

Is there a simple way to do this?  The user is sophisticated and can do the
manual link or import procedure.

OTOH, if I did it through code I could give them a form with an 'Import It'
button and it would be a one click operation.  I have had problems with
TransferSpreadsheet in the past, though.  

MTIA,

Rocky Smolin
Beach Access Software
http://www.e-z-mrp.com
858-259-4334
-- 
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