Daunting Project to Create mapping tool

File Open Dialog

Iv'e done a similar application, although mine was more complicated. I imported a TXT file from a machine that extracts data from samples taken from nature. The TXT files that were imported are about 500K in size. To open these files, I did the following (Open file dialog):
-----------------------------------------------------
Sub OpenFile()
Dim s As String

s = Application.GetOpenFilename("Machine Files (*.txt),*.txt", _
1, "Opens the machine file", , False)
ImportRangeFromDelimitedText s, _
",", ThisWorkbook.Name, "Extracted", "A1"
End Sub

'Note: Extracted is the worksheet where the data is pasted
----------------------------------------------------

The above code will allow you to incorporate the "Browse" dialog you are trying to do. And then the text can be parsed into a worksheet of your choice.

Hope this helps out.

Hi Stu,

Large projects can appear daunting, even to experienced coders. The fact that you are taking the time to set out your thoughts and get some feedback will serve you well when you actually begin the project.

My advice would be to split the project into small modules that you can work on and then use a main 'calling' routine to bring it all together. You will find the project much easier to debug and maintain if you adopt a modular approach (and less daunting! ). You will also find it easier to attract help from other people in respect of specific queries rather than a blanket 'here's my daunting project ... please help me build it' query.

Sub

Yes, I'm sorry I overlooked that. Here is the subfunction, in fact its the most complicated part of it.. The code below is pasted right out of my project, so you might have to modify it a bit. It will basically take the text out of the txt file, and parse it to the specified Workbook/Worksheet.

To be quite honest, I suggest you write your own routine, because this will actually get the data, and format it the way I wanted it. There is also another sub routine inside this one called "ParseDelimitedStrings" which does just that... I also included it below. I hope it helps out anyways.
-------------------------------

Sub ImportRangeFromDelimitedText(SourceFile As String, SepChar As String, _
TargetWB As String, TargetWS As String, TargetAddress As String)

Dim SC As String * 1, TargetCell As Range, TargetValues As Variant
Dim r As Long, fLen As Long
Dim fn As Integer, LineString As String
' validate the input data if necessary
If Dir(SourceFile) = "" Then Exit Sub ' SourceFile doesn't exist
If UCase(SepChar) = "TAB" Or UCase(SepChar) = "T" Then
SC = Chr(9)
Else
SC = Left(SepChar, 1)
End If

' perform import
Workbooks(TargetWB).Activate
Worksheets(TargetWS).Activate
Set TargetCell = Range(TargetAddress).Cells(1, 1)
On Error GoTo NotAbleToImport
fn = FreeFile
Open SourceFile For Input As #fn
On Error GoTo 0
fLen = LOF(fn)
r = 0
While Not EOF(fn)
Line Input #fn, LineString
If r Mod 100 = 0 Then
Application.StatusBar = "Reading data from " & _
SourceFile & " " & _
Format(Seek(fn) / fLen, "0 %") & "..."
End If
TargetValues = ParseDelimitedString(LineString, SepChar)
UpdateCells TargetCell.Offset(r, 0), TargetValues
r = r + 1
Wend
Close #fn
Application.Calculation = xlCalculationAutomatic
NotAbleToImport:

' clean up
Set TargetCell = Nothing 'This is project specific
Application.StatusBar = False 'This is project specific
End Sub

--------------------------------------

Function ParseDelimitedString(InputString As String, SC As String) As Variant

Dim i As Integer, tString As String, tChar As String * 1
Dim sCount As Integer, ResultArray() As Variant
tString = ""
sCount = 0
For i = 1 To Len(InputString)
tChar = Mid$(InputString, i, 1)
If tChar = SC Then
sCount = sCount + 1
ReDim Preserve ResultArray(1 To sCount)
ResultArray(sCount) = tString
tString = ""
Else
tString = tString & tChar
End If
Next i
sCount = sCount + 1
ReDim Preserve ResultArray(1 To sCount)
ResultArray(sCount) = tString
ParseDelimitedString = ResultArray
End Function

Quote from Squire_King

Richie - I agree...I thought this was a bit over the top ...but had already sent it by the time I was having second thoughts ....will know for the future...thanks.

Just a quick question for youself - alot of the posts (particularly older ones by Ivan) seem to be full of raw HTML and have &amp etc....do I need to request my posting rules to be changed to see then correctly.?

Thanks again for the support

Have a great weekend.

Display More

Hi Stu,

I didn't mean that you shouldn't have posted the query as you did - you were only asking for feedback on how to approach it and that is quite acceptable, in my book anyway. I just meant that as you start writing the code it will be easier to ask any questions that arise if you can just give people specific queries rather than having to give details of the whole project.

The HTML issue (eg &amp) is a side-effect of the recent change of forum provider. Basically all the 'old' code needs tidying-up but, as you can imagine, its quite a big task! If there are any in particular that you can't decipher then just post the link - somebody will help you out (or if Ivan's about he may have the code saved somewhere).

Good luck with the project - give us a shout if you need any help or further feedback.