Page 160 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

Table 7-2. Parameters Available with the Workbooks.OpenText
Method (continued)
Parameter Description
Other Set this parameter to True if you use OtherChar to define a non-
Chapter 7
standard delimiter character.
OtherChar The character used as the delimiter in the file to be opened. If
there is more than one character in the string, Excel uses the first
character.
DecimalSeparator, The characters assigned to these two arguments indicate the
ThousandsSeparator decimal separator (a period in the United States) and the
thousands separator (a comma in the United States).
TrailingMinusNumbers A value that indicates whether a number comes after the minus
sign (True, the default value) or if the minus sign comes after a
negative number (False, almost never used). Unless you have a
specific reason to set this parameter to False, you should never
include it in the OpenText method’s call.
Local A Boolean variable that indicates whether to use the local
language set in Excel or the local language set in VBA (if
different).

A routine to open a text file named SalesExport.txt that uses semicolons as its delimiter char­
acters would look like this:

Sub BringInText()
Workbooks.OpenText Filename:="SalesExport.txt", Semicolon:=True
End Sub

When you write a program that changes other files, it’s important to remember that you’re
assuming you have complete control over the files and that they’re not open. The best-writ-
ten code in the universe is little good to you if you try to open a file but get a read-only copy
of the file because one of your co-workers opened it to fill in some numbers for a project
briefing.
The following procedure lets you avoid those problems by checking to see if a particular
workbook is open:

Sub CheckIfOpen()
Dim Wkbk As Workbook
Dim Filename As String
Filename = InputBox("Type the name of the file you want to check.")
For Each Wkbk in Application.Workbooks
If Wkbk.Name = Filename Then
MsgBox (Filename & " is open; changes may result in errors.")
End If
Next Wkbk
End Sub

134