Export only filled cells to access

[johanssson]

I find an easy way to send data from excel sheet to access database with following VBA code :

Sub SaveOnAccess()


Dim NewCon As ADODB.Connection
Set NewCon = New ADODB.Connection


Dim Recordset As ADODB.Recordset
Set Recordset = New ADODB.Recordset


NewCon.Open "Provider=Microsoft.ace.oledb.12.0;Data Source =C:\Fredrik\ToDoFolders\Pågående\ExcelProjekt\Tidsrapporter\Version 2\Tidsrapport.accdb"
Recordset.Open "Tabell1", NewCon, adOpenDynamic, adLockOptimistic
Recordset.AddNew
Recordset.Fields(0).Value = Range("A2").Value
Recordset.Fields(1).Value = Range("B2").Value
Recordset.Fields(2).Value = Range("C2").Value
Recordset.Fields(3).Value = Range("D2").Value


'''Recordset.Fields(0).Value = Range("A3").Value
'''Recordset.Fields(1).Value = Range("B3").Value
'''Recordset.Fields(2).Value = Range("C3").Value
'''Recordset.Fields(3).Value = Range("D3").Value


'''Recordset.Fields(0).Value = Range("A4").Value
'''Recordset.Fields(1).Value = Range("B4").Value
'''Recordset.Fields(2).Value = Range("C4").Value
'''Recordset.Fields(3).Value = Range("D4").Value


Recordset.Update
Recordset.Close


End Sub



Remaining issue is now how to export all line that has a value in Column A.
A1 is the header so for instance the range A2 - A10 is the range to look into.

So if A3 is filled in with a "name" we export entire row 3 to access. If row A3 is empty we exclude it from the export.

I'm thinking of a While loop and at the same time a rowcount but I can't figure out how to to it together.

My test data is like following :

Name Value1 Value2 Value3 Value4
name1 1...........2........3........4
name2 2...........3........3........2

Thanks in advance!