G
gatarossi
Dear all,
I'm trying to export data from excel to access, but I'm having some
difficult:
The layout of my sheet is the following:
A B C D
E F
1
2
3 3*
4
5
6 0701 0702
0703 0704**
7 10*** salary 100 101 102
103
8 20 others 50 51
52 53****
*profi_center_code
** periods
*** accounting_code
**** values
Layout of the table in access:
automatic numbering / accounting_code / profit_center_code /
period / transaction_value
Then I need to export everything by line and by column, but the code
below doesn't work!!! Could I have the correct code?
Sub exporta_despesas_dao()
Dim wrkSpace As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
strDB = ThisWorkbook.Path & "\expenses_control.mdb"
Set wrkSpace = Workspaces(0)
Set db = wrkSpace.OpenDatabase(strDB)
Set rs = db.OpenRecordset("expenses_budget", dbOpenTable)
LF = Cells(65536, 1).End(xlUp).Row
CF = Cells(6, 256).End(xlLeft).Column
For i = 7 To LF
For k = 3 To CF
rs.AddNew
rs("accounting_code") = Cells(i, 1)
rs("profit_center_code") = Cells(3, 2)
rs("period") = Cells(6, 3)
rs("transaction_value") = Cells(i, k)
rs.Update
Next i
Next k
rs.Close
db.Close
wrkSpace.Close
Set rs = Nothing
Set db = Nothing
Set wrkSpace = Nothing
MsgBox "ok", vbInformation
End Sub
Thanks,
André.
I'm trying to export data from excel to access, but I'm having some
difficult:
The layout of my sheet is the following:
A B C D
E F
1
2
3 3*
4
5
6 0701 0702
0703 0704**
7 10*** salary 100 101 102
103
8 20 others 50 51
52 53****
*profi_center_code
** periods
*** accounting_code
**** values
Layout of the table in access:
automatic numbering / accounting_code / profit_center_code /
period / transaction_value
Then I need to export everything by line and by column, but the code
below doesn't work!!! Could I have the correct code?
Sub exporta_despesas_dao()
Dim wrkSpace As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
strDB = ThisWorkbook.Path & "\expenses_control.mdb"
Set wrkSpace = Workspaces(0)
Set db = wrkSpace.OpenDatabase(strDB)
Set rs = db.OpenRecordset("expenses_budget", dbOpenTable)
LF = Cells(65536, 1).End(xlUp).Row
CF = Cells(6, 256).End(xlLeft).Column
For i = 7 To LF
For k = 3 To CF
rs.AddNew
rs("accounting_code") = Cells(i, 1)
rs("profit_center_code") = Cells(3, 2)
rs("period") = Cells(6, 3)
rs("transaction_value") = Cells(i, k)
rs.Update
Next i
Next k
rs.Close
db.Close
wrkSpace.Close
Set rs = Nothing
Set db = Nothing
Set wrkSpace = Nothing
MsgBox "ok", vbInformation
End Sub
Thanks,
André.