Posts by Rich_z

    Re: Data Entry Via Userform


    Hi,


    You can loop round the controls on your form using a construct such as:


    Code
    For Each Control In Characteristic_Entry.Controls


    Where in this case Characteristic_Entry is the name of the form and Control is declared as a Control.


    You can then identify which controls you are interested in by for example giving them each a root name followed by an extention to differentiate them (it's a shame you can't have control arrays).


    So in your code you would do something like this:


    Code
    For Each Control In Characteristic_Entry.Controls
                      '*
                      '* See if this name exists in the values from the spread sheet.
                      '*
                      If Left(Control.Name, 4) <> "Titl" And Mid(Control.Name, 5, 1) = "0" Then


    So here, I am looking at the control name and only processing it if does not start with the string 'Titl' and has a zero for the 5th character.


    You might name your controls something like Chksafeacts_05 and chkreactions_06.


    Looking for the first three characters of the control being 'chk' (translate them all to upper case in the code if you want) and the 3rd from last character being '_', if you take the value at the end of the name, this gives you the offset into your spreadsheet to place the values.


    You could also build a look up table in the code to do the same thing but this is easier and when a new check box is added to the form no code changes need to take place for the existing code to handle it.


    Your code would then look something like this:

    Code
    For Each Control In Characteristic_Entry.Controls
                      '*
                      If Ucase(Left(Control.Name, 3)) = "CHK" And Left(Mid(Control.Name,Len(Control.Name)-3,1) = "_" Then
                         NextRow.Offset(0, Val(Right(Control.Name,2))).Value = "P"
                      End If
                  Next Control



    Regards


    Rich

    Re: Created Batchfile And Then Execute



    You need to insert the relevant executable commands so that it picks up the data from your worksheet and outputs the executable commands to the batch file and then include the shell command to executue it,


    If you point a command button to the macro and click it then yes it will run this code.


    Quote

    to print all the data on that spreadsheet to a batchfile and execute it.


    You can't execute data.


    Regards


    Rich

    Re: Importing Specific Csv Data Into Different Tabs


    Re: Created Batchfile And Then Execute



    Hi mt_aripz,


    Open a file called BATCHFILE.BAT using the OPEN statement:


    Code
    Dim Outfile as Integer
    Dim Out_Cmd as String
    Outfile = FreeFile()
    Open "batchfile.bat" for output access write as #Outfile
    Print #Outfile,"@ECHO OFF"
    Print #Outfile,"d:"


    Loop round your selections and construct the command line you need in Out_Cmd, outputting this to the batch file using the Print # statement.


    Close the file:


    Code
    Close #Outfile


    The execute the batch file using the shell statement. You may need to run the batch file as an argument to cmd.exe.


    Regards


    Rich

    Re: Importing Specific Csv Data Into Different Tabs


    Quote from error#9

    I'm liking the efficienct logic here but i don't know how many different currency there are, this is why i dont thing the CASE method will work.


    The number of currencies doesn't matter. The case statement is looking at the position in your input line for the field value it is currently working on. (oh, and you must clear these just before the INPUT LINE statement.


    The code automatically copes with the different number of currencies by creating a worksheet for them, with the currency code as the name of the worksheet.


    If the worksheet doesn't exist then it is added to the work book.


    Another thing to do is set a flag and if this is the first record, delete any work sheets that already exist. You have to do this after you have added a new worksheet otherwise Excel complains. Name this worksheet something unique and delete all the worksheets that are not called 'hhgtya' for example then rename 'hhgtya' to the correct currency. Once that's been done, clear the flag so that you don't do the delete again this run.


    Regards


    Rich

    Re: Consolidating Column Entries



    Place the cursor on one of the CELLS keywords and press the F1 key.


    That explains the addressing notation you are looking at. Then find all the ",1"'s and change them to ",4"'s as a starting point.


    Regards


    Rich

    Re: Open 1000 Files And Analyze Data


    Hi Quickoats.


    Firstly, this type of question has been answer many times before. Always try and use the Search first before posting.


    Have a look at this post which will tell you how to loop round a directory containing your files and then import them.


    Regards


    Rich

    Re: Importing Specific Csv Data Into Different Tabs


    Quote from error#9


    New sheets if the Currency has been found already?
    on the next available row if already exists?


    Isn't 'found already' and 'if already exists' the same thing ??


    I'm not going to code it for you but you do something like this (and plug this into VBA, pressing F1 on the blue keywords.... I did say capitalised, but the code formatter undid all my hard work!)



    Hope this gives you a few ideas.


    regards


    Rich

    Re: Characters In Cell To Be Re-arranged



    Hi John,


    The problem with that is maintainability. If some one else has to modify the code, or you come back to the code in 6 months time, it can be quite difficult to understand what you have done where.


    Using structures, you get almost self documenting code, it's quite obvious what you are doing (Moving some fields to fields of a structure and then back to a string).


    This of course would all be wrapped up in a function module for modularisation and information hiding as any other routines using this do not really need to know how this works

    Re: Charectors In Cell To Be Re-arranged


    Quote from stevekirk

    hi


    i have attached a sample sheet as it is and how it needs to be.
    the information is is a single cell. could you please advise me what macro would do this .


    thank you
    steve


    Hi Steve,


    There isn't an existing macro to do it. You could take a deep breath and dive into VBA. The help files are very extensive.


    Regards


    Rich

    Re: Share Workbook While Openning It



    As long as only one person accesses this at a time you will be all right.


    Have a search in the forum for threads that contain the terms file sharing, multi user etc because if you intend to allow simultaneous access to the work book, you're heading for trouble.


    Regards


    Rich

    Re: Charectors In Cell To Be Re-arranged



    Create a user defined type, such as:


    Code
    Type Change_Stack                                      '* Change stack
         Changing            As Boolean                    '* Previous changing state
         Update              As Boolean                    '* Previous ScreenUpdating State
    End Type
    '*
    Type Data_Entry                                        '* Data entry details for all sheets
         Previous_Address    As String                     '* Previous Cell visited
         Previous_ForeColour As Integer                    '* Previous font colour
         Previous_FillColour As Integer                    '* Previous fill colour
    End Type


    You then use this like so:


    Code
    Dim Change_Stack(10)           As Change_Stack                   '* For nested calls
    ....
    ...
    Sub Change_Sheet()
        Change_Stack(StackPtr).Changing = g_Changing
        Change_Stack(StackPtr).Update = Application.ScreenUpdating
        StackPtr = StackPtr + 1


    And the other way round:


    Code
    Sub Lock_Sheet()
        StackPtr = StackPtr - 1
        g_Changing = Change_Stack(StackPtr).Changing
        Application.ScreenUpdating = Change_Stack(StackPtr).Update
        If g_Changing = False Then


    In your case, break your string into it's component parts.


    You don't say what it is, but lets assume it's a material code that determines the warehouse, bin number and what ever.


    You would then declare a type:


    Code
    Type Mat_Num
           Material as string * 4
           S_String as String * 1
          Other_Stuff as String *10
    End Type


    Then move the parts of the string to the relevant bits of a declared variable, and then move that back to a single string.


    Regards


    Rich

    Re: Sharing One Workbook With Multiusers



    Hi mbluethunder700


    I think you have a slight misunderstanding of file sharing and allowing a file to be used by several users at once.


    When you share a file, it's placed in a common directory on the server or perhaps a shared directory on a networked pc.


    This allows multiple users to access the file, but only one at a time. The entire file is locked for the duration of the access by a user preventing other users from changing the file.


    In a multi-user system access to a file is granted to many users at once, but if a user is changing a record in that file, other users are prevented from changing that one record until the record is released. So the granularity of the locking in a multi-user system is much finer. It is at record level rather than file level.


    Excel does not have a record style file structure as such so the only way you can prevent conflicting changes from happening is to use file level locking.


    What you are after is a database. And that is what Excel isn't.


    You could do they same thing in Access and if you really wanted use Excel as a front end to it. I would be tempted to do it directly in Access.


    If you've more than a few users using this then dump access and use an industrial strength database such as Oracle or Microsoft SQL.


    Unfortunately the thousands of companies trying to do this need to get someone in their IT department who knows what he's doing or has the guts to tell his boss he is talking daft words, and you'll find that multi-user access to a spreadsheet cannot happen. (And file sharing isn't multi-user access).


    Why try and bend Excel into something it most definately isn't ?


    Regards


    Rich

    Re: Finding Next 1 In A List Of 0 And 1



    Hi Alan,


    What I'm trying to say is don't always get stuck on looking at data in the way it is presented to you at first.


    It may be quicker to write a program to extract the 0's and 1's from the spreadsheet, put them into a string and then call the subroutines I've written rather than trying to do it directly from the spreadsheet.


    Whatever you do, don't get it fixed in your head that you must use the data as is. If a bit of re-arranging (not creative mind you!) helps, and it's going to be faster in the long run, go for it!


    Regards


    Rich

    Re: Sharing One Workbook With Multiusers


    Listen to those two mbluethunder700. I hope you're not trying to make the workbook available to those different users at the same time ??


    Excel is not a multi user system and was never designed to be one. You're working on something that will never work properly.

    Re: Error Opening File In Macro


    You say that this stops working when you have uploaded the spreadsheet to a server.


    The first and most likely question to next ask is when you run the spreadsheet from the server, where is it run, (ie on your machine or the server).


    It looks like it may be running on the server in which case the fully qualified file name you are giving it will be the C: drive on the server.


    Try changing your file name to it's network version by placing the machine name in front of the path, substituting the drive name. For example \\localhost\my_dir\my_file.txt. Doing that will uniquely identify the file from anywhere on the network.


    Another good thing to check with servers and not finding files is access rights.


    Regards


    Rich

    Re: Finding Next 1 In A List Of 0 And 1


    Hi A9192Shark

    Does this do what you want ?


    This takes about 2 seconds for one string, including the output.


    Regards


    Rich

    Re: Finding Next 1 In A List Of 0 And 1



    Ouch.


    I would turn the data around so that you have 12 rows rather than 48000, read it directly from the data file (if you can) and try using the string operations such as Instr which can take a start point in the string to start searching from.


    Just output the results into Excel.


    If it's not a state secret, what are you doing ??


    regards


    Rich

    Re: Workbook Freezes on Opening


    PTG258,


    I'll agree with Jim. It's very hard to offer suggestions if the problem may be to do with coding when you haven't had sight of the code.


    You're not being asked to post the entire project. Paste the relevant procedures. The majority of code being written today does not contain any state secrets, some one, somewhere else has already written it.


    If the code accesses proprietary stuff, it's generally through a procedure call so that won't be giving anything away either.


    Regards


    Rich

    Re: Specify the Appropriate Worksheet Code


    Quote from getty7

    I seriously think I must be in need of a mental examination. This just makes no sense.


    You'll find that this happens to all of us. It's called 'Can't see the wood for the trees'. You get so immersed in your code you can't see a blatent error. You're collegue comes over and says:


    'Hey... you've forgotten the all important full stops'


    And you kick yourself.


    What I do when I get to that point is get up and go for a walk. Do something totally different for 15 minutes. And then come back to the problem.


    Regards


    Rich