truzilla
07-09-2008, 08:47 AM
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:
For Each Cel In Sheets("ABC Access Format").Range("B1:L" & [B65536].End(xlUp).Row)
but i'm not sure....
Heres the full thing:
ANY HELP WOULD BE GREAT, thanks!!!! :bow:
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
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:
For Each Cel In Sheets("ABC Access Format").Range("B1:L" & [B65536].End(xlUp).Row)
but i'm not sure....
Heres the full thing:
ANY HELP WOULD BE GREAT, thanks!!!! :bow:
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