8 Replies Latest reply: May 15, 2014 10:11 AM by elginreigner _ RSS

    Excel appending issue

    warlok

      I have a problem that I can't narrow down and I'm stuck for ideas on where to go.

       

      I have created a project that reads a file and gets the information it needs but appends it to a spreasheet.

       

      The batch file then renames this spreadsheet and runs it through as input for a secondary Monarch project. where the output for this one is the name of the original spreadsheet.

       

      Now I've taken the projects and the batch file and sent it to a coworker at another location. They've loaded the system with the same directory structure.

       

      I can run the project endlessly with no problems. It will read in the information and append to the spreadsheet multiple times if I wish it to.

       

      My co-worker's offsite system doesn't work. It doesn't append any information to the spreadsheet at all. When she sends me the spreadsheet back for me to use and check. It stops appending for me as well.

       

      We're both using Excel 2003 (old, I know) and we're both using Monarch version 10.

       

      When I look at the spreadsheet we're trying to appen to, there doesn't seem to be any difference inside it with one exception. The fonts and background of the heading cells are a diffent color. Apart from that we're both starting with a blank spreadsheet which is empty of information.

       

      Essentially. I end up with an eact duplicate of the initial spreadsheet I start with and can work multiple times with except, Monarch fails to work. It skips over it very fast and doesn't append to it. Almost as if Monarch can't open the file from the batch and I don't know why. I've verified the data by opening. it in Monarch rather than running the batch and that works correctly.

       

      Anyone have any expereince or knowledge in what could possibly cause this? Any hidden data possibly embedded somehow in the excel spreadsheet that might cause it to look similar but actually be different? Is there any way to capture perhaps the error number or code that would occur during a failure to open the spreadsheet in the batch file?

       

      I'm kind of just reaching for ideas at this point.

        • Excel appending issue
          warlok

          I've narrowed it doen to the fact for some reason the spreadsheet my coworkrer generates is in Excel 95 format. The one I send her to use is in 2003 format. Somehow during the save it's being converted to the older format.

           

          Now I have to determine, if it's the way she is saving the file or if the Monarch project I created is somehow doing that on her system due to some sort of system setting.

          • Excel appending issue
            Olly Bond

            Hello Warlok

             

            Are you exporting from the table or the summary? Are you exporting to a sheet or a named range?

             

            If from a summary, could you try exporting to MDB instead, which should eliminate any Excel formatting issues, then introducing an intermediate project that reads this in and -then- exports to Excel?

             

            I'm thinking of changing my domain name to www.monarchwithoutexcel.com[/url] - there's data for humans to read, and then there's data for robots, and they shouldn't be mixed

             

            Best wishes,

             

            Olly

              • Excel appending issue
                warlok

                Hi Olly.

                 

                The table is appending to a named sheet within the batch file.

                 

                I've narrowed down the whole issue to her system somehow saving all Excel spreadsheets whether generated by Monarch or not in Excel 95 format. However, I've also verified that she is in fact running Excel 2003.

                 

                I know the problem has sort of veered away from Monarch being the cause of the problem at this point. But I'm unsure where to go or make a change to ensure by default her system uses Excel 2003 file format. Doing that would fix the issue.

                 

                If anyone knows where it might be set I'd be appreciative but as I said I htink it's become a none Monarch issue.

                  • Excel appending issue
                    warlok

                    It seems I was a little hasty in excluding Monarch as the source of the problem.

                     

                    After doing more checks it seems my co-workers version of Monarch had setup under Options - Export and Clipboard the XLS Files setting to be Excel 95.

                     

                    After changing this all worked correctly. I had discounted Monarch because it seemed a rename in the script was also causing the Excel 95 problem, I failed to realize that because the script had run the first Monarch pass it converted the out put at that time to Excel 95.

                     

                    I'd like to thank everyone who looked into this for me and I appreciate the work done. I feel a little foolish, but we learn and we grow. Thanks again all.

                      • Excel appending issue
                        elginreigner _

                        Warlok, I'm glad you were able to resolve the issue. As you found out Monarch can cause this issue, it would be rare for windows to cause this issue. The only reason I could see Windows causing this issue would be from multiple versions of Excel installed and the Excel(95-97) being the default excel. Again, I'm glad it all worked out as this is what makes us better Monarch users.

                          • Excel appending issue
                            warlok

                            There were additional problems, I'm trying to still work out over it though. I've noticed that Monarch version 10 seems to be able to better handle Excel spreadsheets on a whole over vesion 8.

                             

                            After more digging I was able to confirm on a third machine that the users using the older version 8 had an issue with opening the excel spreadsheet to append to. It seemed to have some tabls configuration issues with a couple of columns of infromation which visually looked okay. In fact the error message even looked strange in that it would tell me the names were the same but incompatable even after I changed them and made them compatable. I ended up fixing it by recreating the spreadsheet using the export feature on a version 8 machine without appending and creating it from scratch.

                             

                            Of course this worked fine and tested fine on a third PC but my target end user is still experiencing some issues and I'm still trying to figure out why right now. Her spreadsheet is empty and it could be bad data she is trying to input, or a similar corruption again of the spreadsheet.

                              • Excel appending issue
                                warlok

                                So the big question is why did it work for me and not the other user. The answer is actually kind of it did work for us both and it didn't.

                                 

                                The end user was hitting an issue that I couldn't duplicate because it happened over 2-3 days. Their Excel spreadsheet for the one report would generate a large amount of records and by the third day would exceed the threshold of Excel causing Monarch to not be able to write the records to Excel and effectively creating a blank Excel spreadsheet.

                                 

                                My intial testing involved using user end user data, but was always just using the data from the day it failed to work. I never reached the threshold so it worked fine for me.

                                 

                                I'm posting this to show that firstly, it doesn't seem to be a difference between Monarch versions. But also as a possible warning for others when testing such things. Sometimes it takes a lot to narrow down issues and it's not always as easy as it may seem to narrow down the problems. It took me a long while to figure out what was happening to the file.

                                  • Excel appending issue
                                    elginreigner _

                                    You can export in the xlsx format to take advantage of the 1.048 million records available. You may will need to install the converter to open them in 2003 office. I do this for the same reason, as you don't want the append to fail.

                                     

                                    As for the Excel 95 format, you can specify in Excel which format you want as default to save. I do not believe Monarch knows this save type, but I'm not 100% on that. But as stated before, can also be caused by multiple installs (I see this a lot).