exporting data to access

[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é.

 

[George Nicholson]

1) your loops aren't in sync and they aren't nested as they should be.

Pick One:

For k = 3 To CF
For i = 7 To LF
.....................
Next i
Next k

*OR*

For i = 7 To LF
For k = 3 To CF
........................
Next k
Next i

2)

rs("period") = Cells(6, k)

HTH,





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é.