Posts by bnix

    Re: Checking dates and loop

    Just a little variation on laythss post

    Re: Extracting values from RecordSet

    You would have to use a third-party tool such as Redemption, but even with this it only places email messages in the Draft or Outbox folder (I forget which one). The security is there for a reason, especially in dealing with emails, are there ways to do this..probably..most of them will not be discussed in this forum. So, unfortunately you will have to manually click send, or use the shortcut keys ALT+S. If it is an older version of Outlook (I think 2000 or lower), .Send would send the message, anything higher you would get the security message.

    Re: Maximum recommended file size

    I agree with Jack about making sure you do backups. I currently work with a workbook that is around 14MB, and it does not cause any problems at the moment, but I always do a backup BEFORE I run the next report, so I will have something to go back to.

    If you forsee this growing very large, you may want to start thinking about moving it to a database application.

    Re: Maximum recommended file size

    Welcome to the forum.

    If you could tell us your current file size, and an idea of what is going on in the workbook (formulas, named ranges, pivot tables, etc.), it would help in advising on workbook size. Also, it will largely depend on available memory, paging size, and other variables as to how big you can let your workbook grow before it becomes a pain to recalculate formulas, opening it and other related items. A 20MB workbook would be no problem for me on my machine, but it would cause problems on an older machine possibly with less memory and lower processor.

    Here is a link that gives a little more detail;

    Re: Extracting values from RecordSet

    I would first remove this line as it is not needed

    1. ' other code
    2. Set myRecordSet.ActiveConnection = Cnn1
    3. ' rest of code

    You are already setting the connection in the .Open Method of the recordset you are already passing the ActiveConnection to Cnn1.

    I set up a quick table with your code, and I forgot to add the Days Unresolved field and got the same message as you state. Are you sure all the fields you are passing in your Query string are present, and if they are are you sure of the spelling of the field names?

    Re: Calculate the Sum of only visible data in rows not hidden

    You could try something along the lines of

    1. Dim c As Range
    2. Dim varSum As Variant
    3. For Each c In Range("A1:A100")
    4. If c.Rows.Hidden = False Then
    5. varSum = varSum + c.Value
    6. End If
    7. Next c

    You could add some additional checking in there, just depends on what end result you are looking for

    Re: Looking for Dyanamic Query to hit multiple tables in multiple DB.

    You could set up some code and load an array with your various UNC names and then loop through to run your queries or updates against the various databases. This is just a small snippet, but hopefully you get the idea

    Re: Extracting values from RecordSet

    If I understand you correctly, you would just need to set up a loop for your recordset;

    Re: Accessing arrays

    This probably does not help, but wanted to throw it out there anyway.

    You could also use

    1. Range("A1:H1") = Data

    This would fill the range with the data from the array, but as I said you are wanting to access certain elements other than the first so I guess I am just rambling and you should go with what Norie and Alastair suggested.

    Re: Compile Error when running macro

    When you get the error, you can view the missing reference the same way you add them;

    1. When you get the error, break on the error CTRL+BREAK
    2. This should take you into the VBE
    3. Click on Tools>References
    4. You should see "MISSING" preceding any reference that may be causing trouble.

    You need to do this on the offending machine, not yours. If yours is working correctly, you will not be able to determine anything.

    Re: Compile Error when running macro

    welcome to the forum.

    When you get the error, you should be able to go to Tools>References and whatever is giving the error you should see "MISSING"..... It could be something else is selected and it is not available on the other machine.

    Re: multiple update queries - vba code??

    It is largely going to debend on exactly what it is you are going to do. Will it always be the same table, always the same field or will you need to pass different values for the field names, will it always be a straight UPDATE...SET scenario, or will there be other conditions to be placed in the SQL statement?

    Should be able to work up a procedure do what you want, but would need additional information before moving forward.

    Re: Delete Column

    Is there some more code associated with this? You say you added this to existing code, did you add it within a loop structure? Maybe if you post the entire code we can decipher a little more.

    Re: Date Formula

    You can try the below formula, I think it should do what you want.


    Where A1 is the persons start date and B1 is the future date and enter the formual in C1.

    Re: Horizontal Filter

    I think you may want to look at a Pivot Table. This will give you more options as to what data to display.

    Re: counting words in column query

    Just to piggy back off of Wills' idea, for a quick snapshot you could also try;

    1. SELECT name_field, COUNT(name_field) AS CountOfName
    2. FROM mytable
    3. GROUP BY name_field
    4. ORDER BY COUNT(name_field) ASC

    This would give you two columns with each name and a count of how many times that name appears in the record.

    As will said though, if the same name is entered differently it would give a duplicate result.