import data from 1 workbook to another and APPEND the data with out duplicates.

  • 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!!

  • If the file that needs to be opened is always the same file and it is always in the same folder, then I suggest that you hard code the file path and name in the macro instead of using a button. If the name will vary and/or the save location can vary, then it would be best to have the file picker dialogue pop up to select the file. Unless you have a specific reason for using a button to do this, the macro can perform the same action.

    After the file is open, I need to get the sheet name(S) from that file

    Please explain in detail what you mean by the above quote.

    data should most always be on the first sheet

    You have to decide if the data will always be in the first sheet or not other wise the macro will not be able to find the data. Even if it's not the first sheet, it can be anywhere as long as the sheet name is always the same and you know what that sheet name is.

    I then need to copy the whole sheet to sheet TWO of the example "ImportTest.xlsm"

    The "ImportTest.xlsm" file has only one sheet named "Voter Names". I believe that there should be two sheets, one where you will paste the data and the other to use as a comparison. Please clarify and attach a modified file with the 2 sheets.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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!

  • 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

    Are you saying that if there is data in more than one sheet, you want to compare the data in all the sheets with data?


    The importtest.xlsm workbook you posted has no data so it would be difficult to test a possible solution because there is no data to compare in the Voter Names sheet. Could you post a copy of this file that includes some data? The data should be representative of your actual data. Also, when comparing the data, do you want to compare the data in all the columns for each row or just the first and last names?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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)

  • Also, when comparing the data, do you want to compare the data in all the columns for each row or just the first and last names?

    Could you please answer this question?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try this macro. It assumes that the data in both sheets starts in row one with no headers.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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.

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


    see the updated code below.


  • 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.

  • Can you attach a copy of your revised workbook including all the macros you are using? It would help if all the columns are included (de-sensitized if necessary).

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!