spencer_time
Board Regular
- Joined
- Sep 19, 2019
- Messages
- 55
- Office Version
- 365
- 2016
- 2007
- Platform
- Windows
Hello, I have some *working* code that will open multiple specialized textfiles, delimited, as consecutive sheets in a NEW workbook. I need to make it open them as consecutive sheets within a workbook specified within a variable (variable currently being defined as active workbook, but have the flexibility to change that if needed as my spreadsheet/program grows). It feels like it should be a simple problem, but everything I try either gives me an error, or some unintended side effects (such as inserting two blank columns in the middle of the delimited data).
The following is the subroutine that opens them in a new file:
Any suggestions are appreciated, thanks ahead of time.
p.s. I've tried to understand line 19 of that code (that I found on the internet and, with the help of this forum, changed to make work in my situation) and am unsure if I'm correct. I've searched a bit and it *might* be better interpreted as (-1 or 0) instead of (1 or 0) -which of course are not equal to the filename- ...but I can't find anything concrete, and although getting this spreadsheet/program working is the current short term goal, I really want to learn in the process so that I am better equipped to do this again when the need arises. Is my comment in the following snippit a good representation of what is happening?
The following is the subroutine that opens them in a new file:
Code:
Private Sub multiple_s2p()
Dim xFilesToOpen As Variant
Dim i As Integer
Dim wb As Workbook
Dim xWb As Workbook
Dim xTempWb As Workbook
Dim xDelimiter As String
Dim xScreen As Boolean
Dim currentWBname As String
Dim renameWorkbook As String
wb = ActiveWorkbook ' this is the workbook the files should go into, instead of making a new workbook
currentWBname = ActiveWorkbook.Name
renameWorkbook = "New Workbook Name"
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False ' turn off screen updating to make program run faster
xFilesToOpen = Application.GetOpenFilename("Text Files (*.s2p), *.s2p", , "Import *.s2p files", , True) ' open file dialog box
If TypeName(xFilesToOpen) = "Boolean" Then ' if filename is true or false (1 or 0) then...
MsgBox "No files were selected", , "Import *.s2p files" ' then pop up message box saying no files were selected
GoTo ExitHandler ' skip the next part and goto end of program tidying up
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("FREQUENCY", "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
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("FREQUENCY", "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
Loop
ExitHandler:
Application.ScreenUpdating = xScreen
Set xWb = Nothing
Set xTempWb = Nothing
Exit Sub
End SubAny suggestions are appreciated, thanks ahead of time.
p.s. I've tried to understand line 19 of that code (that I found on the internet and, with the help of this forum, changed to make work in my situation) and am unsure if I'm correct. I've searched a bit and it *might* be better interpreted as (-1 or 0) instead of (1 or 0) -which of course are not equal to the filename- ...but I can't find anything concrete, and although getting this spreadsheet/program working is the current short term goal, I really want to learn in the process so that I am better equipped to do this again when the need arises. Is my comment in the following snippit a good representation of what is happening?
Code:
If TypeName(xFilesToOpen) = "Boolean" Then ' if filename is true or false (1 or 0) then...
