Re: open statement, pathname and file extension

• Messages sorted by: [ date ] [ thread ]

Date: Sun, 31 Oct 2004 17:07:13 +1100

First, don't mix up the file number (as #k) with the index of the
InFileName() array. The 'as #n' number refers to the input/output channel.
EOF() also refers to that channel, which is why EOF fails when k is greater
than 1. At any given time, some channel numbers may be in use, which is the
point of FreeFile -- it returns a number that is not in use.

You should have something like

Dim pFileNum as long

For k = 1 to NoofFiles
    pFileNum = FREEFILE
    Open InFileNume(k) as pFileNum

    ....
    IF EOF(pFileNum) then ...

    Input #pFileNum, ....

    Close #pFileNum
Next

Second, I think you haven't really thought through the logic of what you're
doing. These lines don't really make sense:

> Input #k, X(nd), Y(nd)
> Xn(nd) = X(nd)
> Yn(nd) = Yn(nd) + Y(nd)

The third line looks like you want the sum of all the Y values: but since
you increment nd with each line of input that's not what's happening.

Third, you're not checking if nd exceeds the ubound of the target arrays.

App.Dir tells you the application folder. However as a general principle,
applications and data should not normally be in the same place.

You can't use Open to work out the filename extension for you. You have to
do that yourself: eg, use Dir() to find all files with a given stem,
regardless of extension.

"Danny" <Danny@discussions.microsoft.com> wrote in message
news:5D993B56-03DD-4E93-B387-742503B4EA48@microsoft.com...
> Hi
>
> I use VBA in MS Excel. I am trying to create files for inputting and
saving
> the data. The code including the open statement is as follow:
>
> For k = 1 To NoofFiles
> Open InFileName(k) For Input As #k
> nd = 0
> Do
> If EOF(1) Then Exit Do
> nd = nd + 1
> Input #k, X(nd), Y(nd)
> Xn(nd) = X(nd)
> Yn(nd) = Yn(nd) + Y(nd)
> Loop
> Close #k
> Next k
>
> The three major issues I have here, are as follows:
>
> 1. The open statement above does not open the the second file (#2) in the
> second iteration, hence an error message always pops-up ("Bad file name or
> number"). It stops when it tries to read the "EOF(1)" statement in the
second
> iteration. However, when I replace the variable k with FreeFile, it works.
> Why does this error occur? I have declared k as integer. It should provide
me
> the same result, right? How can I resolve this if I still want to keep
using
> the variable k?
>
> 2. The code requires to declare the complete file pathname so that the
input
> and output can be directed into a specific directory. I open my Excel file
> from a specific directory in the HD (C:\mydata\myvba\). If I don't declare
> the full file pathname, the target default folder is always "My Documents"
in
> the HD. Can I change this default folder to a folder where my VBA program
in
> (the folder where I run my program)? Is there any statement that may
> automatically direct the input and output files into my active
> directory/folder (the directory where I have my program in), so that when
I
> copy the program into another folder and I run it, it will look for the
input
> files from and write the output files into the same folder?
>
> 3. The last issue is regarding the input file extension. Is there any way
> that I can open the file without explicitly declaring or inputting the
file
> extension (only inputting the file name), such as "filename" only (without
> the extension, say ".csv"; but it still can open the file with ".csv"
> extension)? It should, however, also be able to open any other extensions
of
> input files by declaring it explicitly, say ".txt" (or any other than
> ".csv")? Could it be like in a hierarchical order, for instance, ".csv";
if
> the filename with that extension can't be found, then it searches for the
> next extension, say ".txt", etc.?
>
> Anyone has the answers?
>
> Thanks
> Danny

• Messages sorted by: [ date ] [ thread ]