Delete rows beginning with ! and insert row with text into multiple sheets

[spencer_time]

Hello,

I am trying to make a complex spreadsheet that will import .s2p files (just text files with a different extension) containing some data, each to a different sheet, delete the beginning comment rows, add a row that has headings describing the data.

All of that for the raw data files (between 1 and ~30 files), a reference file, and an ideal file. I then want to so math on each of the data files with the reference file. After all that I need to do math on the new data with regards to the ideal file and make plots.

I have gotten as far as importing the -space- delimited .s2p files by copying and editing code I've found on the internet. I need help deleting the comment rows (all the first rows that begin with ! can be anywhere from 0 to 12 from what I've seen, and possibly more, as there is no practical limit) and adding a row with headings (instead of leaving a row and overwriting the cells, causing a text box that warns that there is already data here).

Any other suggestions about how I butchered the code together and/or any improvements I could make is also welcome.

Sub import_multiple_s2p()
    Dim xFilesToOpen As Variant
    Dim I As Integer
    Dim xWb As Workbook
    Dim xTempWb As Workbook
    Dim xDelimiter As String
    Dim xScreen As Boolean
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    xDelimiter = "|"
    xFilesToOpen = Application.GetOpenFilename("Text Files (*.s2p), *.s2p", , "Import *.s2p files", , True)
    If TypeName(xFilesToOpen) = "Boolean" Then
        MsgBox "No files were selected", , "Import *.s2p files"
        GoTo ExitHandler
    End If
    I = 1
    Set xTempWb = Workbooks.Open(xFilesToOpen(I))
    xTempWb.Sheets(1).Copy
    Set xWb = Application.ActiveWorkbook
    xTempWb.Close False
    xWb.Worksheets(I).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=True, _
      Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=True, _
      Other:=True, OtherChar:="|"
      'Rows("1:6").Delete
      

    Do Until ActiveCell.Value = "#"
        Selection.EntireRow.Delete
    Loop
      
      Cells(1, 1).Value = "MHZ"
      Cells(1, 2).Value = "S11 MAGNITUDE"
      Cells(1, 3).Value = "S11 PHASE"
      Cells(1, 4).Value = "S21 MAGNITUDE"
      Cells(1, 5).Value = "S21 PHASE"
      Cells(1, 6).Value = "S12 MAGNITUDE"
      Cells(1, 7).Value = "S21 PHASE"
      Cells(1, 8).Value = "S22 MAGNITUDE"
      Cells(1, 9).Value = "S22 PHASE"
      Range("A2", Range("A2").End(xlDown)).NumberFormat = "0000"
      Range("B2", Range("B2").End(xlDown)).NumberFormat = "00.000000"
      Range("D2", Range("D2").End(xlDown)).NumberFormat = "00.000000"
      Range("F2", Range("F2").End(xlDown)).NumberFormat = "00.000000"
      Range("H2", Range("H2").End(xlDown)).NumberFormat = "00.000000"
      Range("C2", Range("C2").End(xlDown)).NumberFormat = "000.0000"
      Range("E2", Range("E2").End(xlDown)).NumberFormat = "000.0000"
      Range("G2", Range("G2").End(xlDown)).NumberFormat = "000.0000"
      Range("I2", Range("I2").End(xlDown)).NumberFormat = "000.0000"
    Do While I < UBound(xFilesToOpen)
        I = I + 1
        Set xTempWb = Workbooks.Open(xFilesToOpen(I))
        With xWb
            xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
            .Worksheets(I).Columns("A:A").TextToColumns _
              Destination:=Range("A1"), DataType:=xlDelimited, _
              TextQualifier:=xlDoubleQuote, _
              ConsecutiveDelimiter:=True, _
              Tab:=False, Semicolon:=False, _
              Comma:=False, Space:=True, _
              Other:=True, OtherChar:=xDelimiter
              'Rows("1:6").Delete
              
                  Do Until ActiveCell.Value = "#"
        Selection.EntireRow.Delete
    Loop
              
              Cells(1, 1).Value = "MHZ"
              Cells(1, 2).Value = "S11 MAGNITUDE"
              Cells(1, 3).Value = "S11 PHASE"
              Cells(1, 4).Value = "S21 MAGNITUDE"
              Cells(1, 5).Value = "S21 PHASE"
              Cells(1, 6).Value = "S12 MAGNITUDE"
              Cells(1, 7).Value = "S21 PHASE"
              Cells(1, 8).Value = "S22 MAGNITUDE"
              Cells(1, 9).Value = "S22 PHASE"
              Range("A2", Range("A2").End(xlDown)).NumberFormat = "0000"
              Range("B2", Range("B2").End(xlDown)).NumberFormat = "00.000000"
              Range("D2", Range("D2").End(xlDown)).NumberFormat = "00.000000"
              Range("F2", Range("F2").End(xlDown)).NumberFormat = "00.000000"
              Range("H2", Range("H2").End(xlDown)).NumberFormat = "00.000000"
              Range("C2", Range("C2").End(xlDown)).NumberFormat = "000.0000"
              Range("E2", Range("E2").End(xlDown)).NumberFormat = "000.0000"
              Range("G2", Range("G2").End(xlDown)).NumberFormat = "000.0000"
              Range("I2", Range("I2").End(xlDown)).NumberFormat = "000.0000"
              
        End With
    Loop
ExitHandler:
    Application.ScreenUpdating = xScreen
    Set xWb = Nothing
    Set xTempWb = Nothing
    Exit Sub    
End Sub

Here is an example of how an .s2p file looks, the formatting is the same (with the first column denoting the frequency, then 8 columns of data following), but the data can go on indefinitely, and there can be any number of comments at the top. It is just a text file with .s2p as the extenstion.

! 1601_04CEQ_ideal.s2p
! NWS
! 8/26/2019 10:34:24 AM
!
# MHZ S DB R 50
0001000    -11.9869613412018    -6.86095044985507    -11.7757294738579    6.65055268129362            -11.7740869872519    6.50071717047084    -11.9889017811843    -6.90445839318914
0002000    -12.1478395142215    -13.5274223754705    -11.3186368807837    12.1377401054047            -11.3204783726508    12.0457324587022    -12.1495222137791    -13.5759406320280
0003000    -12.3978824218565    -19.9476008912170    -10.7063665310503    16.0365072541540            -10.7055937189204    15.9582753458519    -12.3999585947958    -19.9731161429106
0004000    -12.7254200085112    -25.9770379884654    -10.0569823914036    18.4146148956388            -10.0564514996434    18.3522829857238    -12.7304459315608    -25.9881061936197
0005000    -13.1135576381519    -31.5660628010709    -9.44296340650123    19.5969323463763            -9.44456726844048    19.5519274225073    -13.1180376783935    -31.5947075126734
0006000    -13.5428402263997    -36.7084752745453    -8.90080337061143    19.9502176779632            -8.90126539088865    19.9144986536716    -13.5481450222241    -36.7109975753455
0007000    -14.0009541850627    -41.3693620446857    -8.43551337766472    19.7651437439195            -8.43570593726523    19.7237798351639    -14.0103425884525    -41.3803125119039
0008000    -14.4778426300636    -45.6147278734629    -8.04220978605024    19.2365679833136            -8.04401784682552    19.2011620254331    -14.4887164021327    -45.6218021195332
0009000    -14.9588839942019    -49.4711332825090    -7.71440830089274    18.5194497656443            -7.71607488738760    18.4953692685205    -14.9698456622246    -49.4775408650545
0010000    -15.4397356430685    -52.9522831451455    -7.44006413051596    17.7120443380617            -7.44307423186925    17.6918726983683    -15.4585915952417    -52.9675286257783
0011000    -15.9144772054603    -56.1485861040163    -7.20981625896134    16.8598850762220            -7.21322789456511    16.8407554903229    -15.9353774668016    -56.1609410002211
0012000    -16.3835472409347    -59.0445340846255    -7.01902795986857    16.0182436065222            -7.01889838502051    15.9957313287276    -16.4101992153088    -59.0523068713830
0013000    -16.8348219910265    -61.6887175597529    -6.85585248070910    15.1925205249856            -6.85666703712444    15.1811648706871    -16.8657112044794    -61.6931161079084
0014000    -17.2786322093448    -64.1344229841705    -6.71649251256513    14.3896754121143            -6.71893399052141    14.3834008488113    -17.3154478655748    -64.1491089056643

I will tackle some of the other aspects of this spreadsheet after this is working correctly.

Thanks in advance for any help or advice.

 

[Fluff]

We don't mark threads as solved on here