vba to import specific colums on a .xls sheet to a access table

[Rameez]

VBA to import specific tables from a .xls sheet to a access table.

Currently I have a code that works fine...but that works only if the file and the table are same...I want to import specific colums from a spreadsheet to a access table.



Any vba code examples are welcome.

here's the code which i got on googling...but it doesn't run...a error msg on range undefined pops up...Please help.

sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\slatool.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Temp", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


Also in cn.open....i am hardcoding the name of my mbd file...i hope there's a workaround by which...ecen if the user changes the filename....the database will work.

please help me out.

[jase_kross]

Rameez - I hope that since your post you have been able to get your code running.

Anyhow, I wanted to make some comments about the code in your post.

An attempt to execute the block

sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & CurrentProject.Path & "\slatool.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "Temp", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

from within Access could result in a sub or function not defined error as 'Range' is not part of the Access Object model but rather it is part of the Excel Object model. Also, as the comments from the block seem to imply, the intention of the procedure is to export data from the active worksheet to a table in an Access database. This implies that the above procedure is/was meant to be used from within Excel.

I tested the above block in an Excel workbook (set reference to ADO) with the code placed in the WorkBook module of the workbook. I also set up a "Temp" table in a slatool.mdb with the fields: FieldName1, FieldName2, and FieldName3 (all of type 'text') and placed the slatool.mdb file within the same directory as the Excel workbook. I only changed the code 'CurrentProject.Path' (highlighted in red) above to 'ThisWorkbook.Path' as CurrentProject.Path is not defined within the Excel Object model. Once I made this adjustment I was able to execute the code without any problems (the database should be closed) from within Excel.

[Rameez]

yeah i did it...i first put all the data in an excel into a access table and then ran query over that table...