5 Replies Latest reply: May 15, 2014 9:54 AM by Nick Osdale-Popa RSS

    jetexport question

    andyp _

      I am having probs altering the code below which i received from a reply to a previous post

       

      the routine is really good it processes 500 files except 2 which are large

       

      if i run monarch from the command line it has no trouble with theses 2 files

      so reading up im guessing jetexporttable would work better than  exporttable

       

      I have tried but just get errors trying to use jetexport table

       

       

      can anyone help ?

       

       

      start code>>

       

       

      Private Sub Command1_Click()

          Dim FSO As New FileSystemObject

          Dim strinputfld As Folder

          Dim stroutputfld As Folder

          Dim strfile As File

          Dim stroutput As String

          Dim done As Boolean

          Dim i As Integer

           

          If Not FSO.FolderExists(txtinput.Text) Then

              MsgBox "Input Folder does not exist Doh!!."

              Exit Sub

          End If

           

          If Not FSO.FolderExists(txtoutput.Text) Then

              FSO.CreateFolder (txtouput.Text)

          End If

           

          Set strinputfld = FSO.GetFolder(txtinput.Text)

          Set stroutputfld = FSO.GetFolder(txtoutput.Text)

           

          For Each strfile In strinputfld.Files

              stroutput = Left(strfile.Name, InStr(1, strfile.Name, ".")) & "xls"

              If FSO.FileExists(stroutputfld.Path & "" & stroutput) Then

                  i = 2

                  While FSO.FileExists(stroutputfld.Path & "" & stroutput)

                      stroutput = Left(strfile.Name, InStr(1, strfile.Name, ".") - 1) & i & ".xls"

                      i = i + 1

                  Wend

              End If

               

               

              done = DoMonarch(strfile.Path, stroutputfld.Path & "" & stroutput, txtmodelname.Text)

              If Not done Then

                  MsgBox "Arse!!! " & strfile.Path & "."

              End If

          Next strfile

           

           

          MsgBox "Sorted..."

           

               

               

           

           

           

           

           

           

           

           

           

      End Sub

       

      Public Function DoMonarch(inputfile, outputfile, model) As Boolean

          Dim oMonarchObj As Object

          Dim oinput As Boolean

          Dim FSO As New FileSystemObject

               

          Set oMonarchObj = CreateObject("Monarch32")

              oinput = oMonarchObj.Setreportfile(inputfile, False)

          If oinput Then

                  oMonarchObj.Setmodelfile (model)

                  oMonarchObj.ExportTable (outputfile)

                  oMonarchObj.Exit

          Else

                   oMonarchObj.Exit

          End If

           

          If FSO.FileExists(outputfile) Then

              DoMonarch = True

          Else

              DoMonarch = False

          End If

       

      End Function

       

      endcode>

        • jetexport question
          RalphB _

          You need Monarch 6.0 Pro to use the JetExportTable command.  If you have 6.0, JetExportTable or JetExportSummary allows you to save files in newer versions of Excel and also you can save into multiple sheets in a workbook.  So if you have been saving info into 2 different workbooks you could combine them into 1 workbook if you desire with JetExportTable.

           

          I had trouble also at first saving files using JetExportTable until I figured out the command.

           

          Your command will be something like this:

           

          JetExportTable

          • jetexport question
            RalphB _

            It should read

            JetExportTable(stringFileName),(string(TableName),(integerAppendFlag)

             

            ie: JetExportTable(myexportfile.xls),(sheet1),(1)

             

            This should help.

            • jetexport question
              andyp _

              great that fixed it i was missing the brackets doh thanks a lot for your help

              • jetexport question
                RalphB _

                I stumbled upon the solution by accident.  The programmers guide is not clear on this.

                • jetexport question
                  Nick Osdale-Popa

                  hmmm... I have no problem using this syntax:

                  [font="courier"]IsExported = mapMonarch.JetExportTable(sExportFile, strSheet, fOverwrite)[/font][/quote]which is the syntax used in the Programmer's Guide.

                   

                  [size="1"][ April 15, 2003, 12:56 PM: Message edited by: Nick Osdale-Popa ][/size]