Posts by Rich_z

    Re: Bad File Mode: Writing To Locked Binary .txt File


    Hi Mumphis,


    This is a follow up to your thread about flat file locking.


    However, to answer your question here, you do not need to open the file in Binary mode.


    A TXT file, whilst a Binary files (all files are binary when you think about it), is a special case where there should not be any binary data in it such as bitmaps etc.


    You need to use the Random clause as outlined in the examples already given as if you open the file for 'OUTPUT' it is deleted first. For 'APPEND', the data is added to the end of the file so that doesn't help.


    BINARY mode requires you to PUT or GET from specific byte positions so INPUT # and WRITE # won't work.


    The easiest way is to use a RANDOM access file that allows you to read or write anywhere in the file based upon a record number rather than a byte position. This makes handling things much easier in the programming.


    Regards


    Rich

    Re: Formulas To Fill In A Matrix


    Hi


    Personally I'd find it easier to build the matrix in VBA and then populate a spreadsheet with it.


    However...


    I'm certain that the guys out there who know more about actual Excel than I ever could will come up with an answer.


    If they don't, and you can't find the answer yourself, send me a pm and I'll come back to this thread and continue this discussion.


    Regards


    Rich

    Re: Searching Based On Contents Of Changeable Cell


    Ok BB,


    What you need to do is to parameterise your subroutine to either accept the string that you want to search on or get the string for itself.


    Try something like this:


    Code
    Sub Search(Search_String as String)
    
    
    Cells.Find(What:=Search_String, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    Application.CutCopyMode = False
    End Sub


    Or


    Code
    Sub Search()
    Dim Search_String as String
    
    
    Search_String = Range("MyCell_Address").Value
    Cells.Find(What:=Search_String, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    Application.CutCopyMode = False
    End Sub


    Regards


    Rich

    Re: Extracting A Value From A Row Of Data


    Quote from alia

    thanks Rich, i get what u've explained and have been working ard with the Ucase() and inStr() function for a while. But if its not too much of a trouble, would u mind showing me an example with the codes? =) just so that i get a betta idea of how the code goes..

    thanks again


    Heh,


    You beat me to it. I posted the reply and then went and found the example... ;) (Well after all you did say that you was familiar with at least one incarnation of VB)....

    Re: Extracting A Value From A Row Of Data


    Use Ucase() to give you an upper case version, then Instr(string,"EDGE") to find those strings with Edge in them.

    Use Instr rather than Left(...,4) to find the string as then this code can be used to find for example the word 'PIPE' in 'ROUND PIPES' or 'RND PIPES'

    As an example:



    This code looks for the word 'VALUE', if it's in the string then it lops off the string up to the first character after and then looks for the first space and uses the characters before that as the value required.

    Rich

    Regards

    Rich

    Re: Created Batchfile And Then Execute


    This is the code in the copy I have:



    Regards


    Rich

    Re: Created Batchfile And Then Execute


    Quote from mt_aripz

    Hi rich,
    it looks good...thank you soo much..for your great help..man
    just one more thing..left..the actual msg which lies on cell "D" , "E", and "F", is not printed out to the batch file..


    didnt know VBA programming cn be this interesting...:)


    Ok, this ones down to you now.


    Look in the code module where it outputs the batch file.


    You can see how it constructs the out_cmd string. You should be able to deduce from that how to add in the columns D E and F.


    Regards


    Rich

    Re: File Locking A Flat File Over The Network


    Hi,


    Another thought for you.


    I used to use MS Basic yonks ago. Our data was stored in Random access files rather than spreadsheets.


    We used to handle record locking like this:



    You would open your file in random access mode something like this:



    The first way would allow multi user access to the file. Using record locking in the functions above.


    Read_And_Lock reads record 1, if the locked field is true then it sleeps for a while, tries again and only exits after 10 tries or the record is not locked. If it's not locked then it immediately writes it back with a locked status. Once it's changed the record it writes it back with write_and_lock.


    If all the programs in the suite adhere to this scheme then it works quite well. This is logical locking as opposed to physical locking where the record is physically locked on disc. Large systems such as SAP use this type of locking.


    The second method physically locks the entire file (which would be more suitable for you) and throws an error which you can catch using an error trap and handle accordingly.


    Oh,


    And the other thing is the file size. Bet this beats even an empty spreadsheet - about a dozen bytes and it'll be quicker!


    Regards


    Rich

    Re: Created Batchfile And Then Execute


    What are you doing working on a Sunday ?


    Mind you, what am I doing working on Sunday ?????


    Here is your spreadsheet back.


    A couple of points.


    First always state 'OPTION EXPLICIT' at the top of each code module. This will help prevent some of the types of errors you have in your original sheet.


    For example you started using Source_Sheet and then changed to Source (or was that me ?)


    Secondly you don't need a seperate module for each subroutine.


    Regards


    Rich

    Re: Created Batchfile And Then Execute


    Quote from mt_aripz

    Really Appreciate ur help..man.


    Ok, We'll do a swap.


    You do my work and I'll do yours....(I've been working all week on a tech spec for Order BoM and Material BoM processing and I'm all BoM'd out!!!)


    You need to do something like this (I haven't run it through excel so expect some errors)



    Now, where do I send my Invoice.


    Btw, The spec has to be ready by 8am Monday Finnish time. :confused:


    Regards


    Rich

    Re: Data Entry Via Userform


    Hi Martin,


    I have a spreadsheet that does exactly what you are trying to acheive, however, even zipped up it is too big to post here.


    Unfortunately I do not have time at the moment to strip out all the uneccesary stuff but if you send me a mail from your work address to [email protected] I will gladly forward it onto you with an explanation of where to look.


    Regards


    Rich

    Re: Created Batchfile And Then Execute


    Have you got the code to run the batch yet ?


    You can do a simple FOR...NEXT (place these keywords in the VBA editor and with the cursor on them press F1). You can then use the RANGE statement to access the data in the cells you want to look at.


    Regards


    Rich

    Re: Alternative To Error Handling



    Don't assume that the error you are handling will be the only error to be hit.


    Secondly always turn off your error handler when you exit the function to which it appertains:


    [vba]
    Dim Time_Cell As String
    Dim Row As Integer
    '*
    On Error GoTo Up_Err
    Set Summary_Sheet = Worksheets("Summary")
    ...
    ...
    ...
    up_Exit:
    On Error GoTo 0
    Summary_Sheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Set Summary_Sheet = Nothing
    Exit Sub
    Up_Err:
    Select Case Err.Number
    Case 13
    MsgBox "Check Time Values - At least one is not a number", vbError, "Error"
    g_Error = True
    Resume up_Exit
    Case Else
    On Error GoTo 0
    Resume
    End Select
    End Sub
    [/vba]


    The select clause in this error handler determines which errors I want to handle, all others then being handled by the normal error screen.


    You must turn the error handler off (at up_exit) because if you encounter an error elsewhere in your code you will find that the Error trap is still active and you keep wondering why the hell you end up in that particular bit of code all the time.


    Regards


    Rich

    Re: Maximisating Patients Seen Per Day


    Well,

    Without any other information that will be a bit hard.

    Without taking into account things like late arrivals, no shows, and running over time the only way you could do it is to divide the total working time by the average visit time.

    But that isn't what you're asking for is it ?

    Have a look at queueing theory. That's where you'll find your answer. This page also has links to a paper entitled 'Waiting Times In Health Care', so that might give you some more pointers.

    Regards

    Rich

    Re: Data Entry Via Userform


    Val Returns the numeric value of a string up to the first non numeric value.


    Code
    Left(Mid(Control.Name,Len(Control.Name)-3,1)


    Hint, count the opening brackets, then the closing brackets.


    Quote


    Ok the issue is probably like I said. You were checking "EVERY" control on the userform.


    Whilst probably not the most efficient way, it should not of affected the out come. The code looks for a variable name starting with 'CHK' that has an underscore as the 3rd character from the right. Having found that it takes the rightmost 2 characters of the string and turns them into a number. (ie the VAL function.


    It then uses that number as the cell offset.


    I think the only problem stopping it running is the above.


    Bearing in mind I took 10 minutes out of another system far crazier than Excel and without running this through VBA to knock this up, it was a pretty good start.


    Regards


    Rich

    Re: Data Entry Via Userform


    Quote from MartinJC

    I've had a read over and over your post and I am struggling to make sense of it (no reflection on you I might add!!).


    Don't worry. I struggle to make sense of myself sometimes!:smile:


    First question


    The Control.Name is the Name property of the Control object. It's defined when you declare the variable 'Control' at the top of your subroutine as:


    Code
    Dim Control as Control


    Second question.


    The zeroes do need to go in because you are looking at the last 2 characters of the variable name to give you your offset. If you had only one, anthing under 10 would return 0, rather than the offset because the VAL() of a character is 0 unless that character is a numeric character.


    Essentially what you are doing is providing a pointer in the variable name to where you want the stuff stored, and then extracting and using that pointer in the code.


    Regards


    Rich