Solved

copy data from EXCEL to Ms. ACCESS

Posted on 2002-04-08
6
144 Views
Last Modified: 2010-05-02
Hi there... here is the question on how to copy the data from EXCEL to ACCESS's DB in VB coding.  I managed to establish both EXCEL & ACCESS in VB. Currently I plan to copy data from Excel, say first cell into ACCESS's DB.

I used this but it doesn't work.
rs!FullName = xlSheet.cells(1,1)

But then if I replaced xlSheet.cells,
rs!FullName = "GRACE", it works!!

ERROR >> Object variable or with block variable not set.


-----------------------------

Dim cnnFind As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet


cnnFind.ConnectionString = "Provider=......Data source... XXX.mdb"
Set rs = New ADODB.Recordset
rs.Open <tableName>
Set xlApp = New Excel.Application
Set xlSheet = New Excel.Worksheet
Set xlSheet = New Excel.Workbook
Set xlBook = GetObject(<Excel path>)

rs.AddNew
rs!FullName = xlSheet.cells(1,1)


>>>>>>>>>>>>>>>>>> Ran until last coding it doesn't work, may I know what should I do next?
0
Comment
Question by:shliau
6 Comments
 
LVL 1

Expert Comment

by:Madmarlin
ID: 6924754

If your xlsheet.cells(1,1) property is popukated and seems to be referencing the correct values then you need an .Update method.

eg

rs.AddNew
rs!FullName = xlSheet.cells(1,1)
rs.Update.

Madmarlin
0
 
LVL 51

Accepted Solution

by:
Ryan Chong earned 50 total points
ID: 6924758
Hi shliau,

Set xlSheet = New Excel.Worksheet
Set xlSheet = New Excel.Workbook
??

Here is an example, customize it to see whether it works:

Private Sub example()
    Dim iExcel As New Excel.Application
    Dim iExcelWB As Excel.Workbook
    Dim iExcelWS As Excel.Worksheet
   
    'iExcel.Visible = True
   
    If Dir$(TargetFile) <> "" Then
        iExcel.Workbooks.Open TargetFile
    Else
        iExcel.Workbooks.Add
    End If
    i = iExcel.Workbooks.Count
    Set iExcelWB = iExcel.Workbooks(i)    
   
    Set iExcelWS = iExcelWB.Worksheets(j)
    'Set iExcelWS = iExcelWB.ActiveSheet

    DoEvents
   
    Msgbox iExcelWS.Cells(1, 1).Value '<< Here

        iExcelWB.Save
        iExcelWB.Close
        iExcel.Quit
        Set iExcelWS = Nothing
        Set iExcelWB = Nothing
        Set iExcel = Nothing
End Sub

Cheers
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6924826
Hi shliau,

you could also walk the tree down the objectmodel which is easier in reading and maintanence

Set xlApp = New Excel.Application
xlApp.Workbooks.Open "Yourname.xls"
xlApp.ActiveWorkbook.Sheets(1).Cells(1,1)

In the code above a little change is needed to tell excel which book and which sheet to use

Set xlApp = New Excel.Application
Set xlSheet = New Excel.Worksheet
Set xlSheet = New Excel.Workbook
Set xlBook = xlApp.Workbooks.open(<Excel path>)

HTH:O)Bruintje
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:bruintje
ID: 6924828
make that

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.open(<Excel path>)
Set xlSheet = xlBook.Sheets(1)

0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7643005
Hi shliau,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept ryancys's comment(s) as an answer.

shliau, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:kodiakbear
ID: 7673194
Moving to the PAQ

kb
Experts Exchange Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Hide vba in gp 7 115
Spell Check in VB6 13 137
How to hault or freeze parent form when a 2d form is open in vb6 3 43
Child Form in front 4 56
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question