Broken macro - can't transfer from excel to access!

[truzilla]

Hi all,

So I basically created this code with the help of one of the admins and i'm trying to test it on one column only to see if it works. It copies the data over, but its incorrect. For some reason there are like 59,000 rows when there should only be 17,000 when trying to copy the data over to access. can anybody tell me whats wrong? I'm thinking it has to do with the sheet selection portion:

[VBA]For Each Cel In Sheets("ABC Access Format").Range("B1:L" & [B65536].End(xlUp).Row)
[/VBA]

but i'm not sure....

Heres the full thing:
ANY HELP WOULD BE GREAT, thanks!!!!

[VBA]Sub Exports()
'Requires reference to Microsoft DAO 3.6 Object Library

Dim LRw As Long
Dim Cel As Range
Dim Date1 As Date
Dim Revenue As Long
Dim Region As String
Dim Level As String


Dim dbs As DAO.Database
Dim rst As DAO.Recordset


'Establish connection
Set dbs = OpenDatabase("C:\Documents and Settings\database")
'Fill recordset
Set rst = dbs.OpenRecordset("ABC")
With rst
' add values to each field in the record
For Each Cel In Sheets("ABC Access Format").Range("B1:L" & [B65536].End(xlUp).Row)
If Cel > 0 Then
.AddNew ' create a new record
' Date1 = Sheets("Access Format").Cells(Cel.Row, 1)
Region = Sheets("ABC Access Format").Cells(Cel.Row, 2)
' Level = Sheets("Access Format").Cells(3, Cel.Column)
' Revenue = Sheets("Access Format").Cells(4, Cel.Column)
' .Fields("Date") = Date1
.Fields("Region") = Region
' .Fields("LVL 4") = Level
' .Fields("Revenue") = Revenue
.Update ' stores the new record
End If
Next
End With
skipped:
Exit Sub
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub[/VBA]

[bg52]

I think you want one Access record per Excel row, but you are creating a record for each cell in the range. You need something like this, which allows you to process each cell in a row, one row at a time:

Sub ShowData()
Dim DataRow As Range
With Sheets(1).Range("B1:L" & [B65536].End(xlUp).Row)
For Each DataRow In .Rows
Debug.Print DataRow.Cells(1, 1).Value, DataRow.Cells(1, 2).Value 'etc.
Next
End With
End Sub

[Norie]

There is no sheet selection portion.

But I can see one potential problem.

By using [B65536] without a worksheet reference then VBA will be looking at column B on what it considers the active sheet.
[vba]
For Each Cel In Sheets("ABC Access Format").Range("B1:L" & Sheets("ABC Access Format").Range("B" & Rows.Count).End(xlUp).Row) [/vba]

[truzilla]

im not sure what you mean bg52, but I tried using Norie's suggestion but still too many rows and the data is being copied incorrectly..=/

[Norie]

I think you should probably try a different method of looping through the data.

What VBA sees as the lastrow of data might not be what you think it should be.

That could be for a number of reasons eg formulas, formatting etc.

[bg52]

I think you should probably try a different method of looping through the data.

That was the point I was making; I think we need to loop through the Rows collection of the range rather than the Cells collection.

In the original code, a new Access record is created for every non-zero cell in the range.

For example: if the range is B1:L100, this would give 1100 records (if each cell contains a non-zero value), as there are 11 columns of 100 rows in the range.