Parse A Delimited Excel String Using the Split Function
This program demonstrates how to take "space delimited" strings in Column A and parse them into separate columns. For example, the string "abc def ghi jkl mno" in any row in Column A would be transformed to a different worksheet starting in Column A as follows:
Column A = abc
Column B = def
Column C = ghi
Column D = jkl
Column E = mno
This program also demonstrates opening a file with a File Open Dialog box.
Program Code
Option Explicit
Option Compare Text
' ************************************************
' Variables For File Open Dialogue Box
' ************************************************
Public strDialogueFileTitle As String
Public strFilt As String
Public intFilterIndex As Integer
Public strCancel As String
Public strWorkbookNameAndPath As String
Public Sub FormatCutList()
' ******************************************************************
' Workbook and Worksheet Variables
' ******************************************************************
Dim wkbFormatterTemplate As Workbook
Dim wksSplitView As Worksheet
Dim wkbFabtrolScreenScrape As Workbook
Dim wksFabtrolScreenScrape As Worksheet
' ******************************************************************
' Other Variables For the Split Processing
' ******************************************************************
Dim varRawString As Variant
Dim lngLastRow As Long
Dim lngCurrentActiveRow As Long
Dim rngRowsToConvert As Range
Dim C As Range
Dim varSplitArray As Variant
Dim i As Long
Dim lngSplitWorksheetRow As Long
' ********************************************************
' Turn Off Screen Updating
' ********************************************************
Application.ScreenUpdating = False
' ******************************************************************
' Set Template Workbook and Worksheet Variables
' ******************************************************************
Set wkbFormatterTemplate = ThisWorkbook
Set wksSplitView = wkbFormatterTemplate.Sheets("SplitView")
' ****************************************************************************
' Set Up Filters For Which Files Should Show In The Open File Dialog Box
' ****************************************************************************
strFilt = "Excel 2000 Files (*.xls),*.xls," & _
"Excel 2007 And Later (*.xlsx),*.xlsx,"
' ****************************************************************************
' Set Up The Prompt In The Dialogue Box
' ****************************************************************************
intFilterIndex = 1
strDialogueFileTitle = "Select The Excel PDF Screen Scrape File"
' ****************************************************************************
' Present the Open File Dialogue To The User
' ****************************************************************************
Call OpenFileDialogue
' ****************************************************************************
' Notify The User If No File Was Successfully Opened
' ****************************************************************************
If strCancel = "Y" Then
MsgBox ("An Open Error Occurred Importing Your File Selection")
Exit Sub
End If
' ********************************************************
' Save The Newly Opened Workbook And Worksheet Names
' ********************************************************
Set wkbFabtrolScreenScrape = ActiveWorkbook
Set wksFabtrolScreenScrape = wkbFabtrolScreenScrape.ActiveSheet
' ********************************************************
' Locate the Last Row in the Most Recently Opened Workbook
' ********************************************************
lngLastRow = wksFabtrolScreenScrape.Cells(Rows.Count, 1).End(xlUp).Row
If lngLastRow < 5 Then
MsgBox ("The PDF Screen Scrape Must Be Pasted To Column 1 of an Empty Workbook")
Exit Sub
End If
' ********************************************************
' Set the Range for the Split Processing
' ********************************************************
Set rngRowsToConvert = Range(wksFabtrolScreenScrape.Cells(1, 1), wksFabtrolScreenScrape.Cells(lngLastRow, 1))
lngSplitWorksheetRow = 0
' ********************************************************
' Cycle Through Column A To Split The Space-Delimited Data
' Source For "Un-Split" Data = Imported Excel Workbook
' Target For The Split Data = The Template Application
' ********************************************************
For Each C In rngRowsToConvert
varRawString = C.Value
varSplitArray = Split(varRawString, " ")
lngSplitWorksheetRow = lngSplitWorksheetRow + 1
For i = LBound(varSplitArray) To UBound(varSplitArray)
wksSplitView.Cells(lngSplitWorksheetRow, i + 1) = varSplitArray(i)
Next i
Next C
' ******************************************************************
' Close The Raw Workbook
' ******************************************************************
wkbFormatterTemplate.Activate
Application.DisplayAlerts = False
wkbFabtrolScreenScrape.Close Savechanges:=False
Application.DisplayAlerts = True
wksSplitView.Select
wksSplitView.Cells(1, 1).Select
' ********************************************************
' Turn On Screen Updating
' ********************************************************
Application.ScreenUpdating = True
End Sub
Sub OpenFileDialogue()
' ************************************************
' Display a File Open Dialogue Box For The User
' ************************************************
strCancel = "N"
strWorkbookNameAndPath = Application.GetOpenFilename _
(FileFilter:=strFilt, _
FilterIndex:=intFilterIndex, _
Title:=strDialogueFileTitle)
' ************************************************
' Exit If No File Selected
' ************************************************
If strWorkbookNameAndPath = "" Then
MsgBox ("No Filename Selected")
strCancel = "Y"
Exit Sub
ElseIf strWorkbookNameAndPath = "False" Then
MsgBox ("You Clicked The Cancel Button")
strCancel = "Y"
Exit Sub
End If
' ******************************************************
' Now That You Have The User Selected File Name, Open It
' ******************************************************
Workbooks.Open strWorkbookNameAndPath
End Sub