Import Excel to Access 2000

[alastair69]

Hello One and all,

I hope you are all well and can spend a little bit of your Precious time to help me out.

I am tring to import a perdifined excel document, the Excel document is used as a template so all the heading stay static though out the document.

This is a double question:

1. Importing
I have looked though the forum and have found some code that looks like
it would work for me, before i post the code, I need to clarify what my
headings are so here goes.

A1 (Cell No) = "A/C" (Excel Name) Should be placed in "Account
Reference" (Access Field Name). Table "JT Client List"

B1 (Cell No) = "Name" (Excel Name) Should be placed in "ClientLookup"
(Access Field Name, this needs to be checked for duplicates and if in
the database then do not over write).Table "JT Clients List"

E1 (Cell No) = "Contact" (Excel Name) Should be placed
in "ContactName" (Access Field Name, this needs to be checked for
duplicates and if in the database then do not over write). Table "JT
Contact List"
****************************************************************
Code Used: (Issued by Stan)

Dim startRow As Integer
Dim totalRows As Integer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim i As Integer
Dim strTargetCC As String

startRow = Selection.Row
totalRows = Selection.Rows.Count

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\CAS\cost_accounting.mdb;"
' open a recordset
Set rs = New ADODB.Recordset

rs.Open "DIST_KEYS", cn, adOpenKeyset, adLockOptimistic, adCmdTable

r = startRow

i = 1
Do While i <= totalRows
If CcTargetCon = True Then
strTargetCC = Left(Range(CcTargetColumn & r).Value, 6)
Else
strTargetCC = Range(CcTargetColumn & r).Value
End If

With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("CC_ORIGIN") = CcOrigin
.Fields("REK_NR") = RekNr
.Fields("CC_TARGET") = strTargetCC
.Fields("PERCENT") = Range(PercentColumn & r).Value
.Fields("Not_Like") = NotLikeInd
.Update ' stores the new record
End With
i = i + 1
r = r + 1 ' next row
Loop
rs.Close
cn.Close
****************************************************************
I would persum that the .fields("IS THIS EXCEL REF ") = (IS THIS ACCESS REF).

Can someone confirm this is the case and how to add an check for duplicate entries and on a last note how to switch table for contacts.

Thanks

Alastair

 

[Bat17]

I would persum that the .fields("IS THIS EXCEL REF ") = (IS THIS ACCESS REF).

Other way around! Fields in access and Ranges in Excel.

For duplicates I would set the table to no duplicates and trap the error when you try to create one, but then I am lazy

Peter

 

[alastair69]

I would persum that the .fields("IS THIS EXCEL REF ") = (IS THIS ACCESS REF).

Other way around! Fields in access and Ranges in Excel.

For duplicates I would set the table to no duplicates and trap the error when you try to create one, but then I am lazy

Peter

Thanks Peter will try and see if it all goes pete tong or not by fingers are crossed.

Crossing fingers makes it hard to type

Alastair