Output To flush data?

M

maniax89

Joined
Sep 10, 2010
Messages
1
Reaction score
0
I have been working on a vba module that takes user input from a form, creates a report based on a query, then exports it to excel. The problem I am having right now is that if I run it 3, 4, or more times in a row, it stops exporting the correct information. What ends up happening is that the previous information that was exported the first few times is shown instead. It catches up too, but unfortunately not along with the user input. Therefore, there is some sort of lag in the export? Any suggestions?


Code: 
Private Sub EmployeeOpen_Click()
  On Error GoTo errHan
      Dim strWhere As String
      Dim treport As Object
      Dim tform As Object
      Dim ssql As String
      Dim db As DAO.Database
  
      Set db = CurrentDb()    
      Set treport = Report_EmployeeNew
      Set tform = Form_EmployeeInformation
  
      strWhere = BuildListWhere
      ssql = "SELECT " ' user input is located elsewhere
      ssql = ssql & strWhere
  
      DoCmd.SetWarnings False
      db.QueryDefs.Delete "Q040Employee"
      db.CreateQueryDef "Q040Employee", ssql
      db.Close
      Set db = Nothing
      DoCmd.SetWarnings True
      DoCmd.OpenReport "EmployeeNew", acViewReport
      Export
  End Sub
  
  Private Sub Export()
      Dim xlApp As excel.Application 'excel application holder
      Dim xlBook As excel.workBook 'excel workbook holder
      Dim xlSheet As excel.workSheet 'excel sheet holder
      Dim xlRange As excel.Range 'excel range holder
      Dim ofile As String 'output file name
      Dim Title As String 'title of the category
      Dim LastRow As Integer 'last row used in the export
      Dim LastColumn As Integer 'last column used in the export
      Dim firstcell As Object 'generic object to be set to cells
      Dim secondcell As Object 'generic object to be set to cells
      
      'establish last cells on the excel sheet using information from report
      LastColumn = Report_EmployeeNew.ColumnCount.Value
      LastRow = Report_EmployeeNew.RowCount.Value + 2
          
      'establish file strings
      Title = "Employee Information"
      ofile = Title & ".xls"
      ofile = "C:/" & ofile
  
      'output the file, but don't open the application yet
      DoCmd.OutputTo acOutputReport, , acFormatXLS, ofile, False
      
      'set the application handle
      Set xlApp = New excel.Application
      
      'set to false if you do not want to watch it happen
      xlApp.Visible = True
      
      Set xlBook = xlApp.Workbooks.Open(ofile)
      Set xlSheet = xlBook.Sheets("Q040Employee")
  
     'do formatting here
  
  
      Set firstcell = Nothing
      Set secondcell = Nothing
      Set xlRange = Nothing
      Set xlSheet = Nothing
      Set xlBook = Nothing
      Set xlApp = Nothing
  
      Exit Sub
      
  errHan:
      MsgBox Err.Number & " - " & Err.Description & vbCrLf & vbCrLf & _
      "Error occurred during Export function.", vbCritical, "Error!"
      On Error Resume Next
      Set firstcell = Nothing
      Set secondcell = Nothing
      Set xlRange = Nothing
      Set xlSheet = Nothing
      Set xlBook = Nothing
      Set xlApp = Nothing
      Exit Sub
     
      Resume
  End Sub
 
Back
Top