Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Import to Access null issue
Hi,
I used this code I found on MrExcel.com however, if my Excel spreadsheet has a null value in the field, it stops. Can someone assist in modifying the code below to account for a null value in a field? Thanks, 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=C:\FolderName\DataBaseName.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
access & excel issue | Excel Discussion (Misc queries) | |||
Data import issue from Access | New Users to Excel | |||
issue with excel 2003 data import | Excel Discussion (Misc queries) | |||
Excel to Access (new Issue) | Excel Programming | |||
I can import Access Tables. But, I can't import Access queries | Excel Programming |