Crystal report export in excel

Question

Having problem in my crystal report, exporting to excel. Can somebody help me how to code or any suggestion code that will catch if the file you exported is already exist. For example you exported lotinfo the next will be lotinfo2 then lotinfo3 etc, my code is always exporting single file and single name.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Try
        Dim CrExportOptions As ExportOptions
        Dim CrDiskFileDestinationOptions As New  _
        DiskFileDestinationOptions()
        Dim CrFormatTypeOptions As New ExcelFormatOptions
        CrDiskFileDestinationOptions.DiskFileName = _
                                    "c:\Lot Enterprise Information.xls"
        CrExportOptions = crypt.ExportOptions
        With CrExportOptions
            .ExportDestinationType = ExportDestinationType.DiskFile
            .ExportFormatType = ExportFormatType.Excel
            .DestinationOptions = CrDiskFileDestinationOptions
            .FormatOptions = CrFormatTypeOptions
        End With
        crypt.Export()
        MessageBox.Show("LOT ENTERPRISE INFORMATION IS SUCCESSFULLY EXPORTED!, LOCATED AT DRIVE C:", "PLEASE CHECK AT DRIVE C:", MessageBoxButtons.OK, MessageBoxIcon.Information)
    Catch ex As Exception
        MsgBox("Please Select Specific date to convert!")
        'MsgBox(ex.ToString)
    End Try

End Sub

Codemunkeee Codemunkeee · Accepted Answer · 2014-02-18T02:30:34

I've been using this function for quite some time now. Just fix it up depending on your use.

Private Function fileExists(ByVal path As String, ByVal filename As String) As String
   ' This function basically adds a counter when the file already exists
   ' eg filename
   ' filename(1)
   ' filename(2) 

   Dim counter As Integer = 0
   Dim orginialFileName As String = System.IO.Path.Combine(path, filename)
   Dim newFileName = orginialFileName
   Dim extension As String = ""
   Dim testIndex As Integer = 0
   While File.Exists(newFileName)
      counter = counter + 1
      extension = newFileName.Substring(newFileName.LastIndexOf("."))

      filename = filename.Substring(0, filename.LastIndexOf("."))
      testIndex = filename.LastIndexOf("(")
      If testIndex <> -1 Then
          filename = filename.Substring(0, testIndex)
      End If

      newFileName = String.Format("{0}({1})", System.IO.Path.Combine(path, filename), counter.ToString())
      filename = String.Format("{0}({1})", filename, counter.ToString())

      newFileName += extension
      filename += extension
  End While

  Return filename
End Function

example usage

Dim output as string
output = fileExists("C:\test", "file.xls")
MsgBox(output)

This link might also be useful for you.

EDIT:

You can use it before your Try-Catch block

    Dim fullPath As String = "C:\fileinfo.xls"
    Dim directory, output, filename As String
    If File.Exists(fullPath) Then
        directory = fullPath.Substring(0, fullPath.LastIndexOf("\"))
        filename = fullPath.Substring(fullPath.LastIndexOf("\") + 1)
        output = fileExists(directory, filename) 
        fullPath = path.combine(directory,output)
    End If

Then change this part

CrDiskFileDestinationOptions.DiskFileName = _
                                    "c:\Lot Enterprise Information.xls"

To

CrDiskFileDestinationOptions.DiskFileName = _
                                    fullPath

Crystal report export in excel

1 Answers