[AccessD] Excel to Access

How about using ADO some really  rough samples
I believe you can use ADO right down to the range of a worksheet


'*******************************************
'* ADO to open Excel97 sheet as db/recordset *
'*******************************************
Dim cnnDB As adodb.Connection
Dim rsADO As adodb.Recordset
Dim strXLPath As String

Set cnnDB = New adodb.Connection
Set rsADO = New adodb.Recordset
strXLPath = "C:\SomeFolder\Your.xls"

' Specify Excel 8.0 by using the Extended Properties property,
' and then open the Excel file specified by strDBPath.
With cnnDB
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .Properties("Extended Properties") = "Excel 8.0"
  ' .Properties("Extended Properties=")="Excel 8.0;HDR=Yes"
   .Open strXLPath
End With

'open recordset from a worksheet - the name of the worksheet must be
'  placed in brackets with a dollar sign appended to sheet name.
With rsADO        
    ' use a lock type other than the default (adLockReadOnly) if you
    ' want to be able to update the recordset - activate next line
    '.LockType = adLockOptimistic

    ' Open the recordset with the SQL command you want
    .Open "SELECT * FROM [SomeSheet$] WHERE LastName = 'Doe'", _
          cnnDB, adOpenDynamic, , adCmdText
    'recordset is now open - perform any operations on it
       
    'when you're done:
    .Close
End With
or
----------


Dim xlsConnection As new ADODB.Connection
Dim rs As ADODB.Recordset
Dim fs As New FileSystemObject


If fs.FileExists(sFilename) Then
   Err.Number = 0
   fs.DeleteFile sFilename, True
End If


xlsConnection.Mode = adModeWrite


xlsConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
sFilename & ";Extended Properties=Excel 8.0;"
' Although Excel doesn't have the notion of a "column type" like a database
does you still need to give
' a valid schema fot the create table command...


xlsConnection.Execute "create table [First Sheet] (column1 int, [another
column] varchar(50) )"
xlsConnection.Execute "create table [Second Sheet] (column1 numeric(???),
etc )"


Set rs = record set for first sheet, but I wont actually use it here...


While Not rs.EOF
   xlsConnection.Execute "insert into [First Sheet] (column1, [another
column]) values (123, 'abc')"
   rs.MoveNext
Wend

Reuben Cummings wrote:

>How could I go about exporting specific portions of an excel spreadsheet to
>a CSV from Access?
>
>I would prefer to have them open the Access app and click a button to bring
>the data in.  The function in Access would send the data from the specified
>ranges (which would be hard coded for now) to a CSV, import that to a table,
>and then format and place the data accordingly.
>
>Reuben Cummings
>GFC, LLC
>812.523.1017
>
>
>  
>
>>-----Original Message-----
>>From: accessd-bounces at databaseadvisors.com
>>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
>>Sent: Wednesday, December 07, 2005 10:16 AM
>>To: 'Access Developers discussion and problem solving'
>>Subject: Re: [AccessD] Excel to Access
>>
>>
>>Reuben,
>>
>>Excel is fraught with danger when used directly, ESPECIALLY when
>>it is hand
>>edited.  The reason is that the data can change data types down a column,
>>let's say it started as a number, but they then cut and pasted something
>>that Excel thinks is a string (but looks like a number).  When you link to
>>that sheet, you will end up with #error or something similar scattered
>>through your data.  If you must do this, then immediately export
>>the data to
>>a CSV file.  The reason for doing that is that a CSV file has
>>nothing in it
>>indicating what the data actually is, thus Access guesses when you set up
>>the import.  Further you can then specify each field intentionally if you
>>need to do so.
>>
>>So export to CSV
>>Import the csv data to a temp table
>>Work from that temp table.
>>
>>
>>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 Reuben Cummings
>>Sent: Wednesday, December 07, 2005 10:02 AM
>>To: AccessD
>>Subject: [AccessD] Excel to Access
>>
>>Could someone point me to or provide a nice way to import Excel data in
>>Access on a daily basis.
>>
>>A potential clients uses Excel daily to calculate some stuff about Mutual
>>funds.  They then want to send that data to Access at the end of every day
>>in order to have a history of the funds.
>>
>>Thanks.
>>
>>Reuben Cummings
>>GFC, LLC
>>812.523.1017
>>
>>
>>
>>
>>--
>>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
>>
>>    
>>
>
>
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada