1 Reply Latest reply: May 15, 2014 9:53 AM by Guy Chowning RSS

    MORETHAN  65535 ERROR  EXEL EXPORTS-DYNAMIC FILTERS

    yesb _

      Hi,

        I m a monarch V7 user,I have a model consisting

      20 filters, each filter returns more than 65535 records.The number of records varies every time.So when I m exporting to excel sheet I m getting 'error exceeds no of rows limitation in exel' and process is aborted.

      Is there any way to dynamically create another work sheet when  number  of records exceed maximum limit(ie 65535)? .Help me I m struck now.

       

      Thanks in advance.

        • MORETHAN  65535 ERROR  EXEL EXPORTS-DYNAMIC FILTERS
          Guy Chowning

          Try this it works for me:

           

          code:

          -


           

          Sub ImportLargeFileADO()

          'Imports text file into Excel workbook using ADO.

          'If the number of records exceeds 65536 then it splits it over more than one sheet.

           

              Dim strFilePath As String, strFilename As String, strFullPath As String

              Dim lngCounter As Long

              Dim oConn As Object, oRS As Object, oFSObj As Object

           

              'Get a text file name

              strFullPath = Application.GetOpenFilename("Text Files (.txt),.txt", , "Please select text file...")

           

              If strFullPath = "False" Then Exit Sub  'User pressed Cancel on the open file dialog

           

              'This gives us a full path name e.g. C:     empfolderfile.txt

              'We need to split this into path and file name

              Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")

           

              strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.path

              strFilename = oFSObj.GetFile(strFullPath).Name

           

              'Open an ADO connection to the folder specified

              Set oConn = CreateObject("ADODB.CONNECTION")

              oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                         "Data Source=" & strFilePath & ";" & _

                         "Extended Properties=""text;HDR=Yes;FMT=Delimited"""

           

              Set oRS = CreateObject("ADODB.RECORDSET")

           

              'Now actually open the text file and import into Excel

              oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1

              While Not oRS.EOF

                  Sheets.Add

                  ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536

              Wend

           

              oRS.Close

              oConn.Close

           

          End Sub

           

          -