Tranceferering data from Excel to Access 
Author Message
 Tranceferering data from Excel to Access

Hi everybody!

I hope some of you can help me with this situation:

I would like to transfer some data from Excel to Access.
The problem is that the data should be placed into the right record in the
database.
To solve this problem the name of each excel document corresponds to a
specific record in the database.

for example

the excel document is called "101.xls"

now the record has a field (Number) which should have the value "101" when
the makro puts the data in the field(called result).
The values in the field "Number" are unique for each record i.e.. the only
exists once(It's the primary key).

The makro must perform this test, otherwise the result from Excel could be
but into the wrong record, and that would spoil my idea with my Excel/Access
interaction.

Here is what I've managed to program until now:

Sub transferedata()

'get the value in Excel
x = Worksheets("Ark1").Range("C21").Value

'---------------Here the document name should be transformed into a
variable(called Excelname) without the ".xls" extension--------------

'Open access and open the right record
Set ObVar = CreateObject("Access.Application")
With ObVar
    'open document
    .OpencurrentDatabase "C:\my documents\db1.mdb"
    'open my eyes
    .Visible = True
    'open the form (which  has a Danish name!)
    .DoCmd.OpenForm ("Kalibrerings Foresp?rgsel")
    'get the right post using the variable from before
    'Docmd.GoToRecord Excelname

End with

'put the Data into the result field
Result  = x

'---------------Here the makro should update the Form or something so the
data is securely stored.----------------

.DoCmd.Save acForm, "Kalibrerings Foresp?rgsel"

End Sub

If you have questions of some kind, it would be great if you mailed me, but
I do check the NG so you don't have to.

--
__________________________
med venlig hilsen / Kind regards

Kristian M. Schneider
Gottorpvej 15
6400 s?nderborg


http://www.*-*-*.com/
tlf. +45-74485006
____________________________



Fri, 27 Apr 2001 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Trouble accessing access dbase data from excel.

2. Running excel add-in (added into excel - not access) in Access VBA

3. read excel file in access 2000 lire fichier excel dans access 2000

4. OLE Access Excel (Datenimport von Excel in Access - muss nicht OLE sein)

5. Access to Excel; Excel to Access

6. Copying Data from Access to Excel

7. Sending data to excel from access - automation 97.

8. sending data from an access VB module to excel

9. How to Drag Down Data Like in Excel (but in Access)

10. Importing select Excel data into Access 97 tables

11. Urgent : Exporting Access 97 data to Excel 97

12. Sending and manipulating data from Access 97 to Excel 97

 

 
Powered by phpBB® Forum Software