Announcement

Collapse
No announcement yet.

$70 USD: Import (copy) data from another workbook into Excel Table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • $70 USD: Import (copy) data from another workbook into Excel Table



    Ozgrid Pre-payment has been sent.

    I have a weekly report (see attached file - Weekly Report_Import Data Sample.xlsm) that I generate that is based off of data that is exported to excel every week (see attached file - Snapshot Export Sample.xlsx). The data from the Snapshot file is manually copied and pasted into the Excel Table (Table1) located on the Snapshot tab of the Weekly Report. And I also have to add rows (and re-format new rows) to the table every week to accommodate the extra rows before pasting into the table. I was looking for a macro to to accomplish the following steps to streamline the process:
    1. Add a macro button to the Data tab of the Weekly Report_Import Data Sample file that will pull up a browser window so a Snapshot Export Sample.xlsx file can be selected and then imported or copied to the Table1 located on the Snapshot tab.
    2. The entire data set from the Snapshot Export tab beginning with cell A2 down the last row and through Column CH will be copied. The beginning row (2) is static and the column range A:CH is also static. But the number of rows will change (mostly increase week over week). The data should be pasted as values into cell B11 which is in the 2nd row of Table1.
    3. Is there a way that the macro could count the number of rows in the Snapshot Export source file (less the Header row) and compare that that to the amount of rows currently available in Table1? For example, in the attached Snapshot Export file contains 1,973 rows of data below Header. The attached Weekly Report_Import Data file has 1,968 rows of data below the Table Header row (and I always leave an additional blank row at the end of the table above the Total Row). Could a macro count the difference of the the amount of rows that need to be added (or subtracted) (5 for this example) and then insert that amount into the table? Then also applying the same format to the new rows as the existing rows have (I normally have to use the Format Painter option to copy format to new rows when I update manually).
      • If this cannot be done in one macro could another be created to accomplish this prior to the import / copy procedure?
    Let me know if you have any ?'s and I will get right back to you.

    Thank you!
    Attached Files

  • #2
    Hi GoCavs...

    In summary, are you simply then overwriting all the records in the weekly report with the rows of data from the Snapshot export?
    Couple of concerns around the columns... they do not align 1 to 1... for example
    Column AK header in the Weekly report says "TRS Est. Billings"
    Column AK header in the sample report says "Lead Opportunity Description"

    It seems there is an extra header in the weekly report file.

    There are also difference in the column header names... for example,
    "Updated By" in the sample report seems to equate to "Last Updated By" in the weekly report.

    The number of rows/formatting is not a problem.

    Regards
    Ger
    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      I can look at this for you, I will respond here with any queries.
      We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

      Comment


      • #4
        We responded at the same time, Ger, let me know if you are taking this on.
        We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

        Comment


        • #5
          Ha ha, not a problem KJ - please go ahead... I may not have time to look at it this week.

          Just realised the column misalignment that i thought existed is just because the weekly file has an extra column of information in Column A, a URL, which the sample file does not... so they seem to align after that. So please go ahead KJ.. I'll bow out here.

          Ger
          Check out our new reputation system. Click on the Like button under the post!
          _______________________________________________

          There are 10 types of people in the world. Those that understand Binary and those that dont.

          Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

          The BEST Lookup function of all time

          Dynamic Named Ranges are your bestest friend

          _______________________________________________

          Comment


          • #6
            OK, thanks Ger.
            We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

            Comment


            • #7
              Thank you KJ !! Let me know if you have any ?'s

              Thank you too Ger!

              Comment


              • #8
                Are both the Weekly Report file and the Export Snapshot files in the same Directory and folder? If not what is the full path to the Export Snapshot files?
                We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

                Comment


                • #9
                  It will be the same Directory but different folder.

                  Snapshot Address:
                  C:\Users\jstriker\Documents\Alix Partners\Weekly Dashboard\Snapshot Export

                  Weekly Dashboard Address:
                  C:\Users\jstriker\Documents\Alix Partners\Weekly Dashboard

                  Comment


                  • #10
                    OK thanks.

                    It is 2AM here now so may not get it done before I start falling asleep! If that happens I will finish it after I wake.
                    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

                    Comment


                    • #11
                      No problem. I appreciate your help

                      Comment


                      • #12
                        When the data is imported what happens to the URLs in Column 1 of Table1?

                        If the imported data has less rows than the current data in Table1, are the extra URLs deleted?

                        If the imported data has more rows than the current data in Table1, do additional URLs need to be inserted with the ending number incremented by 1 for each new URL?
                        We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

                        Comment


                        • #13
                          When the data is imported what happens to the URLs in Column 1 of Table1? The URL's is a formula based off the ID in Column B so when data is pasted in Column B, the URL's will be updated based on the new values.

                          If the imported data has less rows than the current data in Table1, are the extra URLs deleted? Yes, if they will be deleted.

                          If the imported data has more rows than the current data in Table1, do additional URLs need to be inserted with the ending number incremented by 1 for each new URL? Yes, they need inserted but when you insert rows, the formulas automatically get populated since it is a table.



                          Comment


                          • #14
                            OK, ignore last post. I now see how the URL is derived by a formula, so that will adjust automatically.
                            We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

                            Comment


                            • #15


                              I have a solution for you. I will PM you with my PayPal details and attach the file here upon receipt of payment.
                              We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

                              Comment

                              Working...
                              X