Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
...more repetitive copying
I am sending information from Excel to an Access db. In this series,
I have about 100 columns of information to send. The info is on a sheet from Range(AN,2) to (EI,2). I am opening the recordset and updating information. Instead of writing the same line of code 100 times for each cell, is there an easier way to program the column to go to the next column?? If that makes sense. Below is my code so far... as always, everyone on here rocks and thanks for the help in advance. 'open permit.lifecycle recordset Set rs = New ADODB.Recordset str = "SELECT * FROM permit_life WHERE permit_num = '" & strPnum & "'" rs.Open str, cn, adOpenKeyset, adLockOptimistic ', adCmdTable ' all records in a table r = 2 ' the start row in the worksheet ' repeat until first empty cell in column A With rs .Fields("PLFrm1") = Range("AN" & r).Value .Fields("PLTo1") = Range("AO & r).Value ' 98 more fields to update up to column EI... .Update ' stores the new record End With 'close permit_info recordset and empty memory rs.Close Set rs = Nothing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
...more repetitive copying
I haven't tested but this is a direction i'd try out:
Dim ndxFlds As Integer 'Index for looping through fields Dim ndxCols As Integer 'Index for looping through .xls columns ndxCols = 40 'i.e., column AN With rs For ndxFlds = 1 To .Fields.Count .Fields(ndxFlds) = Cells(r, ndxCols) .Update ndxCols = ndxCols + 1 Next End With This, of course, presumes that the field and column order is the same and they correspond. On Jul 15, 1:21*pm, gab1972 wrote: I am sending information from Excel to an Access db. *In this series, I have about 100 columns of information to send. *The info is on a sheet from Range(AN,2) to (EI,2). *I am opening the recordset and updating information. *Instead of writing the same line of code 100 times for each cell, is there an easier way to program the column to go to the next column?? *If that makes sense. *Below is my code so far... as always, everyone on here rocks and thanks for the help in advance. * * 'open permit.lifecycle recordset * * * * Set rs = New ADODB.Recordset * * str = "SELECT * FROM permit_life WHERE permit_num = '" & strPnum & "'" * * rs.Open str, cn, adOpenKeyset, adLockOptimistic ', adCmdTable * * ' all records in a table * * r = 2 ' the start row in the worksheet * * ' repeat until first empty cell in column A * * * * With rs * * * * * * .Fields("PLFrm1") = Range("AN" & r).Value * * * * * * .Fields("PLTo1") = Range("AO & r).Value * * * * * * ' 98 more fields to update up to column EI... * * * * * * .Update ' stores the new record * * * * End With * * 'close permit_info recordset and empty memory * * rs.Close * * Set rs = Nothing |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
...more repetitive copying
This would be perfect...one caveat though...can I start the ndxFlds
count to start on the second field? I have information going from Excel to 4 different Access DB's. This is just one section of of the ws going to a specific db. The common denominator for all the db's is the permit_num - which is the primary key in each db. So, in each db, the first record is the permit number then all the other information. So, I don't want to update that. So if I use the .Fields.Count...I need for it to start counting on the second record. Make sense? Any ideas? Thanks in advance. AB wrote: I haven't tested but this is a direction i'd try out: Dim ndxFlds As Integer 'Index for looping through fields Dim ndxCols As Integer 'Index for looping through .xls columns ndxCols = 40 'i.e., column AN With rs For ndxFlds = 1 To .Fields.Count .Fields(ndxFlds) = Cells(r, ndxCols) .Update ndxCols = ndxCols + 1 Next End With This, of course, presumes that the field and column order is the same and they correspond. On Jul 15, 1:21*pm, gab1972 wrote: I am sending information from Excel to an Access db. *In this series, I have about 100 columns of information to send. *The info is on a sheet from Range(AN,2) to (EI,2). *I am opening the recordset and updating information. *Instead of writing the same line of code 100 times for each cell, is there an easier way to program the column to go to the next column?? *If that makes sense. *Below is my code so far... as always, everyone on here rocks and thanks for the help in advance. * * 'open permit.lifecycle recordset * * * * Set rs = New ADODB.Recordset * * str = "SELECT * FROM permit_life WHERE permit_num = '" & strPnum & "'" * * rs.Open str, cn, adOpenKeyset, adLockOptimistic ', adCmdTable * * ' all records in a table * * r = 2 ' the start row in the worksheet * * ' repeat until first empty cell in column A * * * * With rs * * * * * * .Fields("PLFrm1") = Range("AN" & r).Value * * * * * * .Fields("PLTo1") = Range("AO & r).Value * * * * * * ' 98 more fields to update up to column EI... * * * * * * .Update ' stores the new record * * * * End With * * 'close permit_info recordset and empty memory * * rs.Close * * Set rs = Nothing |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
...more repetitive copying
As i said, i haven't tested the code but if it works then you can
start the ndx at any number less than or equal to the final number, i.e. the only condition for this line For ndxFlds = 1 To .Fields.Count is that the first number ('1' in this instance) needs to be less or equal to field.count, so, you can sart at For ndxFlds = 1 or For ndxFlds = 2 or For ndxFlds = 3 .... Doesn't matter. The same way as i started the Column Index at 40 - it's just a number that you need. On Jul 15, 1:57*pm, gab1972 wrote: This would be perfect...one caveat though...can I start the ndxFlds count to start on the second field? *I have information going from Excel to 4 different Access DB's. *This is just one section of of the ws going to a specific db. *The common denominator for all the db's is the permit_num - which is the primary key in each db. *So, in each db, the first record is the permit number then all the other information. So, I don't want to update that. *So if I use the .Fields.Count...I need for it to start counting on the second record. *Make sense? *Any ideas? *Thanks in advance. AB wrote: I haven't tested but this is a direction i'd try out: Dim ndxFlds As Integer 'Index for looping through fields Dim ndxCols As Integer 'Index for looping through .xls columns ndxCols = 40 'i.e., column AN With rs * * For ndxFlds = 1 To .Fields.Count * * * * .Fields(ndxFlds) = Cells(r, ndxCols) * * * * .Update * * * * ndxCols = ndxCols + 1 * * Next End With This, of course, presumes that the field and column order is the same and they correspond. On Jul 15, 1:21*pm, gab1972 wrote: I am sending information from Excel to an Access db. *In this series, I have about 100 columns of information to send. *The info is on a sheet from Range(AN,2) to (EI,2). *I am opening the recordset and updating information. *Instead of writing the same line of code 100 times for each cell, is there an easier way to program the column to go to the next column?? *If that makes sense. *Below is my code so far... as always, everyone on here rocks and thanks for the help in advance. * * 'open permit.lifecycle recordset * * * * Set rs = New ADODB.Recordset * * str = "SELECT * FROM permit_life WHERE permit_num = '" & strPnum & "'" * * rs.Open str, cn, adOpenKeyset, adLockOptimistic ', adCmdTable * * ' all records in a table * * r = 2 ' the start row in the worksheet * * ' repeat until first empty cell in column A * * * * With rs * * * * * * .Fields("PLFrm1") = Range("AN" & r).Value * * * * * * .Fields("PLTo1") = Range("AO & r).Value * * * * * * ' 98 more fields to update up to column EI... * * * * * * .Update ' stores the new record * * * * End With * * 'close permit_info recordset and empty memory * * rs.Close * * Set rs = Nothing- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
...more repetitive copying
wow...thanks!!! That makes complete sense. I'm going to give it a
try. I really appreciate this. You just saved me tons of copying, pasting, referring to what cell is what. AB wrote: As i said, i haven't tested the code but if it works then you can start the ndx at any number less than or equal to the final number, i.e. the only condition for this line For ndxFlds = 1 To .Fields.Count is that the first number ('1' in this instance) needs to be less or equal to field.count, so, you can sart at For ndxFlds = 1 or For ndxFlds = 2 or For ndxFlds = 3 ... Doesn't matter. The same way as i started the Column Index at 40 - it's just a number that you need. On Jul 15, 1:57*pm, gab1972 wrote: This would be perfect...one caveat though...can I start the ndxFlds count to start on the second field? *I have information going from Excel to 4 different Access DB's. *This is just one section of of the ws going to a specific db. *The common denominator for all the db's is the permit_num - which is the primary key in each db. *So, in each db, the first record is the permit number then all the other information. So, I don't want to update that. *So if I use the .Fields.Count...I need for it to start counting on the second record. *Make sense? *Any ideas? *Thanks in advance. AB wrote: I haven't tested but this is a direction i'd try out: Dim ndxFlds As Integer 'Index for looping through fields Dim ndxCols As Integer 'Index for looping through .xls columns ndxCols = 40 'i.e., column AN With rs * * For ndxFlds = 1 To .Fields.Count * * * * .Fields(ndxFlds) = Cells(r, ndxCols) * * * * .Update * * * * ndxCols = ndxCols + 1 * * Next End With This, of course, presumes that the field and column order is the same and they correspond. On Jul 15, 1:21*pm, gab1972 wrote: I am sending information from Excel to an Access db. *In this series, I have about 100 columns of information to send. *The info is on a sheet from Range(AN,2) to (EI,2). *I am opening the recordset and updating information. *Instead of writing the same line of code 100 times for each cell, is there an easier way to program the column to go to the next column?? *If that makes sense. *Below is my code so far... as always, everyone on here rocks and thanks for the help in advance. * * 'open permit.lifecycle recordset * * * * Set rs = New ADODB.Recordset * * str = "SELECT * FROM permit_life WHERE permit_num = '" & strPnum & "'" * * rs.Open str, cn, adOpenKeyset, adLockOptimistic ', adCmdTable * * ' all records in a table * * r = 2 ' the start row in the worksheet * * ' repeat until first empty cell in column A * * * * With rs * * * * * * .Fields("PLFrm1") = Range("AN" & r).Value * * * * * * .Fields("PLTo1") = Range("AO & r).Value * * * * * * ' 98 more fields to update up to column EI.... * * * * * * .Update ' stores the new record * * * * End With * * 'close permit_info recordset and empty memory * * rs.Close * * Set rs = Nothing- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
...more repetitive copying
Thanks for the feedback.
Post back if didn't work - will try figuring out something else. On Jul 15, 2:09*pm, gab1972 wrote: wow...thanks!!! *That makes complete sense. *I'm going to give it a try. *I really appreciate this. *You just saved me tons of copying, pasting, referring to what cell is what. AB wrote: As i said, i haven't tested the code but if it works then you can start the ndx at any number less than or equal to the final number, i.e. the only condition for this line For ndxFlds = 1 To .Fields.Count is that the first number ('1' in this instance) needs to be less or equal to field.count, so, you can sart at For ndxFlds = 1 or For ndxFlds = 2 or For ndxFlds = 3 ... Doesn't matter. The same way as i started the Column Index at 40 - it's just a number that you need. On Jul 15, 1:57*pm, gab1972 wrote: This would be perfect...one caveat though...can I start the ndxFlds count to start on the second field? *I have information going from Excel to 4 different Access DB's. *This is just one section of of the ws going to a specific db. *The common denominator for all the db's is the permit_num - which is the primary key in each db. *So, in each db, the first record is the permit number then all the other information. So, I don't want to update that. *So if I use the .Fields.Count...I need for it to start counting on the second record. *Make sense? *Any ideas? *Thanks in advance. AB wrote: I haven't tested but this is a direction i'd try out: Dim ndxFlds As Integer 'Index for looping through fields Dim ndxCols As Integer 'Index for looping through .xls columns ndxCols = 40 'i.e., column AN With rs * * For ndxFlds = 1 To .Fields.Count * * * * .Fields(ndxFlds) = Cells(r, ndxCols) * * * * .Update * * * * ndxCols = ndxCols + 1 * * Next End With This, of course, presumes that the field and column order is the same and they correspond. On Jul 15, 1:21*pm, gab1972 wrote: I am sending information from Excel to an Access db. *In this series, I have about 100 columns of information to send. *The info is on a sheet from Range(AN,2) to (EI,2). *I am opening the recordset and updating information. *Instead of writing the same line of code 100 times for each cell, is there an easier way to program the column to go to the next column?? *If that makes sense. *Below is my code so far... as always, everyone on here rocks and thanks for the help in advance. * * 'open permit.lifecycle recordset * * * * Set rs = New ADODB.Recordset * * str = "SELECT * FROM permit_life WHERE permit_num = '" & strPnum & "'" * * rs.Open str, cn, adOpenKeyset, adLockOptimistic ', adCmdTable * * ' all records in a table * * r = 2 ' the start row in the worksheet * * ' repeat until first empty cell in column A * * * * With rs * * * * * * .Fields("PLFrm1") = Range("AN" & r).Value * * * * * * .Fields("PLTo1") = Range("AO & r).Value * * * * * * ' 98 more fields to update up to column EI... * * * * * * .Update ' stores the new record * * * * End With * * 'close permit_info recordset and empty memory * * rs.Close * * Set rs = Nothing- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repetitive macros | Excel Discussion (Misc queries) | |||
Worksheet_Change not repetitive | Excel Programming | |||
looking for repetitive information | Excel Programming | |||
Repetitive keystroke | Excel Discussion (Misc queries) | |||
repetitive | Excel Discussion (Misc queries) |