Posts by TheGlovner

    Re: open an excel file and insert a row with data on it then close it back


    Provided that when you say dBase File you actually mean the Excel Binary File (as opposed to something like an actual Access Database) then yes.


    It's the full code that I created sometime back for standardising the opening of workbooks. So on my system it sits in a separate excel add-in (.xlam) that can be referenced from any code that I write so I don't have to bother coding the opening of sheets every time.


    So the only mandatory variable that needs passed into it is the full file path, all the others are optional around the type of access you need.


    Depending on what's passed into it, it will attempt to open the file, check that access it has against what you requested and either fire an error message confirming that it can't meet your requested access requirements just now (you need write access and someone is locked to it limiting you to read access for the moment being the main issue), or the file is opened as requested.


    The last variable/object allows you to pass an actual workbook object as well, if this is supplied then it will set that object for you before passing it back to your calling code, at which point you can refer to the newly opened workbook as that object, rather than having to mess about with the full file path.


    To be honest I'd probably tidy things up a bit if I did it again now, but it should still be fit for purpose.


    The idea for you is not to worry too much about it, but just drop it in a new module in your project, call it from your code and give it the appropriate variables and objects. It should hopefully just work without you having to worry too much about what is going on, although I accept you don't know me and probably want to be sure I'm not stealing your bank details or some other nefarious scheme.

    Re: Hide entire row based on value of one cell


    I'd guess you want to fire the code on request rather than happening in realtime, so for that you'd need some sort of interface for the user (ActiveX button probably, or possibly checkbox, when ticked filter the sheet, when unticked display all).


    That aside, shouldn't be too difficult to achieve.


    A pretty simple piece of code would be something like:




    Worth bearing in mind though that's only going to hide the rows. And requires some code before hand to get the appropriate sheet in order for this to be passed to the HideDRows routine.


    If you want the rows displayed again then you'd need some way of tracking and toggling the setting, or having two buttons, one to perform the hiding and another just to show all rows again (although that can be done pretty easily from the sheet as well).


    Hope that helps and at least gets your started.

    Re: Computing Index/Match using data from another worksheet


    I'd guess your compile error is connected to the LastRow step.


    Having the period in front of the Cells property suggests that you've defined the worksheet beforehand, i.e.


    Code
    with Worksheets(3)
           LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    end with


    So given the other two are I believe referencing the same worksheet I'd probably go with:


    Code
    with Worksheets(3)
           RR = .Range("a:a") 
           VR = .Range("am:am")
           LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    end with

    Re: Simpying Repetitive Macro Codes


    Oh my. That seems inefficient.



    Only the top part is needed for each checkbox if the rest of the behaviour is generic

    Re: Codes run slower


    All your steps are interacting directly with the sheet which depending on the size of the ranges you are dealing with it can get pretty sluggish.


    I've got no idea from looking at your code what it is you're actually trying to do but if you want to improve efficiency my first suggestion would be to split your process into three parts.


    1. Extract Input Data from the spreadsheet into memory (objects & variables)
    2. Perform the required processes to the data to create the output in memory
    3. Package the output up and return the output data to the worksheet


    It can take a little bit of getting used to as you can't see the things happening on the sheet in real-time, but it's a lot faster at processing this way. Just means you need to make a lot more use of the Immediate and Watch windows in the VBA Win

    Re: Deleting Groups of Like Rows where a no negative value exists in a specific colum


    Quote from nilem;798791

    Hi DGWin,
    try it


    Pretty similar solution although admittedly more compact.


    I unfortunately have the company standards embedded into everything I produce now. Hence the far larger bit of code doing essentially the same thing.

    Re: Deleting Groups of Like Rows where a no negative value exists in a specific colum


    Okay give this one a bash, hopefully I've understood your requirements correctly now.



    Same as before, change the sheet reference name accordingly for your workbook.


    Couple of things to note. This is still interacting with the sheet directly. If it's not a large file then it's probably not an issue, but if it is a large dataset you are dealing with the performance would be greatly improved by taking the data off the sheet completely, evaluating it in memory and then returning the results to the sheet, that way you only need to interact directly with the sheet twice.


    Secondly, this method relies on the data being ordered by the items, if your items are not all grouped together then it would treat the two lots of the same item as two distinct groups and evaluate them individually (again this is something that could be addressed by taking the data into memory first.


    This could all be done quite easily but it is extra work and only really needed if your data isn't grouped or it's a large dataset and you want to improve efficiency.

    Re: open an excel file and insert a row with data on it then close it back


    This may help you (although maybe a lengthy solution). It's all the code for an add-in I had written a while back to handle opening other workbooks where different access may be required (saved me rewriting the same thing every time I needed a bespoke solution).



    So instead of the line:


    Code
    Application.Workbooks.Open "\\ZXG-FXVR-01\Compliance_NCC$\zXML_Validation\PEP_DOCUMENTATION\DataBase.xlsb", UpdateLinks:=0


    In your code, you would replace it with:


    Code
    call A_ExternalWorkbookAccess(strFullPath:="\\ZXG-FXVR-01\Compliance_NCC$\zXML_Validation\PEP_DOCUMENTATION\DataBase.xlsb", _
                                                  blnWriteAccessRequired:= TRUE, blnReadOnly:=FALSE)


    Alternatively you can make the workbook shared, not sure what issues that may cause with users overwriting each other's data though.

    Re: Late Binding an Object based on its Type after being set


    I had a number of other classes which all contain the same properties/methods but what actually went on in them was slightly different.


    So I wanted to go with late binding on the code outside the classes so it was object agnostic.


    Hopefully that makes sense.


    Got the whole thing working now though which means we now have a testing tool that in theory can test any series of calculations in the cobol systems without having to build a bespoke spreadsheet each time, just need to alter the queue of generic calculations to match the larger equations and the appropriate order of operations.


    I may be putting myself out a job, not sure I want to give it to management now.

    Re: Deleting Groups of Like Rows where a no negative value exists in a specific colum


    This should do it:



    You didn't explicitly say how you want to handle zeros (since these are strictly speaking not negative or positive), in the above code they are handled as positive values and deleted, however if you want to treat them as negatives just remove the "=" from the condition.

    Don't know if this is possible but here goes.


    But essentially what I'm trying to do is create an application for my area which allows them to queue up various different calculation components to make a larger calculation for testing various systems.


    So I've got a master class that handles the ordering of various other calculation specific classes.


    The user can input the component calculations in an input sheet and this is then picked up by the code and used to create the appropriate calculation classes in order and add these to a collection in the master calculation queue class.


    Since I'm working in the earlier routines with a generic idea of the classes then I've opted for late binding as the object could be one of many classes, they all share some common routines so this is fine at the earlier stages.


    However, when I get into specific routines for those classes I use a select case based on the TypeName(LateBoundObject) to dictate which call should be made.


    On making the appropriate call the object is passed to a specific routine for that type of object.


    At this point I was wondering if it's possible to dictate that object type as it's appropriate class, rather than continuing to reference it as an object.


    I don't think set objCalcClass = CreateObject(GenericSum) works as this creates a new version of the object, it's already been set at an earlier stage due to it's order in the collection.


    Example of a section of the code:


    Re: Run an Excel Macro From Powerpoint


    [QUOTE=Keifffer;791089]TheGlovner: Wow, what a really clever idea! I tried that, but trying to add a reference (in PPT VBA Editor, to refer to the Excel workbook) I get an error "Can't add a reference to the specified file". I tried saving the workbook as an addin (xlam), with the same "Can't add a reference to the specified file".
    QUOTE]


    Yeah, I tried it myself and encountered the same issue. My thinking is because to open an excel workbook (add-in or otherwise) you require an instance of the excel application. When you reference an excel workbook from another excel workbook it will open the "parent" workbook and then open it's references in the same instance of the application.


    So when you open a powerpoint file in an instance of the powerpoint application, you could only reference other powerpoint files in the VBA (as this would open them inside that instance of the powerpoint application), it won't let you reference an excel file directly as that file can't be opened in that instance of the powerpoint application (because it's not a powerpoint file, it's an excel file, so it needs to open it in an instance of an excel application).


    I hope that makes sense.


    To answer your other point though about having to do this on every PC. You wouldn't need to do that as the referencing is part of that file. So when that file is opened on another PC (provided it's by the same link rather than an attachment on an email) the reference still applies.


    Doesn't help you here but may be useful for future excel developments.

    Re: Calculate button


    You're not far off, two ways you could tackle things, I'm more inclined to have a button be used to call a larger section of code rather than embedding the code within the button click event so I'll show you that:



    Your issue was around the fact you were trying to embed the functional subroutine inside the button click event routine, rather than calling it from the button click routine.

    Re: Run an Excel Macro From Powerpoint


    Never had to do this, but off the top of my head before I find the time to and recreate and solve the problem can you possibly make the excel file a referenced add-in through the powerpoint VBA? Should make communication and rights easier to handle between the two then.