5 Replies Latest reply: May 15, 2014 9:52 AM by RalphB _ RSS

    VBA and JetExportTable

    HKS _

      Actually this is the same macro I posted on the other posting, but different topic. The JetExportTable for the second account number /runtime parameter just not added into the Exported Excel DataFile, why?

       

      Openfile = MonarchObj.SetReportFile("Filename", False)

      vRow = 8

      Do While IsEmpty(Cells(vRow, 2)) = False

        vAcct = Cells(vRow, 2)   'inputed account #

        PromptAcct = MonarchObj.SetRuntimeParameter("ACCT", vAcct)

        If vRow = 8 Then

        OpenModel = MonarchObj.SetModelFile("ModelName")

        End If

        MonarchObj.CurrentFilter "Filtername"

        If vRow = 8 Then

        ExportData = MonarchObj.JetExportTable("DataFilename", vAcct, 0)

        Else

        ExportData = MonarchObj.JetExportTable("DataFilename", vAcct, 1)

        End If

        vRow = vRow + 1

      Loop

      MonarchObj.CloseAllDocuments

      MonarchObj.Exit

        • VBA and JetExportTable
          RalphB _

          Hi,

           

          You need to set the append flag to 2 to append to an existing table or spreadsheet.  Append flag 0 and append flag 1 do basically the same thing, create a new table or spreadsheet or overwrites a nn existing table or spreadsheet.

           

          HTH

           

          Ralph

          • VBA and JetExportTable
            HKS _

            Well, I would like to export the data to a new worksheet within an existing workbook.

            • VBA and JetExportTable
              RalphB _

              Sorry HKS, I misread your first post. Are you exporting out to a new excel file each time or do you want to overwrite an existing file?  If you are creating a new file each time, then what you have is ok and works fine.  If you want to overwrite the same file each time you run the script, change the append flag on both exports to 0 or you could just eliminate the second export line.  The append flag of 1 does not overwrite an existing file.

               

              Ralph

              • VBA and JetExportTable
                HKS _

                I'm sorry that I didn't write my posting clearly. What I want is that all exports will be done into one excel file with multiple worksheets/tabs. Thus, the first account will be exported to the "Sheets1," the second will be exported to "sheets2," and so on. Again, I have a 8.00 version

                 

                [size="1"][ January 12, 2007, 01:06 PM: Message edited by: HKS ][/size]

                • VBA and JetExportTable
                  RalphB _

                  I tested your code using reports and models I have set up and it ran fine.  It added the spreadsheets each time with no problem.

                   

                  The only problem I had was when I tried to add data to the same spreadsheet using the same sheet names when using the "add table" append flag like if you want to add data from a different day using the same account numbers and want to overwrite the existing data.  If that is the case, you need to set the append flag to 0 to overwrite the existing data.