Posts by awsmitty

    I think I have enough observations to intelligently address the issue I'm having. I am using a workbook that is used to route trucks to pick up donations for a homeless shelter. I wrote this code about 7 years ago and it is the first VBA project I wrote. The code is not written very well and needs to be re-written but it has been working for the last 7 years.

    What is happening is once the program has been used for 2-4 entries, the user clicks an icon on a custom ribbon and initiates a form to enter in a zip code the form freezes. Sometimes it will get past this first form but freezes on subsequent forms. The point here is when it is first opened it works for a little bit (2-4 entries) then this freezing starts. The CPU usage goes through the roof, 55% and the memory just keeps growing. One last thing, if I bypass the icon on the ribbon by selecting macro, select the initialization routine and select run, It seems to do much better but eventually will still freeze about after 6-8 usages. And then finally if I just run the code to almost its end, using "Stop" to stop the execution, then killing via reset, I can run the program endlessly. Only after the data that is accumulated during the data entry process is added to the workbook does this freezing problem begin to show up and then only after several entries have been completely entered.

    I am hoping that someone might read this and suggest where I might start to look to debug this problem. I have rewritten the code for some of the forms and other code. I have checked the formatting in the worksheets. I am at my wits-end. Any suggestions would be appreciated.

    Here is one approach. I am not saying it is the best approach.


    Have the code pass the sheet as you have it ByVal to a subroutine. I am doing it this way so that when the program finishes the sheet as you have it now will remain the same, without the additions that are to come below.

    Then,

    First, add three columns to the right of column "C". They would be titled "Account _Meter_Location", "Account_Meter", and "Account_Location".

    Second, place the 3 formulas "=A2&"-"&B2&"-"&C2", "=A2&"-"&B2", and "=A2&"-"C2" (omit the inside and outside quotes) in "D2", "E2" and "F2".

    Third, copy "D2:F2" (note the colon, not comma) and paste it in the remaining Range("D3:F(last row in "F")


    Column "D" can now be searched using Account-Meter-Location as the search value, and the dashes must be there. If found, there is your most desirable choice, else search "E", else "F" and if that returns empty search "A", your least desirable choice.


    There are several things that would be nice to know to really work the problem. How big is column "A"? For a given account number are they all grouped together as in your example, or can they, might they be scattered throughout the sheet? It might be desirable to sort the sheet before getting started. Lastly, you mentioned that your example is a composite of several sheets. Where are these other sheets; are they in the same book?

    Here is one approach. I am not saying it is the best approach.


    Have the code pass the sheet as you have it ByVal to a subroutine. I am doing it this way so that when the program finishes the sheet as you have it now will remain the same, without the additions that are to come below.

    Then,

    First, add three columns to the right of column "C". They would be titled "Account _Meter_Location", "Account_Meter", and "Account_Location".


    Second, place the 3 formulas "=A2&"-"&B2&"-"&C2", "=A2&"-"&B2", and "=A2&"-"C2" (omit the inside and outside quotes) in "D2", "E2" and "F2".

    Third, copy "D2:F2" (note the colon, not comma) and paste it in the remaining Range("D3:F(last row in "F")

    Yes, You definitely need to put Unlaod.Me after all the data is in the sheet. If the form is unloaded before the data has been transferred to the sheet, the data could possibly go away with the form. Basically, it is the last thing to execute once the userform has completed its job, the very last piece of code in the last control object that the user has used. As you have the code written right now that would be the Submit button. If, later on you add more buttons or other controls are added, and one of these new controls become that last control used, Unload.Me would go there as the last thing to execute.

    Paulbxx,


    So far as I could see after simply deleting row 11, yes, the program worked fine. Or at least the data I entered on the form was transferred to row 11. There is still work to do in my opinion. You need to unload the form and I saw the other cells were updated. You need to check and make sure those updates are what you want. But the question to your problem as asked, the data was added.

    Try


    I think the problem is you've got all the cells on row 11 merged. The way you have the program written it wants to add the new values to this row, but there is no column "C", "D", etc. I got the password from your setup sheet. I deleted row 11 and the program seems to work fine.


    Delete row 11 entirely and you've got it, I think


    The other solution would be to manually add some "fake" data to row 12, as you have the sheet right now. Once the first set of "real" data is added, ie, real data to row 13, delete out the Range("C12:O12") (the fake data, delete using shiftup option) so that the "fake data is deleted and the data on row 13 moves to row 12. After that all should work fine.


    Hope this helps.

    I don't know if this fixes the problem or not. I am not familiar with Enum and what it does.


    Online 38, change Application to ApplicInstance


    Then on line 78 change ExternalRef.Application to ExternalRef.ApplicInstance

    Then online 122 change oWhich = Application to oWhich = ApplicInstance



    Could somebody confirm if this is correct. Does the Enum statement beginning online 35 have to contain objects

    Code
    Public Enum ExternalRef
    Worksheet
    Workbook
    Application  <== This guy right here, Does this have to be an object for the program to fulfill its intended purpose?
    End Enum


    If someone could elaborate on this it would be appreciated.


    Thanks

    Re: Rename then move, or move then rename a file using wild cards


    Jindon, Excel Samurai (It makes me chuckle, but appropriate)


    Your code works perfectly. Very fast, faster than I can push F8 then go to the destination folder, way faster than making a copy.


    I know a few days ago I tried something like this, but I couldn't get it to work. I have most of my old code now as comments and have checked for typos but still haven't found my error. I have no idea what I was doing wrong.


    Many thanks. By the way, keep the title, Excel Samurai. I think it's great!


    awsmitty

    Re: Rename then move, or move then rename a file using wild cards


    I assume you mean examples of the file names


    Prudhoe Bay, 1988(a), J. Dalton.avi - date is incomplete


    Prudhoe Bay follow up, J.D.avi - no date - there are several follow ups


    Yates Oil Field, J.D..avi


    West Texas 101.344.wmv - no date, no author


    West Texas 101.345.wmv - no date, no author


    As you see, some have dates, which we need, some person writing the report usedinitials only, this I can correct (new file name) from another workbook of employees, some nothing but an identification number, which again I canretrieve the correct title from another worknbook. In other words, the person doing the archiving not only needs these other workbooks, but one spends sometime reading the archive before realizing that this isn't what was being sought. Where as if the title were a little more informative, one could find precisely the article being sought and omit reading the unnecessary.


    So, let's take the last example. I can cross reference this using another workbook that will provide a more informative title/filename using the 101.345. Hope that helps clarify the problem at hand. Almost all the new titles will in part come from other sources and will be generated via VBA. Also, I was going to use a msgbox showing the current title and the new title so I can verify that the change is correct, if not, hand type one in. I also intend to use a suffix of abbreviations which will be a shorthand summarizing the conclusions of the report. This too will be generated via VBA.


    Hope that helps

    Is there a way to rename then move, or move then rename a file using wild cards for the file name, or using only part of the file name.


    I know wild cads are risky, but I am certain that only the files in question will be altered.


    Many of these files are media of one kind or another, so the file extensions vary, .wav, .avi, .mp4 and the same for music files. In some cases there is no extension at all, but all of these are .lnk files or .url files, but it is not only the extension that requires the wild card. The file naming system of the past was willy nilly, no apparent rhyme or reason for the current file name. I would like to rename these files using a file naming system that would follow a standard has some significance to the file itself, other than just a name


    I have found several leads on the net, the best given here -
    http://www.ozgrid.com/forum/showthread.php?t=162768&p=746639#post746639


    given by [FONT="Verdana"]jindon,
    [/FONT]

    which uses


    Code
    Sub test() 
        Dim fn As String 
        Const myDir As String = "c:\testA\" '<- alter here
        Const newFolder As String = "c:\testB\" '<- alter here
        fn = Dir(myDir & "red_*.csv") 
        Do While fn <> "" 
            FileCopy myDir & fn, newFolder & Replace(fn, "red", "blue") 
            fn = Dir 
        Loop 
    End Sub


    This at least allows the use of wild cards. However, the code makes a copy first, which takes time for the larger files.


    Any help would be appreciated


    awsmitty

    Re: File copying &amp; renaming using VBA &amp; WildCards


    jindon, Excel Samurai (I love it)


    I hope you get this. I realize the post is over 3 years old but I see you’re still active.


    Your code needs to be highlighted! I have been searching for this for days now. I don't really need to copy a file, just move it, and in certain cases rename it. I have, ... or I should say had 3 problems, 1) I need to use wildcards, 2) some files (more then half) need to be renamed and 3) I would prefer to just move, not copy, because some of these files are movies and are large (> 1GB); to copy takes too long. However, the need for wildcards over rides the time requirement.


    I modified your code as follows:


    fn = Dir(myDir & Movie_Actor_Name1 & "*") ' Movie_Actor_Name1 & "*") is old name with wildcard
    FileCopy myDir & fn, newFolder & Replace(fn, Left(fn, InStr(fn, ".") - 1), Movie_Actor_Name2) ' Movie_Actor_Name2 is the new, correct name

    I then go back into the old folder and delete the old file. At this point it's the best thing I have going. Fortunately the first line of code, fn, identifies the precise file that needs deleting.


    If you know of a way to rename the file, old or current name with wildcards, to new name known in its entirety, it would really speed things up. I know using wildcards is risky, but I'm certain I can identify a unique file. We need something like FileMove, the counterpart to FileCopy, but it only exists, to my knowledge, when using fso.MoveFile which does not allow the use of wildcards. Any ideas would be greatly appreciated


    Thanks for your code


    awsmitty

    Re: Custom Msgbox response buttons


    Yes Jack, you have done it again


    Fished this thread up from the internet. I know this is an old thread. Some forums get upset when someone replies to an old thread, but I had to.


    I have never used userforms. From your example I completed my first, in like an hour. Really cool


    Thanks