title: Prompt needed while saving excel
body: <p>I am Trying to the results of two datasets to excel using the below function. Currently I am able to save it to some location. I want to show a prompt when the user click the Export Button and calls the below function. Currently it saves to My Documents. </p>
  
  <pre><code>Imports System.Text
  Imports Microsoft.Office.Interop.Excel
  
  Public Class ExportToExcel
  
      Public Sub DataSetsToExcel(dataSets As List(Of DataSet), fileName As String)
          Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
          Dim xlWorkbook As Workbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
          Dim xlSheets As Sheets = Nothing
          Dim xlWorksheet As Worksheet = Nothing
  
          For Each dataSet As DataSet In dataSets
              Dim dataTable As System.Data.DataTable = dataSet.Tables(0)
              Dim rowNo As Integer = dataTable.Rows.Count
              Dim columnNo As Integer = dataTable.Columns.Count
              Dim colIndex As Integer = 0
  
  
  
              'Create Excel Sheets
              xlSheets = xlWorkbook.Sheets
              xlWorksheet = DirectCast(xlSheets.Add(xlSheets(1), Type.Missing, Type.Missing, Type.Missing), Worksheet)
              xlWorksheet.Name = dataSet.DataSetName
  
              'Generate Field Names
              For Each dataColumn As DataColumn In dataTable.Columns
                  colIndex += 1
                  xlApp.Cells(1, colIndex) = dataColumn.ColumnName
              Next
  
              Dim objData As Object(,) = New Object(rowNo - 1, columnNo - 1) {}
  
              'Convert DataSet to Cell Data
              For row As Integer = 0 To rowNo - 1
                  For col As Integer = 0 To columnNo - 1
                      objData(row, col) = dataTable.Rows(row)(col)
                  Next
              Next
  
              'Add the Data
              Dim range As Range = xlWorksheet.Range(xlApp.Cells(2, 1), xlApp.Cells(rowNo + 1, columnNo))
              range.Value2 = objData
  
              'Format Data Type of Columns 
              colIndex = 0
              For Each dataColumn As DataColumn In dataTable.Columns
                  colIndex += 1
                  Dim format As String = "@"
                  Select Case dataColumn.DataType.Name
                      Case "Boolean"
                          Exit Select
                      Case "Byte"
                          Exit Select
                      Case "Char"
                          Exit Select
                      Case "DateTime"
                          format = "dd/mm/yyyy"
                          Exit Select
                      Case "Decimal"
                          format = "$* #,##0.00;[Red]-$* #,##0.00"
                          Exit Select
                      Case "Double"
                          Exit Select
                      Case "Int16"
                          format = "0"
                          Exit Select
                      Case "Int32"
                          format = "0"
                          Exit Select
                      Case "Int64"
                          format = "0"
                          Exit Select
                      Case "SByte"
                          Exit Select
                      Case "Single"
                          Exit Select
                      Case "TimeSpan"
                          Exit Select
                      Case "UInt16"
                          Exit Select
                      Case "UInt32"
                          Exit Select
                      Case "UInt64"
                          Exit Select
                      Case Else
                          'String
                          Exit Select
                  End Select
                  'Format the Column accodring to Data Type
                  xlWorksheet.Range(xlApp.Cells(2, colIndex), xlApp.Cells(rowNo + 1, colIndex)).NumberFormat = format
              Next
          Next
  
          'Remove the Default Worksheet
          DirectCast(xlApp.ActiveWorkbook.Sheets(xlApp.ActiveWorkbook.Sheets.Count), Worksheet).Delete()
  
  
          'Save
          xlWorkbook.SaveAs(fileName, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
           XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value)
  
          xlWorkbook.Close()
          xlApp.Quit()
          GC.Collect()
      End Sub
  
  End Class
  </code></pre>
  
  <p>In the Export Button CLick </p>
  
  <pre><code> Dim objExportExcel As New ExportToExcel
    objExportExcel.DataSetsToExcel(dataSets, TotalPAHComponentListDropDown.SelectedItem.Text)
  </code></pre>
  
  <p>Please help me if anyone knows the solution, i have been wasting a lot of time on this.</p>