I obtain a data dump from an Access Database into an Excel Spreadsheet. I want to be able to manipulate the data in the spreadsheet, but every time I refresh the query I lose the work that I have done. How can I receive the updated data into the spreadsheet without losing the formulas that I have set up></SPAN></SPAN>
Private Sub Workbook_Open()</SPAN></SPAN>
Call GetRigSchedule</SPAN></SPAN>
End Sub</SPAN></SPAN>
Private Sub GetRigSchedule()</SPAN></SPAN>
Dim sql As String</SPAN></SPAN>
Dim ConnectionString As String</SPAN></SPAN>
Dim dtmStart As Date</SPAN></SPAN>
Dim Result As Range</SPAN></SPAN>
ConnectionString = "provider=SQLOLEDB.1;server=BDONT3APSQL1\BDONT3APSQL11;database=Drilling;Integrated Security=SSPI"</SPAN></SPAN>
dtmStart = 12 / 31 / 13</SPAN></SPAN>
sql = "SELECT r.strRigNumber, wd.strAPINo, wd.strChevNo, wd.strWBSElement, p.strPropertyName, wd.strWellNo, " & _</SPAN></SPAN>
"wd.dtmSpudEstimated, wd.strProgramStatus, wd.dtmFinalSurvey, dc.strDirectionalCode, " & _</SPAN></SPAN>
"wt.strWellType, r.intRigID, wd.dtmSpud, f.strFieldName, p.strSection, " & _</SPAN></SPAN>
"p.strTownship, p.strRange, wd.dtmRelease, wd.sngDrillDays, wd.strLease " & _</SPAN></SPAN>
"FROM tlkpWellType wt " & _</SPAN></SPAN>
"INNER JOIN tblWellData wd ON wt.intWellTypeID = wd.intWellTypeID " & _</SPAN></SPAN>
"RIGHT OUTER JOIN tlkpRig r ON r.intRigID = wd.intRigID " & _</SPAN></SPAN>
"LEFT OUTER JOIN tlkpProperty p ON wd.intPropertyID = p.intPropertyID " & _</SPAN></SPAN>
"INNER JOIN tlkpField f ON p.intFieldID = f.intFieldID " & _</SPAN></SPAN>
"LEFT OUTER JOIN tlkpDirectionalCode dc ON wd.intDirectionalID = dc.intDirectionalID " & _</SPAN></SPAN>
"WHERE ((wd.dtmSpudEstimated >= '" & dtmStart & "') OR " & _</SPAN></SPAN>
"((wd.dtmSpudEstimated IS NULL) AND (wd.dtmSpud >= '" & dtmStart & "'))) " & _</SPAN></SPAN>
"ORDER BY r.strRigNumber, wd.dtmSpudEstimated, wd.dtmSpud;"</SPAN></SPAN>
Set Result = Sheets(1).Range("a1")</SPAN></SPAN>
Range(Result, Result.Offset(65533, 26)).ClearContents</SPAN></SPAN>
Call GetData(ConnectionString, sql, Result)</SPAN></SPAN>
End Sub</SPAN></SPAN>
Private Sub GetData(cs As String, sql As String, dest As Range)</SPAN></SPAN>
Dim i As Long</SPAN></SPAN>
Dim cn As ADODB.Connection</SPAN></SPAN>
Dim rs As ADODB.Recordset</SPAN></SPAN>
Set cn = New ADODB.Connection</SPAN></SPAN>
Set rs = New ADODB.Recordset</SPAN></SPAN>
cn.ConnectionString = cs</SPAN></SPAN>
cn.Open</SPAN></SPAN>
rs.ActiveConnection = cn</SPAN></SPAN>
rs.CursorType = adOpenForwardOnly</SPAN></SPAN>
rs.LockType = adLockReadOnly</SPAN></SPAN>
rs.Source = sql</SPAN></SPAN>
rs.Open</SPAN></SPAN>
For i = 0 To rs.Fields.Count - 1</SPAN></SPAN>
dest.Offset(0, i).Value = rs.Fields(i).Name</SPAN></SPAN>
Next</SPAN></SPAN>
dest.Offset(1, 0).CopyFromRecordset rs</SPAN></SPAN>
rs.Close</SPAN></SPAN>
Set rs = Nothing</SPAN></SPAN>
cn.Close</SPAN></SPAN>
Set cn = Nothing</SPAN></SPAN>
End Sub</SPAN></SPAN>
Private Sub Workbook_Open()</SPAN></SPAN>
Call GetRigSchedule</SPAN></SPAN>
End Sub</SPAN></SPAN>
Private Sub GetRigSchedule()</SPAN></SPAN>
Dim sql As String</SPAN></SPAN>
Dim ConnectionString As String</SPAN></SPAN>
Dim dtmStart As Date</SPAN></SPAN>
Dim Result As Range</SPAN></SPAN>
ConnectionString = "provider=SQLOLEDB.1;server=BDONT3APSQL1\BDONT3APSQL11;database=Drilling;Integrated Security=SSPI"</SPAN></SPAN>
dtmStart = 12 / 31 / 13</SPAN></SPAN>
sql = "SELECT r.strRigNumber, wd.strAPINo, wd.strChevNo, wd.strWBSElement, p.strPropertyName, wd.strWellNo, " & _</SPAN></SPAN>
"wd.dtmSpudEstimated, wd.strProgramStatus, wd.dtmFinalSurvey, dc.strDirectionalCode, " & _</SPAN></SPAN>
"wt.strWellType, r.intRigID, wd.dtmSpud, f.strFieldName, p.strSection, " & _</SPAN></SPAN>
"p.strTownship, p.strRange, wd.dtmRelease, wd.sngDrillDays, wd.strLease " & _</SPAN></SPAN>
"FROM tlkpWellType wt " & _</SPAN></SPAN>
"INNER JOIN tblWellData wd ON wt.intWellTypeID = wd.intWellTypeID " & _</SPAN></SPAN>
"RIGHT OUTER JOIN tlkpRig r ON r.intRigID = wd.intRigID " & _</SPAN></SPAN>
"LEFT OUTER JOIN tlkpProperty p ON wd.intPropertyID = p.intPropertyID " & _</SPAN></SPAN>
"INNER JOIN tlkpField f ON p.intFieldID = f.intFieldID " & _</SPAN></SPAN>
"LEFT OUTER JOIN tlkpDirectionalCode dc ON wd.intDirectionalID = dc.intDirectionalID " & _</SPAN></SPAN>
"WHERE ((wd.dtmSpudEstimated >= '" & dtmStart & "') OR " & _</SPAN></SPAN>
"((wd.dtmSpudEstimated IS NULL) AND (wd.dtmSpud >= '" & dtmStart & "'))) " & _</SPAN></SPAN>
"ORDER BY r.strRigNumber, wd.dtmSpudEstimated, wd.dtmSpud;"</SPAN></SPAN>
Set Result = Sheets(1).Range("a1")</SPAN></SPAN>
Range(Result, Result.Offset(65533, 26)).ClearContents</SPAN></SPAN>
Call GetData(ConnectionString, sql, Result)</SPAN></SPAN>
End Sub</SPAN></SPAN>
Private Sub GetData(cs As String, sql As String, dest As Range)</SPAN></SPAN>
Dim i As Long</SPAN></SPAN>
Dim cn As ADODB.Connection</SPAN></SPAN>
Dim rs As ADODB.Recordset</SPAN></SPAN>
Set cn = New ADODB.Connection</SPAN></SPAN>
Set rs = New ADODB.Recordset</SPAN></SPAN>
cn.ConnectionString = cs</SPAN></SPAN>
cn.Open</SPAN></SPAN>
rs.ActiveConnection = cn</SPAN></SPAN>
rs.CursorType = adOpenForwardOnly</SPAN></SPAN>
rs.LockType = adLockReadOnly</SPAN></SPAN>
rs.Source = sql</SPAN></SPAN>
rs.Open</SPAN></SPAN>
For i = 0 To rs.Fields.Count - 1</SPAN></SPAN>
dest.Offset(0, i).Value = rs.Fields(i).Name</SPAN></SPAN>
Next</SPAN></SPAN>
dest.Offset(1, 0).CopyFromRecordset rs</SPAN></SPAN>
rs.Close</SPAN></SPAN>
Set rs = Nothing</SPAN></SPAN>
cn.Close</SPAN></SPAN>
Set cn = Nothing</SPAN></SPAN>
End Sub</SPAN></SPAN>