7 Replies Latest reply: May 15, 2014 10:01 AM by Data Kruncher RSS

    Help With Exporting Formulas

    Joe Berry

      I would like to use Monarch to create a text formula to export into Excel.  I successfully created the calculated character field with the formula as I would type it into Excel.  Unfortunately it did not work.  An example of the text created was =L1-L2.  Any ideas on what I can do to get it to work? 

       

      I am using Monarch because it will be easier than entering and copying all of the formulas directly in Excel.

        • Help With Exporting Formulas
          Data Kruncher

          Hi Joe,

           

          This concept continues a [URL="http://www.monarchforums.com/showpost.php?p=792&postcount=1"]post by Nick[/URL] from back in 2004 that I only recently discovered.

           

          I think what you're fighting with is a bit of an oddity in how Excel handles the formula. As Nick mentions, if you copy from Monarch and paste into Excel, Excel interprets the formula as a formula and evaluates it as you would expect.

           

          But when you export it from Monarch to an Excel file, upon opening it you see that Excel gives it a leading apostrophe, and thus treats it as a text string. There's nothing you can do in Monarch to prevent that.

           

          Instead, select all of the columns with misbehaving formulas, and from the Data menu, select the Text to Columns feature. Don't mess around with the dialog, just click Finish and your formulas will now calculate properly.

           

          Of course, getting the row numbers to change is a different issue. We can discuss that further.

           

          Does this help so far?

            • Help With Exporting Formulas
              Joe Berry

              Thanks for the repsonse.  This does help - a little extra work, but still better than how I had to do it in Excel.  Thanks for the help.

                • Help With Exporting Formulas
                  pbishop@cds-global.com _

                  So I have a question about this.  Is there anyway I can reproduce that in code?  I'm making a default blank workbook that I will append 4 sheets to.  I will do this process 100's of times each day.  So I'm trying to come up with a default workbook that has a macro that will automatically make these formula cells show the calculation.  So Is there a way I can do that in VBA code or do the text to col feature in VBA.  We don't want to have the users have to do any other steps when opening.  We want the data the way they need it when they open.

                   

                  Any ideas???

                    • Help With Exporting Formulas
                      OddJob _

                      Hi

                      You might also find that a simple search and replace to Replace = with = will also force the formula to recalculate.

                      I have to use this regularly when exporting from Monarch to a worksheet which contains the source data for a lookup contained on another work sheet.

                       

                      As for a VBA / Macro, I tend to take the easy way out and record a Macro and then edit the resulting Macro as necessary.

                       

                      Depending on your requirements, it might be worth creating a dummy spreadsheet that just contains the Macro, then export your Monarch data to another spreadsheet. Open the the dummy spreadsheet and run the Macro that imports the Monarch data, updates the calculations and copies the results to a new spreadsheet file.

                       

                      You can run all of these steps manually and record them to a Macro so, like me, you don't have to be an expert at VBA script or coding etc.

                      Broadly speaking, if you can do it manually, you can do it with a Macro / VBA but, it really depends how comfortable you are with coding etc.

                       

                      Hope this helps

                        • Help With Exporting Formulas
                          pbishop@cds-global.com _

                          I'm trying to make it all automated and as few steps as possible.  I tried recording the macro and then put that code in my workbook open VBA code.  That works as long as the sheet that I want to have update the cells is being displayed.  If it's not then it will try and run the macro against the sheet that is open and I don't want that.  As of now I don't know if I can always count on my sheet names being the same that I could hard code that in the code.  That's why I was trying to find a VBA command that will basically go through the whole workbook and calc any formula cells that were exported from Monarch.  That's where I am stuck at now.

                            • Help With Exporting Formulas
                              OddJob _

                              Hi

                               

                              Although I'm no expert and this is going slightly off topic for the Monarch Forum, I think that within the Macro you can make any sheet the  Active Worksheet to nominate which worksheet the commands are applied to. The sheets can be specified by sheet number rather than worksheet name. So you should be able to select sheet 1, run the update for calculations, Select sheet 2 etc.

                               

                              As I said previously, I generally record the whole process and then edit any parts of the Macro that might need to be changed slightly.

                               

                              Beyond this, I'm afraid I'm not going to be of much help. There are Forum Members with much more detailed knowledge than me on this subject.

                               

                              Hope this Helps.

                                • Help With Exporting Formulas
                                  Data Kruncher

                                  FYI,

                                   

                                  pbishop sent me a copy of his Excel file at my request. He'd left a comment about the same problem on my site.

                                   

                                  I wrote a just few lines of VBA code that should resolve the issue, and will be portable/applicable to any Monarch exported workbook/worksheet regardless of the names used, and sent the file back to him.

                                   

                                  He indicated that my solution should resolve his problem.