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+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.
