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

spencer_time

spencer_time

Board Regular
Joined
Sep 19, 2019
Messages
55
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
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.

Code: 
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.

Code: 
! 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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Cross posted https://www.excelforum.com/excel-pr...nning-with-add-new-first-row.html#post5199672

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Okay Fluff, I understand and apologize. I did not read your forum rules before posting. If it is something that is not being answered and I must post it in multiple locations in the future, I will make sure I include a direct link to the other location.

Trent Spencer - KM4YTU
 
Upvote 0
I have now read your forum's rules and will follow them in all future posts.

Also, I wanted to edit my last post to say this, but do not see an edit function. Is editing of posts possible on this forum? If so, would you mind telling me how, or where to look for the link to initiate an edit?

Thanks,
Trent Spencer - KM4YTU
 
Upvote 0
Also, I wanted to edit my last post to say this, but do not see an edit function. Is editing of posts possible on this forum? If so, would you mind telling me how, or where to look for the link to initiate an edit?
Click to expand...
Forum regulars have about 10 minutes in which to edit a post (I think newbies may not have that option). After that, you would simply need to post back to the original thread with any clarifications, updates, or corrections.
 
Upvote 0
You can replace this
Code: 
    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"
with
Code: 
   With Range("A:A")
      .Replace "!*", True, xlWhole, , , , False, False
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
      On Error GoTo 0
   End With
   Range("A1:I1").Value = Array("MHZ", "S11 MAGNITUDE", "S11 PHASE", "S21 MAGNITUDE", "S21 PHASE", "S12 MAGNITUDE", "S21 PHASE", "S22 MAGNITUDE", "S22 PHASE")
   Range("A:A").NumberFormat = "0000"
   Range("B:B,D:D,F:F,H:H").NumberFormat = "00.000000"
   Range("C:C,E:E,G:G,I:I").NumberFormat = "000.0000"
 
Upvote 0
Fluff, thank you for your help thus far.

It worked for some .s2p files, but others do not seem to delimit correctly. Also when the headers are being input into the first row, I get a message asking "There's already data here. Do you want to replace it?" with OK/Cancel as options. Below is a sample of one of the s2p files that didn't work, they do not have comments in them. I get this problem whether I upload multiple files, or just one.

Code: 
# Hz S DB R 50
1000000    -16.9496242609488    -8.58689662029176    -16.6107105674255    8.20757000102712            -16.6121801059357    7.98110601774455    -16.9028290258889    -8.66753742818949
2000000    -17.160345039084    -16.4903529866769    -15.9980790253834    14.59165897376            -16.0025012756311    14.4256202043857    -17.113823616587    -16.5219217597258
3000000    -17.4569110234875    -24.1547399094944    -15.2220779848417    18.706040552588            -15.2282387099023    18.5873116035894    -17.4077362618371    -24.1307511576949
4000000    -17.8307278351374    -31.2807027225613    -14.4537659135093    20.9083912394711            -14.4581443916628    20.8183242077884    -17.7850771752075    -31.2052091469189
5000000    -18.2635494633286    -37.8606323704411    -13.7671911691377    21.7632657719349            -13.7707058866981    21.682704753608    -18.2229646079218    -37.7603555584193
6000000    -18.730648578772    -43.8725449781662    -13.1847374947529    21.7733591442639            -13.1895055118683    21.7216253589571    -18.6983698884529    -43.7207347699322
7000000    -19.2182762254781    -49.3083973970294    -12.7056772300504    21.3001355585327            -12.7109513563425    21.2493756539912    -19.189886726648    -49.141275758642
8000000    -19.7124864707961    -54.2388671491435    -12.3136884128315    20.5715665149253            -12.3181260117452    20.5296666844449    -19.6881317026912    -54.0687787737426
9000000    -20.1903973055686    -58.7091102681567    -11.9938817868525    19.7241675954174            -12.0000222839416    19.676608677037    -20.1806618278928    -58.5242230646935
10000000    -20.6595854010026    -62.8098038970424    -11.7308619578893    18.8410710335886            -11.7390341192435    18.8096097533336    -20.6637632756176    -62.5531421398315
11000000    -21.1066505338272    -66.507665582555    -11.5149787712039    17.962673281709            -11.5224766508234    17.9490922607454    -21.1244620470981    -66.2714543216958
12000000    -21.5400665660637    -69.9385292007056    -11.3360261904472    17.1358236189079            -11.3421129844749    17.1111870455983    -21.5666862535383    -69.6636542206523
13000000    -21.9459858342634    -73.0643875897233    -11.1866852896227    16.349843282536            -11.1931081558624    16.3347172103154    -21.9885661067405    -72.795847559659

Here is a photo of how the file delimits incorrectly with some files...others work fine. I suspect that it is that in this s2p file the "-" and trailing zeros cause all the columns to not line up perfectly.

uc
 
Upvote 0
Ok, how about
Code: 
Sub Spencertime()
   With Range("A:A")
      .Replace "!*", True, xlWhole, , , , False, False
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
      On Error GoTo 0
   End With
   Range("A1:I1").Value = Array("MHZ ", "S11 MAGNITUDE", "S11 PHASE", "S21 MAGNITUDE", "S21 PHASE", "S12 MAGNITUDE", "S21 PHASE", "S22 MAGNITUDE", "S22 PHASE")
   Range("A2:A1000000").TextToColumns _
      Destination:=Range("A2"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=True, _
      Tab:=False, Semicolon:=False, _
      Comma:=False, Space:=True, _
      Other:=True, OtherChar:="|"
   Range("A:A").NumberFormat = "0000"
   Range("B:B,D:D,F:F,H:H").NumberFormat = "00.000000"
   Range("C:C,E:E,G:G,I:I").NumberFormat = "000.0000"
End Sub
You will need to remove the TextToColumns from your original code.
 
Upvote 0
Solution
Fluff, again, thank you. You once again have gotten me *closer* to solving this aspect of my spreadsheet program.

I am still a beginner when it comes to coding, so it took me a few tries to figure out what needed to be deleted to enable your suggestion to work.

The problem is now quite a bit stranger than before, it now no longer deletes any of the data, but it still inputs -blank- columns in between the fifth and sixth columns (puts 2 blank columns after column E), but the first row remains unchanged.

uc
 
Upvote 0
Could you upload a couple of the s2p files to a share site such as OneDrive, DropBox, GoggleDrive. Then mark for sharing & post the link to the thread?
Otherwise it's going to be very difficult to figure out what's going on.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
162
DanteAmor
DanteAmor
I
  • Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
162
ItalianPlatinum
I
I
  • Question
Modify VBA script to count rows with data in multiple sheets
Replies
0
Views
74
irishjsk
I
O
  • Question
Multiple VBA codes on sheet1
Replies
1
Views
153
skillilea
S
F
  • Question
VBA Import columns by headers - Need prompts to select workbook/sheet name
Replies
2
Views
175
floggingmolly
F

Forum statistics

Threads
1,214,520
Messages
6,120,016
Members
448,936
Latest member
almerpogi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top