Import form Excell to Access using VBA code

jsirico · #1 (**permalink**) 01-23-12, 11:20

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!!!!

Sinndho · #2 (**permalink**) 01-23-12, 12:02

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")

jsirico · #3 (**permalink**) 01-23-12, 12:35

thank you!!! as usual you 've saved me!!!!

Sinndho · #4 (**permalink**) 01-23-12, 12:48

As usual too, you're welcome !

jsirico · #5 (**permalink**) 01-24-12, 04:47

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!!!

Sinndho · #6 (**permalink**) 01-24-12, 05:05

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.).

jsirico · #7 (**permalink**) 01-24-12, 07:29

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????