Hello, I've been building a macro laden xls sheet for sometime now that converts data output from a program that outputs Survey data in txt or csv format so that it may be displayed in a technical dataset then exported into a scientific graphing application. Although it has lots of bells and whistles in it to simplify my design process I have an issue that I cannot seem to solve or find resolution to in any forums.

I invite your help please.

The synopsis is that we are drilling oil wells with wellbore surveys that must be compared against designed profiles that are plotted on a variety of display planes. My challenge is that the survey data which arrives daily is not compatible to simply import into our design program; therefore I must manually enter the data each morning so that it can spit the surveys back out in a format that I can work with. Obviously this is time consuming and archaic but easy to overcome with the right logical steps that would allow me to import, parse, copy, paste into my xls converter workbook and i'm pretty close but need some help.

I require a macro line that can do the following:

1. My dataset is 10 columns wide with up to 500 rows that are keyed off the first column value which is the cumulative depth of the well at each survey.
2. Each morning I manually update and append the data set with the NEW ROWS ONLY that have been drilled since the previous day.
3. This means that the macro must be able to evaluate the existing survey data to identify the previous day's deepest survey so the it only copies the new survey data and appends it to the end of the list.
a. Identify the maximum value of the first column in the existing survey set (drill depth).
b. identify the corresponding value in the update survey set with the same key value (Drill depth)
c. copy the update rows that exist beyond the previous Max value of the existing surveys
d. append the update data in the rows below the previous existing survey data set.
4. The rest of the minor details beyond the scope of this challenge I can handle.

Seems simple but I'm lacking the skills to do so.

Warm regards,

David (aka. aquayreous)


PS> I don't know if this helps but my existing code is as follows:

Sub Update_Final_Surveys()

'open winserve ascii text files into excel template
'macro written 11/21/2012 by David Kunz

'This calls the sub routine to clear data before beginning the update
delete_existing_input_data

'adds new sheet and renames to that of the originating text file.

Dim Text_path As Variant
Text_path = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Open Winserve ASCII Text File")

'Sets the variable name Text_path to browse,select and stores the name of the
'text file to be imported

Dim current_book As String
current_book = ActiveWorkbook.Name

If Text_path <> False Then

Workbooks.OpenText Filename:=Text_path, origin:=xlWindows _
, startrow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
ActiveSheet.Range("A:A").Select
Selection.Copy

Dim Text_name As String
Text_name = ActiveWorkbook.Name

Workbooks(current_book).Activate
Sheets("Input Data").Activate
ActiveSheet.Columns("A:A").Select
ActiveSheet.Paste
Workbooks(Text_name).Activate
Application.CutCopyMode = False
ActiveWindow.Close
Windows(current_book).Activate

'The following sub routines will be called in sequence

Parse_Header_info
new_sheet_parse
delete_blank_rows
Copy_Update_surveys

'Finally the Welldata sheet is activated

Sheets("WellData").Activate
ActiveSheet.Range("A4").Select


Else
MsgBox "No Files Were Selected"
End If

End Sub

----------------------------------------------------------------------------------------------------------------------
This is the "Copy_Update_surveys()" sub routine where I copy the parsed and prepared data into the appropriate data set and would require the append insertion:
------------------------------------------------------
Sub Copy_Update_surveys()
'
' Copy_surveys Macro
' this macro copies the parsed and compacted data to the "welldata" tab
' also copies the company info from the parsed headers into the "company info" tab
'macro written 11/21/2012 by David Kunz
'
Dim current As String
current = ActiveSheet.Name


'at the moment this copies and pastes the entire dataset to overwrite the previous days' numbers. However, sometimes I interpolate and insert lines that will be lost if I don't first locate the update surveys and simply append the new rows at the bottom

Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Sheets("Update").Select
Range("h5").Select
ActiveSheet.Paste


'this copies all the header and well information as additional information

Sheets(current).Select
Range("b1:B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Update").Select
Range("Ac20").Select
ActiveSheet.Paste
Sheets(current).Select
Range("h1:I7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Update").Select
Range("AC30").Select
ActiveSheet.Paste
'this is the end of copy surveys to welldata and header info to company info sheets

Sheets("Origin").Select
Range("b2:g310").Select
Selection.Copy
Sheets(current).Select
Range("n2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'this last part copies the 'as-drilled' origin output data to the "current" sheet if
'multiple text files are inported into the file this ensures that the minimum plotting
'data is recorded for each file (i.e. for plotting multiple wells on a single pad)

End Sub