• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

copy data from EXCEL to Ms. ACCESS

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
shliau
Asked:
shliau
1 Solution
 
MadmarlinCommented:

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
 
Ryan ChongCommented:
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
 
bruintjeCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
bruintjeCommented:
make that

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

0
 
DanRollinsCommented:
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
 
kodiakbearCommented:
Moving to the PAQ

kb
Experts Exchange Moderator
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now