How to add a column to a query that does not change when the query is refreshed?

[Steve4]

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>

 

[HappyMeal]

Hi Steve,

You should set up an ODBC connection which requires no VBA as it is already set up in excel to work. I did this on one of my previous jobs and it worked a charm.

Is it imported as a table?

Setting up the formulas without inserting columns inbetween the data dump should work fine (over the right out of the way)

Hope this helps,

HappyMeal

 

[Steve4]

Thanks for the feedback, I will take a look at this. The data does not come over in a table. Thanks again