Hey, Use the Attached one... I can see the code written here a little bit slow... it can be made faster if I could get your database structure...
Thanks,
Anoop
Sr. Developer
On 8/9/13, Lalit Mohan Pandey <mohan.pande...@gmail.com> wrote:
> Replace line
>
> *Erow = Me.Range(A1).End(xlDown).row + 1*
>
> with this one
>
> *If LenB(Trim(Me.Cells(13, 2).Value)) = 0 Then Me.Cells(13, 2).Value =
> "DATES"*
> *Erow = Me.Cells(Me.Rows.Count, 2).End(xlUp).Offset(1).row*
>
> If it will not work then you need to share your database as well so that we
>
> can run this utility.
>
> On Thursday, 8 August 2013 21:59:19 UTC+5:30, christy palmer wrote:
>>
>> the same thing happens. the data is over written. using your code i do not
>>
>> get an overflow error :-) but i still can't get the data to add rather
>> than
>> replace :-(
>>
>> On Wed, Aug 7, 2013 at 6:13 PM, Anoop K Sharma
>> <aks.sh...@gmail.com<javascript:>>
>> wrote:
>> >
>> > Replace your code with below one... This will be great if you could
>> provide your original file.
>> >
>> >
>> > Dim state As String
>> >
>> > Dim startdate As String
>> >
>> > Dim enddate As String, Erow as long
>> >
>> >
>> >
>> >
>> >
>> > Sub btn1_click()
>> >
>> > state = Me.Cells(3, 3)
>> >
>> > startdate = Me.Cells(4, 3)
>> >
>> > enddate = Me.Cells(5, 3)
>> >
>> > MsgBox " Input parameters - State = " & state & " Date =" & startdate
>> >
>> & " to " & enddate
>> >
>> > Erow=Me.range("A1").End(xlDown).row+1
>> >
>> > MsgBox " Depending on the volume of data, this report would take 5-10
>> >
>> minutes to fetch results", vbInformation
>> >
>> > callDB
>> >
>> > End Sub
>> >
>> >
>> >
>> > Sub callDB()
>> >
>> > On Error GoTo ErrHandler
>> >
>> >
>> >
>> > Dim con As ADODB.Connection
>> >
>> > Dim rs As ADODB.Recordset
>> >
>> > Dim query As String
>> >
>> > Dim strqry As String
>> >
>> > Dim txt As String
>> >
>> > Dim row As Integer
>> >
>> > Dim col As Integer
>> >
>> >
>> >
>> > Set con = CreateObject("ADODB.Connection")
>> >
>> > Set rs = CreateObject("ADODB.Recordset")
>> >
>> >
>> >
>> > strCon = "Provider=OraOLEDB.Oracle;Data Source=enCPR2;User
>> Id=***;Password=***!!"
>> >
>> >
>> >
>> > con.ConnectionString = strCon
>> >
>> > con.Open
>> >
>> >
>> >
>> >
>> >
>> > strqry = strqry & " SELECT "
>> >
>> > strqry = strqry & " DATES, "
>> >
>> >
>> >
>> > rs.Open strqry, con
>> >
>> >
>> >
>> > If Not rs.EOF Then
>> >
>> > rs.MoveFirst
>> >
>> > End If
>> >
>> > row = 14
>> >
>> > col = 2
>> >
>> > Do While Not rs.EOF
>> >
>> >
>> >
>> > For I = 0 To rs.Fields.Count - 1
>> >
>> > Me.Cells(Erow, col) = rs.Fields(I)
>> >
>> > col = col + 1
>> >
>> > Next I
>> >
>> > Erow = Erow + 1
>> >
>> > col = 2
>> >
>> > rs.MoveNext
>> >
>> > Loop
>> >
>> >
>> >
>> > ExitHandler:
>> >
>> > rs.Close
>> >
>> > con.Close
>> >
>> > Set rs = Nothing
>> >
>> > Set con = Nothing
>> >
>> > MsgBox "Report generation is completed"
>> >
>> > Exit Sub
>> >
>> >
>> > On Thu, Aug 8, 2013 at 12:54 AM, christy palmer
>> > <chris...@gmail.com<javascript:>>
>> wrote:
>> >>
>> >> I have a macro that works perfectly except for everytime it's ran it
>> wipes the spreadsheet clean and inserts the new values. I need it to
>> insert
>> the new data while leaving the old data without giving me a stackover flow
>>
>> error :-/ Any help would be greatly appreciated.
>> >>
>> >>
>> >> Dim state As String
>> >>
>> >> Dim startdate As String
>> >>
>> >> Dim enddate As String
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> Sub btn1_click()
>> >>
>> >> state = Me.Cells(3, 3)
>> >>
>> >> startdate = Me.Cells(4, 3)
>> >>
>> >> enddate = Me.Cells(5, 3)
>> >>
>> >> MsgBox " Input parameters - State = " & state & " Date =" &
>> startdate & " to " & enddate
>> >>
>> >> For row = 14 To 100
>> >>
>> >> For col = 2 To 100
>> >>
>> >> Me.Cells(row, col) = ""
>> >>
>> >> Next col
>> >>
>> >> Next row
>> >>
>> >> MsgBox " Depending on the volume of data, this report would take
>> 5-10 minutes to fetch results", vbInformation
>> >>
>> >> callDB
>> >>
>> >> End Sub
>> >>
>> >>
>> >>
>> >> Sub callDB()
>> >>
>> >> On Error GoTo ErrHandler
>> >>
>> >>
>> >>
>> >> Dim con As ADODB.Connection
>> >>
>> >> Dim rs As ADODB.Recordset
>> >>
>> >> Dim query As String
>> >>
>> >> Dim strqry As String
>> >>
>> >> Dim txt As String
>> >>
>> >> Dim row As Integer
>> >>
>> >> Dim col As Integer
>> >>
>> >>
>> >>
>> >> Set con = CreateObject("ADODB.Connection")
>> >>
>> >> Set rs = CreateObject("ADODB.Recordset")
>> >>
>> >>
>> >>
>> >> strCon = "Provider=OraOLEDB.Oracle;Data Source=enCPR2;User
>> Id=***;Password=***!!"
>> >>
>> >>
>> >>
>> >> con.ConnectionString = strCon
>> >>
>> >> con.Open
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> strqry = strqry & " SELECT "
>> >>
>> >> strqry = strqry & " DATES, "
>> >>
>> >>
>> >>
>> >> rs.Open strqry, con
>> >>
>> >>
>> >>
>> >> If Not rs.EOF Then
>> >>
>> >> rs.MoveFirst
>> >>
>> >> End If
>> >>
>> >> row = 14
>> >>
>> >> col = 2
>> >>
>> >> Do While Not rs.EOF
>> >>
>> >>
>> >>
>> >> For I = 0 To rs.Fields.Count - 1
>> >>
>> >> Me.Cells(row, col) = rs.Fields(I)
>> >>
>> >> col = col + 1
>> >>
>> >> Next I
>> >>
>> >> row = row + 1
>> >>
>> >> col = 2
>> >>
>> >> rs.MoveNext
>> >>
>> >> Loop
>> >>
>> >>
>> >>
>> >> ExitHandler:
>> >>
>> >> rs.Close
>> >>
>> >> con.Close
>> >>
>> >> Set rs = Nothing
>> >>
>> >> Set con = Nothing
>> >>
>> >> MsgBox "Report generation is completed"
>> >>
>> >> Exit Sub
>> >>
>> >>
>> >>
>> >>
>> >> --
>> >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>> >>
>> >> FORUM RULES
>> >>
>> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> >>
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> >> 2) Don't post a question in the thread of another member.
>> >> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> >> 4) Acknowledge the responses you receive, good or bad.
>> >> 5) Jobs posting is not allowed.
>> >> 6) Sharing copyrighted material and their links is not allowed.
>> >>
>> >> NOTE : Don't ever post confidential data in a workbook. Forum owners
>> and members are not responsible for any loss.
>> >> ---
>> >> You received this message because you are subscribed to the Google
>> Groups "MS EXCEL AND VBA MACROS" group.
>> >> To unsubscribe from this group and stop receiving emails from it, send
>> >>
>> an email to excel-macros...@googlegroups.com <javascript:>.
>> >> To post to this group, send email to
>> >> excel-...@googlegroups.com<javascript:>
>> .
>> >> Visit this group at http://groups.google.com/group/excel-macros.
>> >> For more options, visit https://groups.google.com/groups/opt_out.
>> >>
>> >>
>> >
>> >
>> >
>> >
>> > --
>> > Regards,
>> > Anoop
>> > Sr. Developer
>> > Facebook ID - https://www.facebook.com/anooop.k.sharma
>> >
>> > --
>> > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>> >
>> > FORUM RULES
>> >
>> > 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> >
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> > 2) Don't post a question in the thread of another member.
>> > 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> > 4) Acknowledge the responses you receive, good or bad.
>> > 5) Jobs posting is not allowed.
>> > 6) Sharing copyrighted material and their links is not allowed.
>> >
>> > NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> >
>> members are not responsible for any loss.
>> > ---
>> > You received this message because you are subscribed to a topic in the
>> Google Groups "MS EXCEL AND VBA MACROS" group.
>> > To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/excel-macros/FShBh9sM3vY/unsubscribe.
>> > To unsubscribe from this group and all its topics, send an email to
>> excel-macros...@googlegroups.com <javascript:>.
>> > To post to this group, send email to
>> > excel-...@googlegroups.com<javascript:>
>> .
>> > Visit this group at http://groups.google.com/group/excel-macros.
>> > For more options, visit https://groups.google.com/groups/opt_out.
>> >
>> >
>>
>>
>>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
Regards,
Anoop
Sr. Developer
Facebook ID - https://www.facebook.com/anooop.k.sharma
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
Copy of excel vba.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12
