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

  • KjBox
    replied
    You're welcome

    Leave a comment:


  • GoCavs
    replied
    oh yes...this is much better. love it. I will keep testing and if something pops up I will get back to you. Thank you again. Much appreciated!!

    Leave a comment:


  • KjBox
    replied
    Try this version
    Attached Files

    Leave a comment:


  • GoCavs
    replied
    I ran a few tests and had a few ?'s. I have columns in the Snapshot tab Table1 table (CJ:CZ) that have formulas and after running the macro, it appears those columns are getting overwritten when the procedure runs and being pushed out of the table range. If the procedure could only paste in data up to column CH in the Snapshot Export file I think we will be there.

    Leave a comment:


  • KjBox
    replied
    Now attached in post #18

    Leave a comment:


  • GoCavs
    replied
    Thank you!!!! I will let you know if I have any ?'s. Now go get some rest!!!

    Leave a comment:


  • GoCavs
    replied
    Thank you Kj...I don't see the file in your post.

    Leave a comment:


  • KjBox
    replied
    Oops forgot to attach file, half asleep here!
    Attached Files

    Leave a comment:


  • KjBox
    replied
    Payment received, many thanks.

    Here is the file, the button is to the right of your filtering controls.

    Let me know if you have any issues.

    Leave a comment:


  • GoCavs
    replied
    Excellent. I'll get payment out when I get the info.

    Leave a comment:


  • KjBox
    replied
    I have a solution for you. I will PM you with my PayPal details and attach the file here upon receipt of payment.

    Leave a comment:


  • KjBox
    replied
    OK, ignore last post. I now see how the URL is derived by a formula, so that will adjust automatically.

    Leave a comment:


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



    Leave a comment:


  • KjBox
    replied
    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?

    Leave a comment:

Working...
X