Selecting Specific Cell in Excel with ADO 1

[joebb3]

How would I modify the code at the bottom (BOLDED) to accomplish the following excel import statements? I need to import specific cells from the datasheet.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "CurrentUIC", filenameimp, True, [b]"PRTEdit!a1:a2"[/b]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "ImportAllData", filenameimp, True, [b]"PRTEdit!a4:n"[/b]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "ImportBCAData", Finalsaveb, True, [b]"BCEdit!a4:q"[/b]

Public Function UpdateExcel()
On Error GoTo ErrHandler
Dim cn As New Connection, cn2 As New Connection
Dim rs As New Recordset, rs2 As New Recordset
Dim connString As String, connString2 As String
Dim sql1 As String, sql2 As String
[b]connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\ATestDir\myTest.xls;" & _
            "Extended Properties=""Excel8.0;HDR=Yes;"";"[/b]
cn.ConnectionString = connString
cn.Open connString
''cn2.Open connString

Set rs.ActiveConnection = cn
'-- sheet name = newcustomers
sql1 = "select * from newcustomers"
rs.Open sql1, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
    Debug.Print "field value = "; rs.Fields(0).Value
End If
'--- you could insert code to loop and write text file here.
Exit Function
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.description
For Each er In cn.Errors
    Debug.Print "err num = "; Err.Number
    Debug.Print "err desc = "; Err.description
    Debug.Print "err source = "; Err.Source
Next
End Function

above code credited to cmmrfrds

Thanx,
Joe

 

[CautionMP]

[tt]sql1 = "SELECT * FROM [PRTEdit$a1:a2]"[/tt]

MSDN: How To Use ADO with Excel Data from Visual Basic or VBA

Hope this helps,
CMP


(GMT-07:00) Mountain Time (US & Canada)

 

[joebb3]

Right on! Thanks for all the info! And the web page! "Teach a man to fish..."

My database still gets HUGE after data import from excel... 178MB that compresses down to 2MB...

The import does A LOT od sql queries... Would THAT be causing it?

 

[CautionMP]

Could be. Bloat is usually caused by deleted items, the space they take up doesn't actually get removed from the database until it is compacted.

Usual suspects:[ul]
[li]Temporary stroage Tables (data that gets appended at the begining of a process and deleted at the end).[/li][li]Indexed fields (these are actually tables).[/li][/ul]

What's the best way to address this?
I don't know, it depends on so many things (both database and developer).

Places to start:[ol]
[li]Eliminate temporary storage in the database.[/li]
[li]Review your indexes.[/li][/ol]

That should make it clear as mud;-)
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[joebb3]

Basically, my code goes like this:

About 60 spreadsheets are located on a folder on the drive. I load all of the filenames into a table and import them 2 at a time into two seperate tables with DoCmd TransferSpreadsheet. I load a dictionary with user data from my "Member" table and in the first part of the import, I check the dictionary against the imported record for duplicates:

With db1r
- Check the first rec against the dictionary if its unique, import the data to a table with a straight write
as in "db1r![name]= name etc..."

The second part I import the data the same way except I use a SQL query to pull SSN and Cycle from my permanent table to see if it matches the record I'm importing.
if it exists, its a duplicate so I don't write, if the query returns 0 records, I write to a related table in the same manner as above.

The third part I use two SQL queries to compare data, if they don't match I import the record with a write as above.

Then I loop back for every person in the first set of spreadsheets. (Anywhere from 10-60 records)

then I loop back and get the next two spreadsheets and start over till I go through all the XLS files.

At the end of each loop, I delete all temporary tables (Tables holding the import data), close all objects and set them to "Nothing"

On a WAG, I recently switched to a version of the ABOVE code by cmmrfrds to import the data sheets and it works well, but doesn't help the bloat. I do not use .index...

I would classify my expertise as an Advanced Hobbyist... I don't code for a living, but creating databases such as this makes my job easier. I created another DB about a year ago and its HUGE, not in disk space size, but in the amount of data it holds and it does not suffer from this bloat. I've been pulling my hair out (whats left of it) trying to figure this out.

I'm not looking for you to solve my problem, just some advice if you can make heads or tails of my question. In the mean time, I'm going to try to work a way to compare the data without the SQL.

Thanx!
Joe