7 Replies Latest reply: May 15, 2014 9:53 AM by RalphB _ RSS

    JetExport Append To Excel

    sxschech _

      I was using code found elsewhere in this forum and perhaps am misunderstanding it.  I have a button on an MS-Access form which runs vba to take text data files, run them through Monarch and Export/Append to an Excel file.  It runs a loop (for 5 separate text files that use 5 different models which output into same format for use in excel); the first time it creates the file, the second time and on, it should append to the existing file.  Currently, the code creates the initial excel file, but no additional files are appended.  I am only including the snippet, if you need the full code, I can provide.

      -


      'Create New File if GLASC, otherwise append data

                          If i = 1 Then

                              exportfile = MonarchObj.jetExportTable(stFileLoc & "GradTransport.xls", "GradTransport", 0)

                          Else

                              exportfile = MonarchObj.jetExportTable(stFileLoc & "GradTransport.xls", "GradTransport", 2)

                          End If

      -


       

      Notes:

      Access 2003, Monarch 8.01 pro

      exportfile is boolean

      stFileLoc="DiscimageserverBackup for Steve SAdStatDataM-2007-Data"

       

      I stepped through code and it does step into the if statement as I would expect, but apparently isn't appending per the append flag "2".  Also, how can I filter the records that are to be saved in excel format.

        • JetExport Append To Excel
          RalphB _

          Hi,

           

          Are you incrementing the "i" counter each time the loop runs? This is the only way the script will know which exportfile to use otherwise it sees that i still equals 1.

           

          As for a filter, you can use the line like

           

          Monarchobj.CurrentFilter = "filtername"

           

          HTH.

           

          Ralph

          • JetExport Append To Excel
            Data Kruncher

            A few months ago Ralph  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001256;p=1#000001"]posted some other ideas[/url] relating to problems with appends. Does this discussion of range names help at all with your situation? This often seems to be the root problem of appends not working as expected.

             

            Expanding on the filters topic a little, I like to double check that the filter name I set in the code actually worked, because if the name you specified in your code doesn't exist in the model, Monarch won't stop or complain. It will just keep on going with the current data set.

             

            Kruncher

            • JetExport Append To Excel
              sxschech _

              I took a look at the other discussion, but didn't seem like it helped my situation.  I am indeed incrementing by 1 in the code, I did not include entire code, since the issue was with the monarch code rather than the loop.  I believe the code I am using is being executed because when I go through step mode (F8) it gets highlighted and I see monarch flash briefly.  It is to quick for me to see what messages, if any, are displayed.

              • JetExport Append To Excel
                RalphB _

                Hmmm...

                 

                  ----


                'Create New File if GLASC, otherwise append data

                If i = 1 Then

                exportfile = MonarchObj.jetExportTable(stFileLoc & "GradTransport.xls", "GradTransport", 0)

                Else

                exportfile = MonarchObj.jetExportTable(stFileLoc & "GradTransport.xls", "GradTransport", 2)

                End If

                -


                  /quoteIf i is incrementing each time you loop and when you get to your if export if statement and the append flag doesn't change, I don't know what is happening.  Maybe someone else can answer why.  I throw it open to anyone who may have an answer.

                 

                There is a possible work-a-round however if you are creating a new file each time and not overwriting an existing file.  If this is a new file with a different name each time, you can set the append flag to 2 and leave it at that.  This will create the file and append each time.  You only have to have the append flag set to 0 only if you want to overwrite an existing file.

                 

                HTH

                 

                Ralph

                • JetExport Append To Excel
                  sxschech _

                  Hi Ralph,

                   

                  Thanks for your suggestion.  I tried it, unfortunately it didn't make a difference.  The next thing I've tried is to save them to separate files using the "i" loop counter as the differentiator. (GradTransport1.xls, GradTransport2.xls, etc.) This created the export for all the files. Now I have 5 files instead of one. Not sure why I am unable to save into one file.  Could the problem be due to the col. heading.  For example, all the files when viewed in excel begin in Col A and end in Col K.  The only difference I noted were that some files had a heading of School and others College.  Do the headers need to be the same.  I'll see what that does.

                  • JetExport Append To Excel
                    sxschech _

                    I think that was the problem.  I changed College to School and the files combined into one.  I guess it isn't like an SQL union where the Col headers don't matter so long as the number of Cols and data format are the same.

                    • JetExport Append To Excel
                      RalphB _

                      I'm glad you have it figured out.  Yes, column headings need to be the same when you append to a file.  You can append fewer columns with the same name but you can't have more columns in your append export. For example, in your first export, you export columns A thru K.  You can't then append an export with columns A thru L. 

                       

                      BTW, the order of the columns does not matter with the append, the columns just have to be there.

                       

                      Ralph