Listing Examples of the VBA Open Statement

Sub SimpleOpenExamples() Dim lInputFile As Long Dim lOutputFile As Long Dim lAppendFile As Long

' Get a valid file number lInputFile = FreeFile

' Open MyInputFile.txt for input

Open "C:\MyInputFile.txt" For Input As #lInputFile

' Get another valid file number lOutputFile = FreeFile ' Create a new file for output

Open "C:\MyNewOutputFile.txt" For Output As #lOutputFile

' Get another valid file number lAppendFile = FreeFile

' Open MyAppendFile.txt to append data to it ' or create new file if MyAppendFile doesn't exist Open "C:\MyAppendFile.txt" For Append As #lAppendFile

' Close the files

Close lInputFile, lOutputFile, lAppendFile End Sub

The SimpleOpenExamples procedure opens three files. The first example opens a file named MyInputFile.txt for input. This statement generates a run-time error if the file is not found, so it would be best to use an error-handling mechanism. The second example opens a new file for output, overwriting any existing file with the same name in the same directory without any warning. The third example opens a file in Append mode. If an existing file is present, the existing file is used; otherwise a new file is created.

WARNING Openingfiles is an error-prone process. You would be wise to use an error-handling mechanism in any procedure that uses Open.

Most businesses are not open 24/7/365 and your text files should not be either. Closing files that you have opened is easily achieved by using VBA's Close statement followed by the file number you want to close. The official syntax of Close is

Close [FilenumberList]

where FilenumberList is a comma-separated list of file numbers to close. If you do not specify any file numbers, Close will close all active files that were opened using Open. Obviously, after you call Close with a file number, the file number no longer ceases to be associated with the file that was closed.

File I/O

Opening and closing files does not do any good unless you can read or write from those files. Depending on what you are doing, you will use Input, Input #, Line Input #, Print #, or Write # to read from or write to a file. The use of these statements is detailed in the following list.

Input The Input statement is used to read data from a text file n characters at a time where n is the number of characters to read. Input returns any character it comes across such as commas, tabs, quotation marks, linefeeds, and spaces in addition to normal text. You can use the VBA functions EOF and LOF to determine if you have reached the end of file and to determine the length (size) of the file.

Input # The Input # statement is more sophisticated than Input. Input # knows how to read data into a series of variables. It also removes quotes and converts date strings to VBA dates. Use Input # on files created using the Write # statement.

Line Input # The Line Input # statement is meant for reading entire lines of data at once from a text file. Line Input # is usually used against text files written using the Print # statement.

Print # Print # is used to write display-formatted data to a text file. Print # is usually used to create files for use with the Input statement.

Write # Write # is meant for creating data-oriented text files. Files created using Write # can be easily read using the Input # statement.

Listing 15.5 presents an example that uses Write # to create a comma-delimited file based on the worksheet shown in Figure 15.16. After creating a new file, a second procedure opens the file and writes the data from the file onto a new worksheet using Input #.

Figure 15.16

Sample data for experimenting with Write # and Input #

Figure 15.16

Sample data for experimenting with Write # and Input #

O 11 iMil 1 H (iii.itr if 1 ni -- -.

5

BSE

iE 11] Filo Edt View Insert Format Tools Data

Wilder Hdp

- 5 X

: J

ti

_J -O

dJ

ÜL_

Reply bvîhçhortpes

_jaj I z ■. ioo% . 1

VûÈ

A1

-

f, Symbol

A

B

c

D

i Í

F

G

H 1

1

!_J^

1

[Symbol I

[Date

Open

High

Lew

Last

Vol il m â

Open Interest

2

MWZ031

1 maxa

340.5

354

343

354

7353

23345

3

MWZ03

6/4Ö002

352

352

351

351

3352

23549

4

MWZ03

6/5Í2002

351

351

343

349

3854

23283

5

kíWOB

6«2002

0

34T

347

347

3614

23147

S

MWZD3

6/7/20GG

0

339

333

339

3311

23299

7

MWZ03

6/10/2002

333

339

338

339

2033

21274

0

MWZ03

6/11/2002

0

339

333

339

3064

21042

9

MWZD3

6/120002

340

340

337

338 5

4216

21287

10

NÍWZD3

Ë/13/20Q2

3375

330

336

33a

4031

21836

¡ 11

MWZ03

6/14(2002

331

331

326

326

4B34

22284

12

MWZ03

6/17/20Q2

323

330

323

330

2437

22034

13

MWZ03

6/18Í2002

333

333

329

330

4527

22566

14

MW203

6/19/2002

325 5

325.5

325.5

325 5

5346

22837

15

MWZ03

6/20/20CQ

0

326

3£E

32S

11055

23727

V

H i

» ¡Tí,sheet!/I Rescl From rett Fie 1 leït toc^Lmn! '/{<

I

>r

1 Ready

+1 0

Responses

  • Eric
    How to use the open statement in VBA?
    7 years ago

Post a comment