Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Bad File Name Or Number

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi guys & gals

    I have written some code for my worksheet but I keep getting the above error message. I have tried using Help to see if I can get it sorted out but I am not getting anywhere. Most of the code I found in a visual basic manual and I changed it around to suit my purposes but since I have limited knowledge of VB, I find myself at a loss. Would anyone care to have a look at the code and probably point me in the right direction????


    Drop me a line:- Randycas@yahoo.com

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think you could have better results if you posted the not working code and tell us where exactly is it showing the error.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I posted all the code since the line that is giving the problem is

    ""#gFileNum, gCurrentRecord, gPerson""

    This goes throughout the coding


    Dim w As Workbook
    Dim gPerson As PolicyInfo
    Dim gFileNum As Integer
    Dim gRecordLen As Long
    Dim gCurrentRecord As Long
    Dim gLastRecord As Long


    Public Sub SaveCurrentRecord()
    'Fill gPerson with the currently displayed data
    gPerson.Polno = Polno.Text
    gPerson.Refno = Refno.Text
    gPerson.Action = Action.Text
    gPerson.Comments = Comments.Text

    'Save gPerson to the current record
    Put #gFileNum, gCurrentRecord, gPerson

    End Sub
    Public Sub ShowCurrentRecord()
    'Fill gPerson with data of current record
    Get #gFileNum, gCurrentRecord, gPerson

    'Display gPerson
    Polno.Text = Trim(gPerson.Polno)
    Refno.Text = Trim(gPerson.Refno)
    Action.Text = Trim(gPerson.Action)
    Comments.Text = Trim(gPerson.Comments)

    'Display the current record number in the caption of the form

    Cancellations.Caption = "Record " + _
    Str(gCurrentRecord) + "/" + _
    Str(gLastRecord)

    End Sub
    Private Sub Form_Load()
    'Calculate the length of the record
    gRecordLen = Len(gPerson)
    'Get the next available file number
    gFileNum = FreeFile

    'Open the file for random access. If the file
    'does not exist then it is created.

    Open "CANCELLATIONS" For Random As gFileNum Len = gRecordLen

    'Update gCurrentRecord.
    gCurrentRecord = 1

    'Find what is the last record number of the last record

    gLastRecord = FileLen("CANCELLATIONS.XLS") / gRecordLen

    'If the file was just created, the update gLastRecord to 1

    If gLastRecord = 0 Then
    gLastRecord = 1
    End If

    'Display Current Record
    Show CurrentRecord

    End Sub

    Private Sub Action_Enter()
    ActiveControl.DropDown

    End Sub

    Private Sub cmdExit_Click()
    'Save the current record
    SaveCurrentRecord
    'Close the File
    Close #gFileNum
    For Each w In Application.Workbooks
    w.Save
    Next w
    Application.Quit

    End

    End Sub

    Private Sub cmdNew_Click()
    'Save the current record
    SaveCurrentRecord
    'Add a new blank record
    gLastRecord = gLastRecord + 1
    gPerson.Polno = ""
    gPerson.Refno = ""
    gPerson.Action = ""
    gPerson.Comments = ""


    'Update gCurrentRecord
    gCurrentRecord = gLastRecord

    'Display the record that was just created.
    ShowCurrentRecord

    'Give the focus to the txtName field.
    Polno.SetFocus

    End Sub
    Private Sub cmdNext_Click()
    'If the current record is the last record,
    'beep and display an error message. Otherwise,
    'save the current record and skip to the
    'next record.

    If gCurrentRecord = gLastRecord Then
    Beep
    MsgBox "End of File!", vbExclamation
    Else
    SaveCurrentRecord
    gCurrentRecord = gCurrentRecord + 1
    ShowCurrentRecord
    End If
    'Give focus to the polno field
    Polno.SetFocus

    End Sub
    Private Sub cmdPrevious_Click()
    'If the current record is the first
    'beep and display an error message. Otherwise
    'save the current record and go the the previous record
    If gCurrentRecord = 1 Then
    Beep
    MsgBox "Beginning of File!", vbExclamation
    Else
    SaveCurrentRecord
    gCurrentRecord = gCurrentRecord - 1
    ShowCurrentRecord
    End If
    Polno.SetFocus

    End Sub


    Private Sub cmdSearch_Click()
    Dim NameToSearch As String
    Dim Found As Integer
    Dim RecNum As Long
    Dim TmpPerson As PolicyInfo

    'Get the name to search from the user
    NameToSearch = InputBox("Search for:", "Search")

    'If the user did not enter a name, exit from this procedure
    If NameToSearch = "" Then
    'Give the focus to the polno.
    Polno.SetFocus
    'Exit this porcedure.
    Exit Sub
    End If

    'Convert the name to be searched to upper case.
    NameToSearch = UCase(NameToSearch)

    'Initialize the Found flag to False
    Found = False

    'Search for the name the user entered.

    For RecNum = 1 To gLastRecord
    Get #gFileNum, RecNum, TmpPerson
    If NameToSearch = UCase(Trim(TmpPerson.Refno)) Then
    Found = True
    Exit For
    End If
    Next

    'If the name was found, display the record of the found name

    If Found = True Then
    SaveCurrentRecord
    gCurrentRecord = RecNum
    ShowCurrentRecord
    Else
    MsgBox NameToSearch + " not found!"
    End If
    'Give the focus to the Polno
    Polno.SetFocus

    End Sub

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Slimpickens

    how have you configured the code to run ?
    and is this the complete code.

    the reason i ask is that this is obviously
    VB code and is designed to run similar to
    a VBA Userform eg.

    Private Sub Form_load()

    VBA eq Private Sub UserForm_Initialize()

    Running the code via a userform ini would
    set up your defined Freefile variable.

    But I think you are also missing some Data Type declarations as you have;
    Dim gPerson As PolicyInfo but have no
    declarations for this

    would suggest that there should be something
    along these lines....

    Private Type PolicyInfo
    Polno As String
    Refno As String
    Action As String
    Comments As String
    End Type


    Post for further help and how you have configured this to run.

    Ivan

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the help Ivan,

    I made declarations for PolicyInfo , I have them in a module, do I need to move them.

    I am not quite sure what you mean by "configuring the code" since I copied the code from a book, just making some adjustments. I really have absolutely no idea what is going on when coding starts to get really technical but I am trying to learn. Though it is hard. I have been trying to find a manual which deals directly with excel and Vb, but the bookstores here do not have any.

    I will try the code for the Intialize event and see what happens, while I wait to locate a manual.

    Carol


  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 08:57, slimpickens wrote:
    Thanks for the help Ivan,

    I made declarations for PolicyInfo , I have them in a module, do I need to move them.

    I am not quite sure what you mean by "configuring the code" since I copied the code from a book, just making some adjustments. I really have absolutely no idea what is going on when coding starts to get really technical but I am trying to learn. Though it is hard. I have been trying to find a manual which deals directly with excel and Vb, but the bookstores here do not have any.

    I will try the code for the Intialize event and see what happens, while I wait to locate a manual.


    Hi Carol
    Have you used this code for a Userform ?
    Thats what the code was designed for.

    When I said configure I meant adjusted.
    As I said before this code was meant for
    a VB Form and there are slight diff in VB
    and VBA syntax.

    The Type declarations should reside in the
    same Module / userform as the code you gave
    above.

    Ivan

    Post if you require assistance

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I will try that and get back to you Ivan. Sorry I took so long to answer, but we were having some problems with the Internet here at work.

    Carol

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •