Setting reference WRQ Reflection for IBM Version 13.0.1 and Excel VBA

ghh3rd · Oct 17, 2007

I use Excel VBA without any issues, but when I set a reference to the "WRQ Reflection for IBM Version 13.0.1" library, I recieve a "Compile error - wrong number of arguments or invalid property assignment" - an example is that it happens on the word "Left" in my exting VB code line:

If Left(MyLine, 15) = " PROGRAM NAME" Then

Does anyone know why my VB code bombs when I set this reference?

Thanks,

Randy

IknowMe · Oct 17, 2007

How is the variable "MyLine" being set?

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

ghh3rd · Oct 22, 2007

Thanks for your reply Mr. Wilson. Sorry for the delay in getting back to you -- my 10 yr old son broke his leg roller skating on Wed.

Code:

Function Interface_Parer(network)
    ' Requires a reference to Microsoft Scripting Runtime (Tools > References)
    On Error GoTo Interface_Parer_error
    Dim HeaderRow As String
    Dim FX0 As FileSystemObject
    Dim MyFile As Object
    Dim MyLine As String
    Set FXO = CreateObject("Scripting.FileSystemObject")
    Dim FSO As FileSystemObject
    Dim FSOFile As File
    Dim FSOStream As TextStream
    Dim PriorProgCode As String, ProgCode As String
    PriorProgCode = ""
    ProgCode = ""
    Set FSO = New FileSystemObject
    network = UCase(network)

IknowMe · Oct 26, 2007

Where are you assigning a value to the variable MyLine? Sorry for the delay I've been out of town.

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

ghh3rd · Oct 26, 2007

Here's the entire program. The "MyLine=" is right after the "Select Case"/"End Select" at the top. Thanks for taking a look.

Code:

    Function Interface_Parer(network)
    ' Requires a reference to Microsoft Scripting Runtime (Tools > References)
    On Error GoTo Interface_Parer_error
    Dim HeaderRow As String
    Dim FX0 As FileSystemObject
    Dim MyFile As Object
    Dim MyLine As String
    Set FXO = CreateObject("Scripting.FileSystemObject")
    Dim FSO As FileSystemObject
    Dim FSOFile As File
    Dim FSOStream As TextStream
    Dim PriorProgCode As String, ProgCode As String
    PriorProgCode = ""
    ProgCode = ""
    
  
    Set FSO = New FileSystemObject
        
    
    network = UCase(network)
            
    If FileExists("C:\Documents and Settings\houldsgh\My Documents\DLYDINF" & network & "_PARED.TXT") Then
        Kill ("C:\Documents and Settings\houldsgh\My Documents\DLYDINF" & network & "_PARED.TXT")
    End If
    
    Select Case network
        Case "A"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFA_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFA.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFA - ABC")
            MyFile.WriteLine (" ")
        Case "C"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFC_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFC.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFC - CBS")
            MyFile.WriteLine (" ")
        Case "E"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFE_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFE.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFE - MT3")
            MyFile.WriteLine (" ")
        Case "F"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFF_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFF.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFF - FOX")
            MyFile.WriteLine (" ")
        Case "M"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFM_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFM.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFM - MNT")
            MyFile.WriteLine (" ")
        Case "N"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFN_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFN.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFN - NBC")
            MyFile.WriteLine (" ")
        Case "S"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFS_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFS.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFS - SYN")
            'MyFile.WriteLine (" ")
        Case "T"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFT_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFT.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFT - TEL")
            MyFile.WriteLine (" ")
        Case "U"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFU_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFU.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFU - UNI")
            MyFile.WriteLine (" ")
        Case "V"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFV_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFV.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFV - TF")
            MyFile.WriteLine (" ")
        Case "X"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFX_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFX.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFX - PAX")
            MyFile.WriteLine (" ")
        Case "Y"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFY_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFY.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFY - CW")
            MyFile.WriteLine (" ")
        Case "Z"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFZ_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFZ.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFZ - AZA")
            MyFile.WriteLine (" ")
    End Select
        
    
    Set FSOStream = FSOFile.OpenAsTextStream(ForReading, TristateUseDefault)
    HeaderRow = "  CALL   CODE    SHOW DATE        START TIME    DUR   END TIME     MOP   NUM   NUM   AIR  BASE CODE  SYS  SOURCE  FLAG   REPORT DATE"

    'SET UP EXCEL SHEET TO SELECT ONE TO PULL, AND
    'CASE SELECT TO CHANGE CALL LETTERS BASED ON SELECTION.
    
    Do While Not FSOStream.AtEndOfStream
        MyLine = FSOStream.ReadLine
        MyLine = Mid(MyLine, 2, 999)
        If Mid(MyLine, 22, 7) = "A1NPARM" Then
            If Mid(MyLine, 54, 8) = "RETAINED" Then
                MyFile.WriteLine (MyLine)
            End If
        End If
       If Mid(MyLine, 22, 7) = "A1NDINF" Then
           If Mid(MyLine, 54, 8) = "RETAINED" Then
                MyFile.WriteLine (MyLine)
           End If
       End If
       If Mid(MyLine, 22, 7) = "TA1N01W" Then
          If Mid(MyLine, 54, 8) = "RETAINED" Then
             MyFile.WriteLine (MyLine)
          End If
       End If
        
       If Left(MyLine, 15) = "   PROGRAM NAME" Then
            ProgCode = Mid(MyLine, 63, 7)
            If Not ProgCode = PriorProgCode Then
                MyFile.WriteLine " "
                MyFile.WriteLine (MyLine)
                Select Case network
                    Case "A"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WABC   WPVI   KABC(p)   WFAA(c)  WLS(c)   xxxKGO(p)~xxx")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "C"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WCBS   KYW    WBBM(c)   KCBS(p)    KTVT(c)   KTVT(c)")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "E"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  [KBEH(p)/KBLM(p)]   [KGKK(p)/KMMC(p)]   WANX(e)    KMOH(m)")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "F"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WNYW   WTXF   WFLD(c)   KTTV(p)   KDFW(c)")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "M"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WWOR   WPHL   WPWR(c)   KCOP(p)   KDFI(c)")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "N"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WNBC(e)   WCAU(e)    WMAQ(c)   KNBC(p)   KXAS(c)              ")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "T"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WNJU(e)   WWSI(e)    WSNS(c)   KVEA(p)   KXTX(c)  ")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "U"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WXTV    WUVP    WGBO(c)   [KMEX](p)   <<<[KDWU](p)missing lately OK>>>   KUVN(c)")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "V"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WFUT     WFPA    WXFT(c)    KFTR(p)   KSTR(c)")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "X"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WPXN   WPPX    WCPX(c)   KPXN(p)     KPXD(c)   ")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "Y"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WPIX    WPSG    WGN(c)    KTLA(p)   KDAF(c)")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                    Case "Z"
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
                        MyFile.WriteLine ("  WNYN   WZPA   WOCK(c)   KAZA(p)   KODF(C)")
                        MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
               End Select
               MyFile.WriteLine (HeaderRow)
               PriorProgCode = ProgCode
            End If
        End If
        
        Select Case network
            Case "A"
                If Left(MyLine, 4) = "WABC" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WPVI" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WLS " Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KABC" Then
                    MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WFAA" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WCVB" Then
                    MyFile.WriteLine ("Special")
                    MyFile.WriteLine (" " & " " & MyLine)
                    MyFile.WriteLine (" ")
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "C"
                If Left(MyLine, 4) = "WCBS" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KYW " Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WBBM" Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KCBS" Then
                    MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KTVT" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "E"
                If Left(MyLine, 4) = "KBEH" Then
                    MyFile.WriteLine ("*P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KBLM" Then
                    MyFile.WriteLine ("*P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KGKK" Then
                    MyFile.WriteLine ("~P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KMMC" Then
                    MyFile.WriteLine ("~P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WANX" Then
                    MyFile.WriteLine (" E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KMOH" Then
                    MyFile.WriteLine (" M" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WMBQ" Then
                    MyFile.WriteLine ("Special")
                    MyFile.WriteLine (" " & " " & MyLine)
                    MyFile.WriteLine (" ")
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "X"
                If Left(MyLine, 4) = "WPXN" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WPPX" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WCPX" Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KPXN" Then
                 MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KPXD" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "M"
                If Left(MyLine, 4) = "WWOR" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WPHL" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WPWR" Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KCOP" Then
                    MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KDFI" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "N"
                If Left(MyLine, 4) = "KXAS" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WNBC" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WCAU" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WMAQ" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KNBC" Then
                 MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WMGM" Then
                    MyFile.WriteLine ("Special")
                    MyFile.WriteLine (" " & " " & MyLine)
                    MyFile.WriteLine (" ")
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "T"
                If Left(MyLine, 4) = "WNJU" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WWSI" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WSNS" Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KVEA" Then
                    MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KXTX" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "U"
                If Left(MyLine, 4) = "WXTV" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WUVP" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WGBO" Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KMEX" Then
                    MyFile.WriteLine ("P*" & "" & MyLine)
                End If
                If Left(MyLine, 4) = "KDWU" Then
                    MyFile.WriteLine ("P*" & "" & MyLine)
                End If
                If Left(MyLine, 4) = "KUVN" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KDTV" Then
                    MyFile.WriteLine ("Special")
                    MyFile.WriteLine (" " & " " & MyLine)
                    MyFile.WriteLine (" ")
                    End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "V"
                If Left(MyLine, 4) = "WFUT" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WFPA" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WXFT" Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KFTR" Then
                    MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KSTR" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "F"
                'Debug.Print Left(MyLine, 4)
                If Left(MyLine, 4) = "WNYW" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WTXF" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WFLD" Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KTTV" Then
                    MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KDFW" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "Y"
                If Left(MyLine, 4) = "WPIX" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WPSG" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WGN " Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KTLA" Then
                    MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KDAF" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (" " & " " & MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (" " & " " & MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "Z"
                If Left(MyLine, 4) = "WNYN" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WZPA" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WOCK" Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KAZA" Then
                    MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KODF" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "QBWB" Then
                    MyFile.WriteLine ("Special")
                    MyFile.WriteLine (" " & " " & MyLine)
                    MyFile.WriteLine (" ")
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (" " & " " & MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (" " & " " & MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If
            Case "S"
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (" " & " " & MyLine)
                End If
            Case Else
        End Select
   Loop
   
   MyFile.Close
      
GoTo Interface_Parer_exit

Interface_Parer_error:
    If Err = 53 Then
       MsgBox ("DLYDINF" & network & ".TXT doesn't exist in the directory.")
        GoTo Interface_Parer_exit
    Else
        MsgBox (Err & "  " & Error)
        GoTo Interface_Parer_exit
    End If
Interface_Parer_exit:
'End
    
End Function

IknowMe · Oct 26, 2007

Based on your example I don't even see why you'd be setting the reference to begin with. What am I missing?

Are you scraping something to build the file?

As you seem comfortable using a case statement I'd suggest nesting them to shorten your code some

e.x.

Code:

Select Case network
            Case "A"
                If Left(MyLine, 4) = "WABC" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WPVI" Then
                    MyFile.WriteLine ("E" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WLS " Then
                 MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "KABC" Then
                    MyFile.WriteLine ("P" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WFAA" Then
                    MyFile.WriteLine ("C" & " " & MyLine)
                End If
                If Left(MyLine, 4) = "WCVB" Then
                    MyFile.WriteLine ("Special")
                    MyFile.WriteLine (" " & " " & MyLine)
                    MyFile.WriteLine (" ")
                End If
                If Left(MyLine, 10) = "  STNS T/C" Then
                    MyFile.WriteLine (MyLine)
                End If
                If Left(MyLine, 16) = "  TOTAL STATIONS" Then
                    MyFile.WriteLine (MyLine)
                    MyFile.WriteLine (" ")
                    MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
                End If

Could be

Code:

Case "A"
    Select Case Left(MyLine, 4)
        Case "WABC"
            MyFile.WriteLine ("E" & " " & MyLine)
        Case "WPVI"
            MyFile.WriteLine ("E" & " " & MyLine)
        Case "WLS "
            MyFile.WriteLine ("C" & " " & MyLine)
        Case "KABC"
            MyFile.WriteLine ("P" & " " & MyLine)
        Case "WFAA"
            MyFile.WriteLine ("C" & " " & MyLine)
        Case "WCVB"
            MyFile.WriteLine ("Special")
            MyFile.WriteLine (" " & " " & MyLine)
            MyFile.WriteLine (" ")
        End Select
        If Left(MyLine, 10) = "  STNS T/C" Then MyFile.WriteLine (MyLine)
        If Left(MyLine, 16) = "  TOTAL STATIONS" Then
            MyFile.WriteLine (MyLine)
            MyFile.WriteLine (" ")
            MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
        End If

Also near the top

Code:

    Select Case network
        Case "A"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFA_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFA.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFA - ABC")
            MyFile.WriteLine (" ")
        Case "C"
            Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFC_Pared.txt", True)
            Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINFC.txt") 'INPUT FILE
            MyFile.WriteLine ("DLYDINFC - CBS")
            MyFile.WriteLine (" ")

could be

Code:

    Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINF"[COLOR=red] & Network &[/color] "_Pared.txt", True)
    Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINF"[COLOR=red] & Network &[/color] ".txt") 'INPUT FILE
   
    Select Case network
        Case "A"
             MyFile.WriteLine ("DLYDINFA - ABC")
             MyFile.WriteLine (" ")
        Case "C"
            MyFile.WriteLine ("DLYDINFC - CBS")
            MyFile.WriteLine (" ")

if you can post a sample of your DLYDINF*.txt file I can get this chunk cleaned up and working for you. You'd also have more replies with VBA help if you post VBA questions in the VBA forum. forum707

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

WinblowsME · Oct 29, 2007

Here's my suggestion to reduce redundancy and to shorten up the main function.

Code:

' [b]Untested[/b]

Function Interface_Parer(network)
   '...
   
   Set FSO = New FileSystemObject
   Set MyFile = FXO.CreateTextFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINF" & network & "_Pared.txt", True)
   Set FSOFile = FSO.GetFile("C:\Documents and Settings\houldsgh\My Documents\DLYDINF" & network & ".txt") 'INPUT FILE

   Call Write_DLYDINF(MyFile, network)

   '...

   Call Write_Header(MyFile, network)

   '...

   Call Write_Line(MyFile, MyLine, network)

   '...
End Function

Code:

Sub Write_DLYDINF(MyFile As Object, network As String)
    Select Case network
        Case "A"
            MyFile.WriteLine ("DLYDINFA - ABC")
        Case "C"
            MyFile.WriteLine ("DLYDINFC - CBS")
        Case "E"
            MyFile.WriteLine ("DLYDINFE - MT3")
        Case "F"
            MyFile.WriteLine ("DLYDINFF - FOX")
        Case "M"
            MyFile.WriteLine ("DLYDINFM - MNT")
        Case "N"
            MyFile.WriteLine ("DLYDINFN - NBC")
        Case "S"
            MyFile.WriteLine ("DLYDINFS - SYN")
        Case "T"
            MyFile.WriteLine ("DLYDINFT - TEL")
        Case "U"
            MyFile.WriteLine ("DLYDINFU - UNI")
        Case "V"
            MyFile.WriteLine ("DLYDINFV - TF")
        Case "X"
            MyFile.WriteLine ("DLYDINFX - PAX")
        Case "Y"
            MyFile.WriteLine ("DLYDINFY - CW")
        Case "Z"
            MyFile.WriteLine ("DLYDINFZ - AZA")
    End Select
    
    MyFile.WriteLine (" ")
End Sub

Sub Write_Header(MyFile As Object, network As String)
   MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
   
   Select Case network
      Case "A"
         MyFile.WriteLine ("  WABC   WPVI   KABC(p)   WFAA(c)  WLS(c)   xxxKGO(p)~xxx")
      Case "C"
         MyFile.WriteLine ("  WCBS   KYW    WBBM(c)   KCBS(p)    KTVT(c)   KTVT(c)")
      Case "E"
         MyFile.WriteLine ("  [KBEH(p)/KBLM(p)]   [KGKK(p)/KMMC(p)]   WANX(e)    KMOH(m)")
      Case "F"
         MyFile.WriteLine ("  WNYW   WTXF   WFLD(c)   KTTV(p)   KDFW(c)")
      Case "M"
         MyFile.WriteLine ("  WWOR   WPHL   WPWR(c)   KCOP(p)   KDFI(c)")
      Case "N"
         MyFile.WriteLine ("  WNBC(e)   WCAU(e)    WMAQ(c)   KNBC(p)   KXAS(c)              ")
      Case "T"
         MyFile.WriteLine ("  WNJU(e)   WWSI(e)    WSNS(c)   KVEA(p)   KXTX(c)  ")
      Case "U"
         MyFile.WriteLine ("  WXTV    WUVP    WGBO(c)   [KMEX](p)   <<<[KDWU](p)missing lately OK>>>   KUVN(c)")
      Case "V"
         MyFile.WriteLine ("  WFUT     WFPA    WXFT(c)    KFTR(p)   KSTR(c)")
      Case "X"
         MyFile.WriteLine ("  WPXN   WPPX    WCPX(c)   KPXN(p)     KPXD(c)   ")
      Case "Y"
         MyFile.WriteLine ("  WPIX    WPSG    WGN(c)    KTLA(p)   KDAF(c)")
      Case "Z"
         MyFile.WriteLine ("  WNYN   WZPA   WOCK(c)   KAZA(p)   KODF(C)")
   End Select
   
   MyFile.WriteLine (header)
   MyFile.WriteLine ("- - - - - - - - - - - - - - - - - - - - - - - - - - - - -")
End Sub

Sub Write_Line(MyFile As Object, MyLine As String, network As String)
   Dim substring As String

   substring = Left(MyLine, 4)

   Select Case network
      Case "A"
         Select Case substring
            Case "WABC" Or "WPVI"
               MyFile.WriteLine ("E" & " " & MyLine)
            Case "WLS " Or "WFAA"
               MyFile.WriteLine ("C" & " " & MyLine)
            Case "KABC"
               MyFile.WriteLine ("P" & " " & MyLine)
            Case "WCVB"
               MyFile.WriteLine ("Special")
               MyFile.WriteLine (" " & " " & MyLine)
               MyFile.WriteLine (" ")
         End Select
      Case "C"
         Select Case substring
            Case "WCBS" Or "KYW "
               MyFile.WriteLine ("E" & " " & MyLine)
            Case "WBBM" Or "KTVT"
               MyFile.WriteLine ("C" & " " & MyLine)
            Case "KCBS"
               MyFile.WriteLine ("P" & " " & MyLine)
         End Select
      Case "E"
         Select Case substring
            Case "KBEH" Or "KBLM"
               MyFile.WriteLine ("*P" & " " & MyLine)
            Case "KGKK" Or "KMMC"
               MyFile.WriteLine ("~P" & " " & MyLine)
            Case "WANX"
               MyFile.WriteLine (" E" & " " & MyLine)
            Case "KMOH"
               MyFile.WriteLine (" M" & " " & MyLine)
            Case "WMBQ"
               MyFile.WriteLine ("Special")
               MyFile.WriteLine (" " & " " & MyLine)
               MyFile.WriteLine (" ")
         End Select
      Case "X"
         Select Case substring
            Case "WPXN" Or "WPPX"
               MyFile.WriteLine ("E" & " " & MyLine)
            Case "WCPX" Or "KPXD"
               MyFile.WriteLine ("C" & " " & MyLine)
            Case "KPXN"
               MyFile.WriteLine ("P" & " " & MyLine)
         End Select
      Case "M"
         Select Case substring
            Case "WWOR" Or "WPHL"
               MyFile.WriteLine ("E" & " " & MyLine)
            Case "WPWR" Or "KDFI"
               MyFile.WriteLine ("C" & " " & MyLine)
            Case "KCOP"
               MyFile.WriteLine ("P" & " " & MyLine)
         End Select
      Case "N"
         Select Case substring
            Case "WNBC" Or "WCAU"
               MyFile.WriteLine ("E" & " " & MyLine)
            Case "KXAS" Or "WMAQ"
               MyFile.WriteLine ("C" & " " & MyLine)
            Case "KNBC"
               MyFile.WriteLine ("P" & " " & MyLine)
            Case "WMGM"
               MyFile.WriteLine ("Special")
               MyFile.WriteLine (" " & " " & MyLine)
               MyFile.WriteLine (" ")
         End Select
      Case "T"
         Select Case substring
            Case "WNJU" Or "WWSI"
               MyFile.WriteLine ("E" & " " & MyLine)
            Case "WSNS" Or "KXTX"
               MyFile.WriteLine ("C" & " " & MyLine)
            Case "KVEA"
               MyFile.WriteLine ("P" & " " & MyLine)
         End Select
      Case "U"
         Select Case substring
            Case "WXTV" Or "WUVP"
               MyFile.WriteLine ("E" & " " & MyLine)
            Case "WGBO" Or "KUVN"
               MyFile.WriteLine ("C" & " " & MyLine)
            Case "KMEX" Or "KDWU"
               MyFile.WriteLine ("P*" & "" & MyLine)
            Case "KDTV"
               MyFile.WriteLine ("Special")
               MyFile.WriteLine (" " & " " & MyLine)
               MyFile.WriteLine (" ")
         End Select
      Case "V"
         Select Case substring
            Case "WFUT" Or "WFPA"
               MyFile.WriteLine ("E" & " " & MyLine)
            Case "WXFT" Or "KSTR"
               MyFile.WriteLine ("C" & " " & MyLine)
            Case "KFTR"
               MyFile.WriteLine ("P" & " " & MyLine)
         End Select
      Case "F"
         Select Case substring
            Case "WNYW" Or "WTXF"
               MyFile.WriteLine ("E" & " " & MyLine)
            Case "WFLD" Or "KDFW"
               MyFile.WriteLine ("C" & " " & MyLine)
            Case "KTTV"
               MyFile.WriteLine ("P" & " " & MyLine)
         End Select
   End Select

   If network = "A" Or network = "C" Or network = "E" Or network = "X" Or _
      network = "M" Or network = "N" Or network = "T" Or network = "U" Or _
      network = "V" Or network = "F" Then

      If Left(MyLine, 10) = "  STNS T/C" Then
         MyFile.WriteLine (MyLine)
      ElseIf Left(MyLine, 16) = "  TOTAL STATIONS" Then
         MyFile.WriteLine (MyLine)
         MyFile.WriteLine (" ")
         MyFile.WriteLine "----------------------------------------------------------------------------------------------------------------------------------"
      End If
   End If
End Function

WinblowsME · Oct 29, 2007

Oops, last line should be

Code:

End Sub

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Setting reference WRQ Reflection for IBM Version 13.0.1 and Excel VBA

ghh3rd

IS-IT--Management

IknowMe

Programmer

ghh3rd

IS-IT--Management

IknowMe

Programmer

ghh3rd

IS-IT--Management

IknowMe

Programmer

WinblowsME

Technical User

WinblowsME

Technical User

Similar threads

Part and Inventory Search

Sponsor