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.
 
Sort by date Sort by votes

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I tried to edit the last post but 10minutes had past.

Ideal1500 works as expected
1601Ideal works as expected
19 works as expected
09232019 has the extra column problem
manu_comment_test has the extra column problem

p.s. I could not get *.s2p to select all the files, but holding shift or ctrl while selecting them does work.

EDIT:08:55
Unfortunately, 09232019 is in the format that ~ 70% of what I will be using this with will be in, and as such, the one I need to work the most.
 
Last edited:
Upvote 0
Do you want to open all files in a folder, or do you need to be able to select those you want?
 
Upvote 0
Most of the time I would need all in a folder.

However, I'm fine with selecting them with *shift* as that works, but the extra columns on some files is a problem.

EDIT:09:40
UPDATE: I have tried removing the part that makes the header and the part that changes the number format and the problem remains, I'm grasping at straws here and do not understand. The files import delimited correctly if done manually, with tab and space selected.
 
Last edited:
Upvote 0
If I turn off consecutive delimiter when manually importing the file (you have to select all files {above import/cancel} to open an s2p file while trying to manually import a text file) I get the same extra column problem. What's confusing about that is that in the code consecutive delimiter appears to be on.
 
Upvote 0
Ok, how about
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
    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
    
    With Range("A:A")
      .Replace "!*", True, xlWhole, , , , False, False
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
      On Error GoTo 0
   End With
   On Error Resume Next
   Rows(2).SpecialCells(xlBlanks).EntireColumn.Delete
   On Error GoTo 0
   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:=vbTab
   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"
    Do While I < UBound(xFilesToOpen)
        I = I + 1
        Set xTempWb = Workbooks.Open(xFilesToOpen(I))
         xTempWb.Sheets(1).Move after:=xWb.Sheets(xWb.Sheets.Count)
         With Range("A:A")
            .Replace "!*", True, xlWhole, , , , False, False
            On Error Resume Next
            .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
            On Error GoTo 0
         End With
         On Error Resume Next
         Rows(2).SpecialCells(xlBlanks).EntireColumn.Delete
         On Error GoTo 0
         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:=vbTab
         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"
    Loop
ExitHandler:
    Application.ScreenUpdating = xScreen
    Set xWb = Nothing
    Set xTempWb = Nothing
    Exit Sub
End Sub
 
Upvote 0
You have done it!

I was beginning to think it couldn't be done the way I needed it to be, I have been -off and on- working on this for a week now.

I have tested it with all the different types of s2p's I know I have and it works with them all.

Thank you so much, you have been a wonderful help!
 
Upvote 0
Okay, I hate to ask so many questions, but without the ability to edit my first post/thread, how do I mark this thread as solved?
 
Upvote 0
Glad to help & thanks for the feedback.
With files like that it's normally better to import them, rather than just open them, but I can't remember how to do it.
 
Upvote 0
After looking back a bit, I'm guessing that is just not possible to do on this forum.

[SOLVED] in post #16 by Fluff [SOLVED]
 
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
184
DanteAmor
DanteAmor
I
  • Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
188
ItalianPlatinum
I
I
  • Question
Modify VBA script to count rows with data in multiple sheets
Replies
0
Views
93
irishjsk
I
O
  • Question
Multiple VBA codes on sheet1
Replies
1
Views
159
skillilea
S
F
  • Question
VBA Import columns by headers - Need prompts to select workbook/sheet name
Replies
2
Views
186
floggingmolly
F

Forum statistics

Threads
1,214,870
Messages
6,122,021
Members
449,060
Latest member
LinusJE

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