VBScript and Excel: Couplea Questions 1

[Quintios]

I have a text processing VBS file that takes a text file and turns it into a comma delimited file which I then copy into Excel. Because I want to distribute this in order to make it as simple as possible it would make sense to put the whole thing into the spreadsheet. So my questions:

1. Does VBA support Regular Expressions like VBS?
2. Is there any sort of VBS to VBA translator out there?
3. If I can't get Regular Expressions to work in Excel, how do I call the VBS script from a sub procedure in Excel?

Thanks in advance for your help!

Onwards,

Q-

 

[Bowers74]

Hola Quintios,

Something like this should get you started:

Sub ImportTXTToActiveCell()
    Dim ImpRng As Range
    Dim Filename As String
    Dim r As Long, c As Integer
    Dim txt As String, Char As String * 1
    Dim Data
    Dim i As Integer
    Set ImpRng = ActiveCell
    On Error Resume Next
    Filename = "c:\textfile.txt"
    Open Filename For Input As #1
    If Err <> 0 Then
        MsgBox &quot;Not found: &quot; & Filename, vbCritical, &quot;ERROR&quot;
        Exit Sub
    End If
    r = 0
    c = 0
    txt = &quot;&quot;
    Application.ScreenUpdating = False
    Do Until EOF(1)
        Line Input #1, Data
        For i = 1 To Len(Data)
            Char = Mid(Data, i, 1)
            If Char = &quot;,&quot; Then 'comma
                ActiveCell.Offset(r, c) = txt
                c = c + 1
                txt = &quot;&quot;
            ElseIf i = Len(Data) Then 'end of line
                If Char <> Chr(34) Then txt = txt & Char
                ActiveCell.Offset(r, c) = txt
                txt = &quot;&quot;
            ElseIf Char <> Chr(34) Then
                txt = txt & Char
            End If
        Next i
        c = 0
        r = r + 1
    Loop
    Close #1
    Application.ScreenUpdating = True
End Sub

Simple enough for ya? ;-)


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Quintios]

I appreciate the reply and all, but I already have the function written in VBS, and I cannot directly import the original text file into Excel. It must be converted first using regular expressions and lots of replaces (I also add a couple columns of information) as the original text file is quite raw.

From what I can tell, your script simply copies an existing comma-delimited file into Excel. I'd still have to run the external script to create that comma-delimited file because the original file is in a text format with no commas. I'm trying to avoid the external script.

Onwards,

Q-