Posts by mycomputerguy-w

    Royuk, or Mumps.... could either of you look into this. I've been racking my brain trying to find a solution and i haven't been successful. the if and statement will only compare the first argument.


    Thanks in advance guys!

    After moving this code into the main app, I did find a couple issues I'll start with this one simply because by solving this, it may very well solve the rest of the issues. The main app uses a table with headers and the import sub you created, adds everything below the actual table. I'd like this to append the data into that table preserving the headers but adding dynamically to the table, either at the bottom of top. because this table is sorted, once refreshed, it should all be back in alphabetical order.

    I actually got that sorted on my own Mumps..... Thanks for all the help.


    see the updated code below.


    ok.... couple things. first, this is doing exactly what i wanted it to do in regards to importing. HOWEVER, the sheet(s) that sent you are missing some data that will be in the original import files that are used. i purposely left this data and columns off of the sheets I sent due to its sensitive nature. let me see if i can explain this a little better. The file that is selected, needs to have columns A-C deleted (the whole column(s)), Column G Deleted and columns I-N deleted. this should leave only columns A-C and column E on the sample I sent you. Boy that really sounds confusing when I read it. I hope that makes sense to you.


    All of these removed columns in the original file as mentioned above, will have to be deleted before the compare is done. I didn't think about this until I was testing the import and it drug in ALL this redacted info. after the import is complete then i will be running a separate sub to clean and normalize the data and use text to columns to put it all into the correct format. which by the way, there WILL be headers (Column Names).


    hopefully this makes sense.

    ok... after reading it again, that is a little confusing. what i was thinking is to check the file that is sent to see if there are multiple sheets and if those multiple sheets ALL had data but lets leave that part out. ill train the users to prep the file before import. lets just assume that the import file will have a single sheet and that data WILL have duplicates. ill send new samples with data in each. if we could import the data from book2 to importtest, that is what ill need to accomplish. Ive loaded both files with data, some of the data is duplicated. (towards the bottom)

    Files

    • Book2.xlsx

      (11.59 kB, downloaded 28 times, last: )
    • importTest.xlsm

      (20.87 kB, downloaded 28 times, last: )

    ok.... here we go,


    first- the file name will most likely never be the same name or be in the same folder nor will the sheet names be the same. The data will be coming from the same place but it is never built or sent out by the same person every time so the chances of anything being the same are pretty slim. So to answer your question, yes, the file picker dialogue pop up is the best option that I can think of.


    2- explaining in detail, the quote 'After the file is open, I need to get the sheet name(S) from that file" I guess I really don't NEED the sheet name but my thought was that the sheet name or location of the sheet in the workbook could change breaking the procedure because, as explained above, the chances of the file names or the sheet names being the same is slim to none. So once the file being imported is open, I wanted to first check to see how many sheets there are, is there data in ALL sheets, if there is more than 1, and is that data the same or different in each sheet , if the data is ONLY in one sheet, even though there might be a single or multiple sheets, then, where ever the data is, compare the data in THAT sheet to the current data in the other workbook (which, right now is named importtest.xlsm and could change) . If ALL the data is the same in both places ( which it could be) then just quit the sub. If the data has different entries, than select JUST the different entries and append them to the bottom of the sheet "Voter Names" in importtest.xlsm.

    3rd- when I wrote this post, my idea was to copy all data , AFTER it was formatted with the TextToColumns call, into a second sheet on importtest.xlsm. I've since refined my thoughts and figured out, that would be an extra and unnecessary step in what is already turning out to be a larger procedure than originally anticipated. Comparing the data in the the 2 sheets would be the better option if possible.


    Let me take a minute and explain to you what this "App" that I'm building is for and what it is anticipated to do.


    This import procedure is one cog in a very much larger App. The idea here is to build an all in one solution for towns and cities under the jurisdiction of a larger county seat. each "Town" or "City" is responsible for its citizens on the voter roll. if that citizen decides that they want to vote by absentee ballot, there is a huge procedure that needs to be performed by the city or town officials all while complying with local ordinances. The app holds all the voter information. when people leave the area, come to the area, die, are born, become voter age and register, don't register and don't vote for a few years then decide they want to later.... these are all reasons that they could be removed or added to the data I'm trying to import. Those are the entries I'm looking for. The data is built by employees in the COUNTY government, and can be sent to the Towns or Cities at their request. their particular data will be specific to them and their township. This is why the button exists. Its on the settings page of the userforms that aren't included in this sample simply because the sample is getting too big to send so I have to break it apart and work on bits at a time. That button will be for them to update their "Database" of names. Because the data comes from the county seat, it most likely is going to change and that presents the problems I'm attempting to address here.


    I hope this helps you see my vision. As always, im open to suggestions and always welcome changes that will make the code and the app run better and be more efficient. Any questions, feel free to ask.


    Cheers!

    OK Gurus, I have another problem for you. I need a sub routine that will allow for a customer file to be opened from a command button (at the moment, I have it set up with the Application.GetOpenFilename method with now command button , just using the run command). If this is the most efficient way, than I will use that. After the file is open, I need to get the sheet name(S) from that file. (data should most always be on the first sheet. Fields and data will look like the example in book2). After getting the sheet names, I then need to copy the whole sheet to sheet TWO of the example "ImportTest.xlsm" . After getting it to sheet 2, the formatting takes off but I've got that working so unless you see a better way to do it, that doesn't need any help. Then I need to compare sheet2 with existing data on sheet1 to be sure there is no duplicates, delete any duplicates, copy THAT data and append it AT THE BOTTOM of the data on sheet one that will already be there. whew!! i think i explained it right. I'll start there and see what kind of replies I get. as always, Any questions, feel free to ask, ill do my best to answer them.



    Thanks for your great work!!

    Files

    • Book2.xlsx

      (11.14 kB, downloaded 29 times, last: )
    • importTest.xlsm

      (18.13 kB, downloaded 27 times, last: )

    If you aren't using VBA to do this, then I think you may be in the wrong forum. If what you are looking for is to have just a "Third Column" with the percentage result, that would be a question for the "Formulas" forum. the column would just be exactly that, another column. then the percentage formula would be added to the cell for calculation.


    I would at least try to post your question there and see anyone can help you with it.


    Have a good day! sorry i couldn't be more help!

    Thanks Roy... With your example, it got me on the right path. Its working perfect now.


    The old code:

    The fix? EVERYTHING had to be inside of the with statement EXCEPT the oWord.Quit False statement otherwise the oWord statement call would quit prematurely. I figured it had to be something simple. Correcting the call order was all that was needed. Thanks again for your help.


    The working code:

    Are you attempting to print more than 1? This doesn't work for me. If i select more than 1 in the listbox, it will only print the last selection in alphabetical order. If its working for you than what could possibly be the issue why it wont work for me? If i select 1, it prints 1, if I select 5, it prints 1, if i select 10, it prints 1... and the one it prints is ALWAYS the last in alphabetical order?? I'm extremely confused now.

    I'm a little confused as to How you want this to happen but to insert add a column to a piviot table, you'll need to include this in your code:


    Hopefully this will give you a start and maybe someone that is a little more familiar with piviot tables can help you refine it to your needs.