chaostheory
Registered User.
- Local time
- Today, 10:36
- Joined
- Sep 30, 2008
- Messages
- 69
I am relatively new, ok totally new to access and VBA but i pick up quickly. Im having a problem putting information into access from excel. I basically have a spreadsheet that i have doing various things and formatting data to the way it needs to be to be put into the table. Some of the fields are empty some are populated etc. I did this for another spreadsheet and it works beautifully but for some reason on this one, set up the same with with DAO reference, and identical code (just more fields), it is not working its hanging up on one specific line each time. Can you pleassse take a look at it and give me some insight!!
The red line of code is the line it hangs on, and it hangs on the second pass. The field has a number on the first pass and on the second pass that field is blank. Data type of the cell is number to 2 decimal places, same thing for the field in access, number with 2 dec places. I dont get why there would be a datatype conversion error..when they are the same.
The red line of code is the line it hangs on, and it hangs on the second pass. The field has a number on the first pass and on the second pass that field is blank. Data type of the cell is number to 2 decimal places, same thing for the field in access, number with 2 dec places. I dont get why there would be a datatype conversion error..when they are the same.
Code:
Sub FromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("Z:\_AC_LBP\Database\03 Converted AC Tracking - Raw Data.mdb")
' open the database
Set rs = db.OpenRecordset("Bare Characterization", dbOpenTable)
' get all records in a table
Sheets("Sheet1").Select
r = 2 ' the start row in the worksheet
Do While Len(Range("B" & r).Formula) > 0
' repeat until first empty cell in column B
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Piece ID") = Range("A" & r).Value
.Fields("Skid") = Range("B" & r).Value
.Fields("If Reject, Defect") = Range("C" & r).Value
.Fields("Version of Char Targets") = Range("D" & r).Value
.Fields("Comments") = Range("E" & r).Value
.Fields("Ultrasound Results") = Range("F" & r).Value
.Fields("Light Box Date") = Range("G" & r).Value
.Fields("Light Box Unit") = Range("H" & r).Value
.Fields("Light Box Set-up") = Range("I" & r).Value
.Fields("Light Box Qnt Leaks Next to Skin") = Range("J" & r).Value
.Fields("Light Box Qnt Leaks NOT Next to Skin") = Range("K" & r).Value
.Fields("All Light Box Leaks Repaired") = Range("L" & r).Value
.Fields("Adj Plug Depth F1-1 cnt (mm)") = Range("M" & r).Value
.Fields("Adj Plug Depth F1-2 (mm)") = Range("N" & r).Value
.Fields("Adj Plug Depth F1-3 (mm)") = Range("O" & r).Value
.Fields("Adj Plug Depth F1-4 (mm)") = Range("P" & r).Value
.Fields("Adj Plug Depth F1-5 (mm)") = Range("Q" & r).Value
.Fields("Adj Plug Depth F2-1 cnt (mm)") = Range("R" & r).Value
.Fields("Adj Plug Depth F2-2 (mm)") = Range("S" & r).Value
.Fields("Adj Plug Depth F2-3 (mm)") = Range("T" & r).Value
.Fields("Adj Plug Depth F2-4 (mm)") = Range("U" & r).Value
.Fields("Adj Plug Depth F2-5 (mm)") = Range("V" & r).Value
.Fields("Plug Strength") = Range("W" & r).Value
.Fields("SuperFlow Date") = Range("X" & r).Value
.Fields("SuperFlow Unit") = Range("Y" & r).Value
.Fields("SuperFlow Set-up") = Range("Z" & r).Value
[COLOR=red] .Fields("BP at 150cfm (In H2O)") = Range("AA" & r).Value[/COLOR]
.Fields("BP at 177cfm (In H2O)") = Range("AB" & r).Value
.Fields("BP at 300cfm (In H2O)") = Range("AC" & r).Value
.Fields("BP at 353cfm (In H2O)") = Range("AD" & r).Value
.Fields("BP at 450cfm (In H2O)") = Range("AE" & r).Value
.Fields("BP at 600cfm (In H2O)") = Range("AF" & r).Value
.Fields("BP at 706cfm (In H2O)") = Range("AG" & r).Value
.Fields("BP at 750cfm (In H2O)") = Range("AH" & r).Value
.Fields("BP at 900cfm (In H2O)") = Range("AI" & r).Value
.Fields("Laser Gauge Date") = Range("AJ" & r).Value
.Fields("Contour Number") = Range("AK" & r).Value
.Fields("Nominal Height (mm)") = Range("AL" & r).Value
.Fields("Laser Gauge Software") = Range("AM" & r).Value
.Fields("Laser Gauge ID") = Range("AN" & r).Value
.Fields("3D Cylindricity (mm)") = Range("AO" & r).Value
.Fields("Tube Gage (mm)") = Range("AP" & r).Value
.Fields("Template (mm)") = Range("AQ" & r).Value
.Fields("Contour Avg Axis Major") = Range("AR" & r).Value
.Fields("Contour Avg Axis Minor") = Range("AS" & r).Value
.Fields("3D Tubegage (mm)") = Range("AU" & r).Value
.Fields("3D Max Diameter (mm)") = Range("AV" & r).Value
.Fields("Piece Bow (mm)") = Range("AW" & r).Value
.Fields("Average Height Deviation (mm)") = Range("AX" & r).Value
.Fields("Perpendicularity 360") = Range("AY" & r).Value
.Fields("Perp Major Axis") = Range("AZ" & r).Value
.Fields("Perp Minor Axis") = Range("BA" & r).Value
.Fields("Shape (mm)") = Range("BB" & r).Value
.Fields("Average Diameter (mm)") = Range("BC" & r).Value
.Fields("Out of Round (mm)") = Range("BD" & r).Value
.Fields("Parallelism MMC (mm)") = Range("BE" & r).Value
.Fields("Parallelism MM (mm)") = Range("BF" & r).Value
.Fields("Parallelism TIR (mm)") = Range("BG" & r).Value
.Fields("Parallelism E-C-E (mm)") = Range("BH" & r).Value
.Fields("Parallelism C-E (mm)") = Range("BI" & r).Value
.Fields("Parallelism A-E (mm)") = Range("BJ" & r).Value
.Fields("Weight Before Neb (g)") = Range("BK" & r).Value
.Fields("Weight After Neb (g)") = Range("BL" & r).Value
.Fields("Neb Date") = Range("BM" & r).Value
.Fields("Neb Time") = Range("BN" & r).Value
.Fields("Neb Unit") = Range("BO" & r).Value
.Fields("Neb Set-up") = Range("BP" & r).Value
.Fields("Neb Leaks Observed") = Range("BQ" & r).Value
.Fields("Max Number of Adj Leaks") = Range("BR" & r).Value
.Fields("Visual Inspection Results") = Range("BS" & r).Value
.Fields("Order ID") = Range("BT" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
msgbox (r - 2 & " rows of data were added to the Bare Characterization table")
End Sub