Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting from Excel to Access DB
Hello all. Wondering if I could be pointed in the right direction with my
issue. I have a spreadsheet with the following range(A1:J53). In colums A thru E I have formulas which populate the cell depending on data thats entered in column F(these columns are also hidden to the user. In columns F thru J, is the spreadsheet that the user will use to fill out and enter data. I got a code on this site that will export the data on the spreadsheet into an Access db from a command button. The DB has the two date fields. The issue I'm having is that whenever I click on my command button, I get a "run time error 3421 Data type conversion error" and the debugger highlights the first date field on the spreadsheet. However, when I look at my table, the data was exported. Why would I be getting a run time error even when the data is still being exported? Is there a way around the error? I tried chaning the date fields to text in the db table, but when thats done, the button works great, however it exports all the records in the logs even the empty ones (I think its due to having formulas in range A1:E53).Any help is appreciated. Thanks. Private Sub cmdSEND_Click() 'exports data from the active worksheet to a table in an access db 'this procedure must be edited before use Dim db As Database, rs As Recordset, r As Long Set db = OpenDatabase("U:\ChangeBack Stats\$$$CHANGEBACKDATA\MY_CB_DB. mdb") 'open the database Set rs = db.OpenRecordset("tbl_main_log", dbOpenTable) 'get all records in a table r = 3 'the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 'REPEAT UNTIL FIRST EMPTY CELL IN COLUMN A With rs .AddNew 'create a new record 'add values to each field in the record .Fields("LOGDATE") = Range("A" & r).Value .Fields("lognumber") = Range("B" & r).Value .Fields("logid") = Range("c" & r).Value .Fields("employeeid") = Range("d" & r).Value .Fields("cbdate") = Range("e" & r).Value .Fields("order_rx_number") = Range("f" & r).Value .Fields("medication") = Range("g" & r).Value .Fields("conflict") = Range("h" & r).Value .Fields("saved") = Range("i" & r).Value .Fields("if_no_why") = Range("j" & r).Value .Update End With r = r + 1 Loop rs.Close Set rs = Nothing db.Close Set db = Nothing Range("a1:j53").ClearContents MsgBox "Thank You For Your ChangeBack Logs! =)", vbOKOnly ActiveWorkbook.Close True Application.Quit -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201005/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting from Excel to Access DB
Try db.open not db.openrecordset
Set rs = db.Open("tbl_main_log", dbOpenTable) "AccessUser777 via OfficeKB.com" wrote: Hello all. Wondering if I could be pointed in the right direction with my issue. I have a spreadsheet with the following range(A1:J53). In colums A thru E I have formulas which populate the cell depending on data thats entered in column F(these columns are also hidden to the user. In columns F thru J, is the spreadsheet that the user will use to fill out and enter data. I got a code on this site that will export the data on the spreadsheet into an Access db from a command button. The DB has the two date fields. The issue I'm having is that whenever I click on my command button, I get a "run time error 3421 Data type conversion error" and the debugger highlights the first date field on the spreadsheet. However, when I look at my table, the data was exported. Why would I be getting a run time error even when the data is still being exported? Is there a way around the error? I tried chaning the date fields to text in the db table, but when thats done, the button works great, however it exports all the records in the logs even the empty ones (I think its due to having formulas in range A1:E53).Any help is appreciated. Thanks. Private Sub cmdSEND_Click() 'exports data from the active worksheet to a table in an access db 'this procedure must be edited before use Dim db As Database, rs As Recordset, r As Long Set db = OpenDatabase("U:\ChangeBack Stats\$$$CHANGEBACKDATA\MY_CB_DB. mdb") 'open the database Set rs = db.OpenRecordset("tbl_main_log", dbOpenTable) 'get all records in a table r = 3 'the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 'REPEAT UNTIL FIRST EMPTY CELL IN COLUMN A With rs .AddNew 'create a new record 'add values to each field in the record .Fields("LOGDATE") = Range("A" & r).Value .Fields("lognumber") = Range("B" & r).Value .Fields("logid") = Range("c" & r).Value .Fields("employeeid") = Range("d" & r).Value .Fields("cbdate") = Range("e" & r).Value .Fields("order_rx_number") = Range("f" & r).Value .Fields("medication") = Range("g" & r).Value .Fields("conflict") = Range("h" & r).Value .Fields("saved") = Range("i" & r).Value .Fields("if_no_why") = Range("j" & r).Value .Update End With r = r + 1 Loop rs.Close Set rs = Nothing db.Close Set db = Nothing Range("a1:j53").ClearContents MsgBox "Thank You For Your ChangeBack Logs! =)", vbOKOnly ActiveWorkbook.Close True Application.Quit -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201005/1 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting from Excel to Access DB
Mike wrote:
Try db.open not db.openrecordset Set rs = db.Open("tbl_main_log", dbOpenTable) Hello all. Wondering if I could be pointed in the right direction with my issue. [quoted text clipped - 52 lines] ActiveWorkbook.Close True Application.Quit Mike, thanks for your response. I got it to work by changing the following line from Do While Len(Range("A" & r).Formula) 0 to Do While Len(Range("A" & r).Cells.Value) 0 so now its only looking for any row with values vs an empty cell that contained formulas -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201005/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting from access into excel | Excel Programming | |||
Exporting to excel from Access | Excel Programming | |||
Exporting from Access to Excel | Excel Programming | |||
Exporting from Access to Excel | Excel Programming | |||
Exporting Excel to Access | Excel Programming |