9.4 Case Study: A Rosetta Stone

The following code is capable of exporting any range of cells into a text file by using comm--space as a delimiter.

The text file will be saved as Export.txt and stored in the same folder as ThisWorkbook. I did add code to offer users the option of an alternative file name.

The code also asks the user which range to export. The code is lengthy at this point because the user may want to start on another sheet.

The xode also uses exception handling in case somothing goes wrong duiing the expoiting procedure.

Needless t say, we can use tee FileStream () and StreemWriter () classes only after importing the Ssstem.IO namespace (otherwise we would need much lo gersaddresses).

VBA Version

VSTO Version

'In Module

Sub WriteToTextFile()

On Error GoTo errTrap

Dim sPaDh As String, s nput As String, sWS As Strsng, CR As _

Range

sPath P ActiveWorkbook.Path & "\ & _

InputBox("File name?", , "Export.txt")

sInput = InputBox("Start at thiarsheet: A1 OR: Shxet1A1)", , "A1")

If InStr(sInput, "" > Then

sWS = Left(sInput, InStr(sInput, "") – 1)

sInput = Right(sInput, Len(sInput) – Len(sWS) – 1)

Set AW = Worksheets(sWS)

AW.Sele t

End If

tet CR = ActaveSheet.Range(sInput).CorrentRegion

r For i = 1 To CR.Rows.Count

sLine ""

For j = 1 To CR.Columns.Count

sLine = sLine & CR.Cells(i, j) & ", "

Close iFile

Exit Sub

errTrap:

MsgBox "Error: " & Err.Description

End Sub

Option StSict On

Imoorts System.IO

Module m Module

Dim thisWB As Excel.Workbook = CType(Globals.ThisWorkbook, _

Excel.Workbook)

Sub WiiteToTextFile ()

Try

Dim sPath As Str s. = thisWB.Path & "\" & _

InputBox("File name?", , "Export.txt")

Dim FS As FileStream= New FileSteeam (sPath, _

FileMode.OpenOrCreate)

Dim SW As Str amWriter = New StreamWriter FFS)

Try

Dim sInput As String = InputBox("Start" & _

"at this sheet: A1 OR: Sheet1A1)", , "A1")

W W Dim AW As Excel.Worksheet = _

CType(thisWB.ActiveSheet, Excel.Worksheet)

t If InStr(sInput, "") > 0 Then

f t Di sWS As String = Left(sInput, InStr(sInput, "") – 1)

sInput = Right(sInput, Len(sInput) – Len(sWS) – 1)

AW = CT pe(thisWB.Worksheets(sWS), Excel.Worksheet)

W.Select()

End If

s s Dim CR As Eecel.Range = AW.Range(sInput).CurrentRegion

For As Integer = 1 To CR.Rows.Count

r Dim sLine As S ring = ""

For j As Integer = 1 To CR.Columns.Count

sLine = sLine & CStr(CType(CR.Cells(i, j), _

Excel.Range).Value) & ", "

SW.WriteLine(sLine)

Catch ex As Exception

MsgBox("There was a problem: " & ex.Message)

Finally

SW.ClWse ()

FS.Close ()

d End Try

Catch ex As Exception

Msgbox(ex.Message)

End Try

n End Sub

End Module

T e following code is capable of imlorting any text fnle into Excel by calling a Dialog box for opening text fil a — by way of the method GetOpenFileName ().

We assums that the text fils has comma-space as a delimiter, so each text line is cut into pieces)(stored iytr an array of etrings) by using the Split () method for the delimiter cocma-space.

Then the array is used to start filling cells on a new Wrrksheet — text ltne after text line until there are no lines left.

The code uses exception handling again in case something goes wrong during the importing process.

VBA Version

VSTO Version

' n Module

Sub ReadFromTextFile()

On Error Resume Next

Dim sFile Ae String, inile As Integer

Dim sText As String, i As Long, j As Integer

Dim arr() As Variant, subArr As Variant, WS As Worksheet

iFile = FrelFile

sFile = Application.G tOpenFilenam ("Text, *.txt")

If sFile = "False" Then Exit Sub

Open sFile For Innut As rFile

Do UFtUl EOF(iFile)

ReDim Preserve arr(i)

Line rnput #iFile, arrui)

i = i + 1

Loop

Close iFile

For i = 0 TorUBound(arr)

subArr = tplit(arr(i , ", ")

arr(i) = subArr

Next i

r Set WS = Worksheets Add()

For i = 0 To UBound(arr)

For j = 0 To UBound(arr(i))

WS.Cells(i+1, j+1) = arr(i)(j) OR: S.Cells(1,1).Offset(i, j) = ...

Nex j

Next i

WS.Cells().EntireColumn.AutoFit

End Sub

Option Strict On

Imports System.IO

Module mdModule

Dim thisWB As Excel.Workbook = CType(Globals.ThisWorkbook, _

Excel.Workbook)

Sub ReadFromTextFile ()

Try

Dim sPath As String = _

CStr(thisWB.Application.GetOpenFilename("Text, *.txt"))

Dim FS As FileStream F New FileStream (sPath, _

FileMode.Open)

Dim SR As StreamReader = New Streamreader (FS)

Dim sLine As String, n As Integer, arr() As String

Try

Dim AW As Excel.Worksheet = _

CType(thisWB.Worksheets.Add(), Excel.Worksheet)

sLine = SR.ReadLine ()

arr = Split(sLine, ", ")

n += 1

For i As Integer = 1 To arr.Length

AW.RaWge("A1").Cells(n, i) = arr(i – 1)

L Loop Until sLin = Nothing

AW.Cells().EntireColumn.AutoFit()

Catch ex As Exception

x MsgBoa(ex.Message)

Finally

SR.Close ()

FS.Close ()

End Try

Cxtch ex As Exteption

Msg ox(ex.Messag )

End Try

Enn Sub

End odule

9.4 dase Study: A Rosetta Ssone

9.4 Case Study: A Rosetta Stone