johanssson
New Member
- Joined
- Sep 27, 2013
- Messages
- 25
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!
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!
Last edited: