Make my Visual Basic work

ElJeffe

I'm teaching myself a little vB, specifically as regards coding Excel macros (and more specifically still, Excel 2004 on OS X). I have a little function here that I can't make work:

Function OpenFile(FileName As String, FileIndex As Integer) As Integer
    Open FileName For Append As #FileIndex
    'Check to see if file already exists - if it does, ask if user wants to overwrite it
    If Seek(FileIndex) <> 1 Then
        If MsgBox("This file already exists.  Overwrite?", vbYesNo) = vbNo Then
            MsgBox ("Operation Aborted")
            Close FileName
            OpenFile = 0
        Else
            OpenFile = FileIndex
        End If
    Else
        OpenFile = FileIndex
    End If
End Function

The idea is to pass the function a filename and file index number. If the file doesn't exist, it creates it and readies it for input as #FileIndex. If the file does exist, it asks you if you'd like to overwrite it: if yes, it creates and readies it; if no, it closes the file and exits. The function returns either the file index number or 0 (the latter if the user opts to abort rather than overwrite an existing file).

Problem is, I can't get the function to ever recognize that a file exists by any manner I've tried. I originally tried LOF and FileLen, assuming that a file size of 0 would designate an empty file. Except that method always asserted that the file was 0 bytes, even if it clearly wasn't. The seek method used above always returns a current location of 1, even when the file is lengthy.

So how do I make my function do what I want it to do?

ecco the dolphin

I'm unsure of the exact behaviour relating to Visual Basic for the Office suites, but in most platforms I've encountered, the existence of a file is independent of its length (0 byte files also exist).

Typically there's another way of finding out if a file exists - apparently you might be able to use the Dir() function?

http://www.dailydoseofexcel.com/archives/2004/04/15/the-dir-function/

ElJeffe

That dir function looks a better route than what I was doing. Still, any idea why FileLen and LOF would both give the size of any file as zero bytes? I'm assuming that a text file with text in it is going to be longer than 0 bytes, right?

edit: I tried the dir method, and it worked great. The resulting code, for anyone bored enough to be interested:

Function OpenFile(FileName As String, FileIndex As Integer) As Integer
    'Check to see if filename already exists - if it does, ask if user wants to overwrite it
    If Len(Dir(FileName)) <> 0 Then
        If MsgBox("This file already exists.  Overwrite?", vbYesNo) = vbNo Then
            MsgBox ("Operation Aborted")
            OpenFile = 0
        Else
            Open FileName For Output As #FileIndex
            OpenFile = FileIndex
        End If
    Else
        Open FileName For Output As #FileIndex
        OpenFile = FileIndex
    End If
End Function

ecco the dolphin

ElJeffe wrote: »

That dir function looks a better route than what I was doing. Still, any idea why FileLen and LOF would both give the size of any file as zero bytes? I'm assuming that a text file with text in it is going to be longer than 0 bytes, right?

I'm afraid I have no clue why FileLen or LOF do not work as you'd expect. My Excel/Visual Basic-fu is weak. One thing I thought of was that you accidentally erased the file to 0 bytes when you opened it (I think this happens when you open a file for "Output"), but I checked and you did open the file for "Append" in your initial code.