Announcement

Collapse
No announcement yet.

Update Multiple Sheets From CSV File

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

  • Update Multiple Sheets From CSV File



    I have a fairly large Excel Workbook with 76 tabs. The workbook works just fine, but I would like to automate the manual process a little further.

    In the first tab I have a list. The second tab there is a template. I have written code to build the template in a new tab based on the original list.

    Each week I will have data "dumped" from another program into a .csv file that will need to be added to most all of the tabs. I would like to automate this process as much as possible. If somebody would like to take this on.. please let me know. Also, there will be some more work to do on seperating data out.

    Thanks,
    LonghornShane

    Tried numerous times to post the file... Tried zipping it up and still no luck...

    Please send message if interested.

  • #2
    Re: 74 Tabs that contain Charts and data that will accumulate (WBS)

    Willing to pay a reasonable amount....

    Comment


    • #3
      Re: 74 Tabs that contain Charts and data that will accumulate (WBS)

      Hi Longhorn, sounds like the file may have been a bit too big.

      Maybe try posting on a hosted site like save file. http://www.savefile.com

      If no success there, post back and I'll PM you email address and I'll take a look a closer look at the job / requirements. Hard to say yet how much work is involved without seeing the file and a sample CSV file.

      By the way, I'm assuming the job (requirements) is basically take a csv file, read it and update some/all of the 76 sheets. In which case, what is the criteria for updating each sheet (i.e. how do I/code know which sheet to update from the data in the CSV file).

      Cheers,
      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


      • #4
        Re: 74 Tabs that contain Charts and data that will accumulate (WBS)

        LonghornShane, you MUST state an amount you are willing to pay else this thread will be deleted.

        Comment


        • #5
          Re: Update Multiple Sheets From CSV File

          Thanks for the input and replies.

          I will try and go to savefile.com and get it posted there.

          Dave,

          It truly depends on how many "bells and whistles" are going to be added to this file. I understand a stated amount needs to be added to the post, but until somebody tells me what they will attempt to do than it is somewhat hard to estimate the "job".

          It could be anywhere from $50-$500 dollars.. Again... Depending on what type of work will be done.

          I hope this clears up any misunderstandings. I am truly trying to abide by the rules set forth by this wonderful website.

          Thanks,

          Longhorn

          Comment


          • #6
            Re: Update Multiple Sheets From CSV File

            Files are at the following...

            http://www.savefile.com/projects/808692329

            Comment


            • #7
              Re: Update Multiple Sheets From CSV File

              Hi Longhorn... I have made the connection between the CSV file and the tab names, but thats about as far as I got. the CSV file contains 7 day week periods along the top, but its not clear to me where/how the numbers underneath for each sheet should be entered in the given worksheet name, or more specifically where they should be entered into the given worksheet name...

              Also, there is "CUM" line in the CSV file, which is borderline rude, but that aside, I cant figure out how the CUM line is calculated / derived, or if it is relevant to the given worksheet name, or relevant at all to this problem.

              Also, will the name in the CSV file always follow the format of "6-TP MAST.99.99.99", which transforms into a worksheet name of TP_99.99.99"

              Will the worksheet name in the CSV file always exist as a worksheet in the workbook? (i.e. is it guaranteed that a sheet will always exist for the data in the CSV file....

              Finally will the 7 day periods in the CSV file always exist in the given worksheet (or at least can you guarantee that all the periods start on the same [7 day] date).

              I'm honestly not sure yet if I would have time to take it on (I'm technically on holidays), but in case any other Excel people are watching, they will need the above information too.

              Cheers for now,
              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


              • #8
                Re: Update Multiple Sheets From CSV File

                I hope this clears up any misunderstandings.
                Sure does, thanks.

                Comment


                • #9
                  Re: Update Multiple Sheets From CSV File

                  I will try and answer these questions as best as possible.

                  The first thing that needs addressed is what a WBS is and how it works. (very short summary)

                  Sample WBS
                  1
                  1.1
                  1.1.1
                  1.2
                  1.2.1
                  1.2.2

                  The workhours in WBS "element" 1.1 will be a total of 1.1

                  1.2 will be a total of 1.2.1 and 1.2.2

                  So 1.1 AND 1.2 will be totaled into the top tier of "1".

                  Formula: 1=(1+2); 1.1=(1.1.1); 1.2=(1.2.1+1.2.2)

                  **********************************************************

                  The values of each WBS element will transfer into the "LATE PLAN" and "EARLY PLAN" in each corresponding tab. The dates START IN CELL B132, the "EARLY PLAN" STARTS IN CELL C132 and the "LATE PLAN" STARTS IN CELL E132.
                  **********************************************************

                  The CUM is simply a total of the WBS elements as you are progressing through the weeks / months / years.. etc. I can take that out of the CSV file if it would make things easier...
                  **********************************************************
                  Also, will the name in the CSV file always follow the format of "6-TP MAST.99.99.99", which transforms into a worksheet name of "TP_99.99.99"
                  YES!

                  The 6-TP MAST.99.99.99 will ALWAYS follow that format and transform into the tabs.

                  **********************************************************

                  Will the worksheet name in the CSV file always exist as a worksheet in the workbook? (i.e. is it guaranteed that a sheet will always exist for the data in the CSV file....
                  It doesn't have to be, but it could if necessary. One thing to point out.. These CSV files will be "dumped" from a program on a weekly basis. And essentially that is why I am attempting to automate this as efficiently as possible.
                  ***********************************************************

                  Finally will the 7 day periods in the CSV file always exist in the given worksheet (or at least can you guarantee that all the periods start on the same [7 day] date).
                  YES! These week ending periods will always stay the same.

                  Feel free to send me a message and I am more than willing to talk about this on the phone or via email if it makes things easier. Of course the amount and payment method etc. will be worked through this website.

                  Comment


                  • #10


                    Re: Update Multiple Sheets From CSV File

                    Sorry for not replying before now Longhorn...

                    So to put this in simpler terms, update the early and late columns in the worksheets for each of the given date periods, with data from the CSV file.

                    Dont worry about the "CUM" line in the CSV file. If it's an "evaluated" figure, it can be ignored.

                    I have some additional questions -
                    When I open the CSV file there is a list of dates with numbers underneath for each relevant sheet - however do these numbers represent the early or late columns? If you know what I mean. I would expect two numbers per dated period in the CSV file (one for early and one for late). Or is the whole CSV file representing an "Early" (or "Late") period.

                    As you mentioned above, some worksheets are an accumulation of data from "sub" sheets underneath it. However, your CSV file contains RAW data (i.e. numbers) for those sheets on those dates... but those sheets on those dates are already prepopulated with formulas. What is someone supposed to do in that instance? e.g Sheet TP_01.06 contains dates in the worksheet, but also contains RAW data in the CSV file...

                    Basically the key thing here for me is to understand how the CSV file should be mapped to each sheet.

                    Sorry if I missed something obvious.
                    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

                    Working...
                    X