Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    54

    Unanswered: Import form Excell to Access using VBA code

    Hi guys
    Id' like to import a table from Excell to Access;
    I've created the empty table,
    and I'd like to fill it using excell values.
    The code that I've managed to try is :
    Sub xlm()
    Dim xl As Excel.Application
    Dim xlsht As Excel.Worksheet
    Dim xlWrkBk As Excel.Workbook
    Dim myRec As DAO.Recordset
    Dim xlApp As Excel.Application
    Dim xlWrksht As Excel.Worksheet


    Set myRec = CurrentDb.OpenRecordset("tbl3")
    Set xlApp = CreateObject("Excel.Application")
    Set xlWrksht = xlApp.Open("C:\Users\gsirico\Documents\a.xls").Wor kseet("CODIFICA")

    myRec.AddNew
    myRec.Fields("COD_CIE") = xlWrksht.Cells(2, "A")
    myRec.Fields("CT_OPR") = xlWrksht.Cells(2, "B")
    myRec.Fields("CD_MVT") = xlWrksht.Cells(2, "C")
    myRec.Fields("CT_MVT") = xlWrksht.Cells(2, "D")
    myRec.Fields("CD_RGL_FIN") = xlWrksht.Cells(2, "E")
    myRec.Fields("CD_MODEPAIE") = xlWrksht.Cells(2, "F")
    myRec.Fields("CODMAD") = xlWrksht.Cells(2, "G")
    myRec.Update



    End Sub

    But unfortunatly I've stuck on a strange error:
    Object doesen't support this prorperty or method;
    how can I manage to do this?
    thank in advance!!!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You must respect the object model of Excel. Try this:
    Code:
    Set xlApp = CreateObject("Excel.Application")
    Set xlWrkBk = xlApp.Workbooks.Open(""C:\Users\gsirico\Documents\a.xls"")
    Set xlWrksht = xlWrkBk.Sheets("CODIFICA")
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    54
    thank you!!! as usual you 've saved me!!!!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As usual too, you're welcome !
    Have a nice day!

  5. #5
    Join Date
    Jan 2012
    Posts
    54
    hi I'm in trouble again:
    the code I've tryed out does.t works properly for me:
    I'd like to do this importation untill the end of File xls, but for some strange reason that I'cant undestand,
    the do ... Untill EOF construct doesen't works!!!!:
    here is my code:
    Do

    myRec.AddNew
    'myRec.Fields("COD_CIE") = xlWrksht.Cells(2, "A")
    myRec.Fields("CT_OPR") = xlWrksht.Cells(2, "B")
    myRec.Fields("CD_MVT") = xlWrksht.Cells(2, "C")
    myRec.Fields("CT_MVT") = xlWrksht.Cells(2, "D")
    myRec.Fields("CD_RGL_FIN") = xlWrksht.Cells(2, "E")
    myRec.Fields("CD_MODEPAIE") = xlWrksht.Cells(2, "F")
    'myRec.Fields("CODMAD") = xlWrksht.Cells(1, "G")


    Loop Until EOF(xlWrkBk)
    myRec.Update

    End Sub
    the error is the method does't support the construct......!!!!!!


    the second issue that i've met in this part is that for the code commented the there is a problem: he says thath the cell are empty when they are not!!!!!
    help me if you can!!!
    Last edited by jsirico; 01-24-12 at 05:53.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    EOF cannot be use in this case. This function returns a Boolean value True when the end of a file opened for Random or sequential Input has been reached
    meaning a file opened with:
    Code:
    Open "FileName" For Input As #1
    or:
    Code:
    Open "FileName" For Random As #1 Len = Len(MyRecord)
    (EOF Function - Access - Office.com).

    You need to find another way to determine when there are no more data to be read (empty cell, special value, end of range address reached, etc.).
    Have a nice day!

  7. #7
    Join Date
    Jan 2012
    Posts
    54
    ok thanks to you I've been able to solve the problem(i've utilized a For construct!!!) but still the there are two fields that i'm not been able to import due to a strange error.....
    here is the code:
    Code:
    Dim xlsht As Excel.Worksheet
    Dim xlWrkBk As Excel.Workbook
    Dim myRec As DAO.Recordset
    Dim xlApp As Excel.Application
    Dim xlWrksht As Excel.Worksheet
    Dim i As Long
    
    
    Set myRec = CurrentDb.OpenRecordset("table3")
    Set xlApp = CreateObject("Excel.Application")
    
    Set xlWrkBk = xlApp.Workbooks.Open("C:\Users\gsirico\Documents\a.xls")
    Set xlWrksht = xlWrkBk.Sheets("a")
     
     For i = 2 To 412
     
    myRec.AddNew
    On Error Resume Next
    myRec.Fields("COD_CIE") = xlWrksht.Cells(i, "A")
    myRec.Fields("CT_OPR") = xlWrksht.Cells(i, "B")
    myRec.Fields("CD_MVT") = xlWrksht.Cells(i, "C")
    myRec.Fields("CT_MVT") = xlWrksht.Cells(i, "D")
    myRec.Fields("CD_RGL_FIN") = xlWrksht.Cells(i, "E")
    myRec.Fields("CD_MODEPAIE") = xlWrksht.Cells(i, "F")
    myRec.Fields("CODMAD") = xlWrksht.Cells(i, "G")
    myRec.Update
    
     Next
    
    
    End Sub
    but the coloum A and G ifor access are empty while for excell they are not; any ideas????

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    What I do is link the excel file in msaccess then run a make table or a append query to a msaccess table.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    Jan 2012
    Posts
    54
    Can you be a little bit more clear?
    Why my code doesn't work properly or at least where can I find Documentation??
    Last edited by jsirico; 01-24-12 at 10:06.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •