Posts by Jong

    helping out

    This task is relatively easy to do. First you will need to create some kind of macro to perform the task for you.
    You can begin by clicking "Tools > Macros > Macros"

    Here, specify the name of the macro you want to create, then click "create"

    You will be presented with the VB interface. In the text file present, add the following code:

    The problem with the above code is that it is not yet optimized for your usage. It checks every possible cell in the worksheet, which will take a few seconds to do. What i would suggest is you add some kind of boudary to make the macro more efficient. For example:

    Above, the code will exit on row 100, making it a lot more efficient. You can change this number to whatever row you think you would go up to.

    The easiest way to call this code would now be to click "Tools > Macros > Macros" then select the macro from the list, and click RUN


    I don't.

    But it would be extremely easy to do. Depending on your interface, the inner workings of the application would be quite simple if you run it over excel.

    All you would need is some kind of way to have an input. Once the input is received, send the data to the worksheet and save the file.

    I suggest having some kind of userform to receive the input. Validate the userform to ensure data is correct, then send it off to the spreadsheet. You could also create a userform to view the data, but that would be redundant. If you format the worksheet nicely, all the data can be viewed directly.

    If you have any specific questions regarding the project, just ask.


    Ok, I'm not sure if i completely understand your question, but the following chunk of code will eliminate any blank cells by moving data to the first available row.


    Looks like to me that your not specifying the worksheet for the range.

    for example:

    With ActiveCell
    .FormulaR1C1 = "=PROPER(TRIM(RC[-1]))"
    End With
    Range("B2").AutoFill Destination:=Worksheet("MySheet").Range("B2:B" & Range("B65536").End(xlUp).Row)


    I suggest you create a UserForm with some kind of selection method for your scenarios. If your scenarios are dynamic, you should use a ListBox and populate it dynamically.
    If you have a fixed set of scenarios, and a large amount, use a DropDownList.
    If you have a fixed set of scenarios, and a small amount, use CheckBox or RadioButton selection methods.

    If you need help with the code part of this, let us know.
    The Form can be created quite easily by using drag&Drop interface in VB.


    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

    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.


    What you can do is when you do invoke your macro, the selected cells can be access doing the following:

    Worksheets("MySheet").Activate 'Activate the sheet with the selection

    With cells, you can do whatever you need to do in VBA to the cells.
    You can also save the Range into a Range variable:

    Dim myRange as Range

    myRange = ActiveSheet.Selection