Daunting Project to Create mapping tool

  • Slightly daunted by the following task ...but hopefully you may be able steer me in the right direction...

    I have been asked to see if it's possible to create an export tool to convert a spreadsheet into User definable 'flat file' .

    The premise being-

    WkBk1 has the 'tool' and the 'data' is in a customer's Workbooks which contain basically the same info, just with different headers / layout ...I'll call this Wkbk2.

    What I envisage –

    In WkBk1 the User clicks on a button to open a user form

    The Userform will have a text box for user to put path and file name of where WkBk2 is ....and preferably a BROWSE button to open a dialog..

    A textbox to indicate which sheet info is in

    There should then be a tick box to say if "First Line contains headers"

    Then a radio button to choose Use previous template OR
    to choose new USER defined


    On User defined,

    Click on radio button to determine if Fixed Width or Delimited …on Delimited a textbox becomes visible for user to input delimiter ….like a comma, or semi-colon etc. ( might need to indicate a defined range with a case structure to validate)

    A combobox (?) is populated with all WkBk2's headers or Column letters if tickbox for "First line contains headers" is not ticked.

    Another combobox (?) has our pre-defined list to match against.

    If User has already chosen Fixed width, there are then three text boxes call START LENGTH and END

    The idea is User chooses one from User, one from Ours, put the start position, length of field and end position. (Positions will have defaults)

    On completion of these five boxes, they would click an “Add to template” button. Then they pick the next field…etc….It would be good to see these build below / what hasn’t been mapped yet (mandatory fields)

    Once complete they click on Create Template and they can name their Template which becomes a the name of a new sheet in WkBk2 (details are recorded in sheet …some how)

    This adds also to the Use previous Template at the beginning.

    User asked if they want to run now, If no cancel true, if yes then they choose what they want the file to be called and where (back to a text box for name and path OR Browse option.)

    Then records are mapped.

    File name will be something like C:\Customer1.txt

    As I said daunting …but any thoughts to push this in the right direction appreciated.

    There is an attached picture of a suggested layout that will hopefully help you picture the above (and if you’ve read this then thanks for your time and patience!!!)

  • 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. :)

  • Many :thanx: for the feedback

    Jong - Thanks for the code ...quick question, "ImportRangeFromDelimitedText" is there a seperate Sub, funtion or property for this?

    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.

    [SIZE=6]S[/SIZE][SIZE=6]t[/SIZE][SIZE=6]u[/SIZE] :drum:

  • 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)
    SC = Left(SepChar, 1)
    End If

    ' perform import
    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
    Close #fn
    Application.Calculation = xlCalculationAutomatic

    ' 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 = ""
    tString = tString & tChar
    End If
    Next i
    sCount = sCount + 1
    ReDim Preserve ResultArray(1 To sCount)
    ResultArray(sCount) = tString
    ParseDelimitedString = ResultArray
    End Function

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!