VB Script Crashes Access When Exporting to Excel

VB Script Crashes Access When Exporting to Excel

• mole

  Excellent, the message box wouldn't have fixed anything but it could be that the other times the code ran the dates hadn't been supplied, that would have caused the error. You can remove the MsgBox code.

  There isn't any validation in place on the dates so I think the best option would be to put some in.

  This isn't ideal but put this

  If IsDate(Me.txtEnrollFrom.Value) = False And IsDate(Me.txtEnrollTo.Value) = False Then

  MsgBox "Date values are incorrect"

  Exit Function

  End If

  at the beginning of the Export2XL function and that should hopefully be you.

  Does it then run from the C:\ drive

  
  
  

• DannyH246

  Hello again, glad thats working alright.

  Your right the masked textbox will make sure the date is in the correct format but what it won't do is make sure the user enters something in, unless there is other code to do that. The error you got with the execute command would of occurred if there were no dates. IsDate() function covers this as no value is an invalid date.

  It's tricky with From and To dates and it all kinda depends on what the user wants to do. There a case for both approaches but it would be better to have the direction only going one way. What I mean is always go forward from yesterday to tomorrow rather than going back from tomorrow to yesterday.

  The way you would check this is with a function called DateDiff. With this function you can, among other things, work out how many days there are between dates. If the To day is after the From date it returns a positive number, if the To date is before the From date then it returns a negative, and returns zero if they are the same.

  Public Sub Test()

  Dim TheDate As Date ' Declare variables.
  Dim Msg
  TheDate = InputBox("Enter a date")
  Msg = "Days from today: " & DateDiff("d", Now, TheDate)
  MsgBox Msg

  End Sub

  If you want to specify a specific time period then do a check, > 356 days for example to make sure the user goes over a year, = 356 days, < 356 days.

  Programming can be a bit daunting at first but the best thing to do if your starting out is to ask questions even if you think they are daft.

  
  
  

• jchaffeejr

  Hello again,

  Your database code is trying to create a connection to itself in order to export the data, however it's not finding the file. The files path is hard coded and so the database always needs to be ran from H:\

  Try this line of code instead and see if that helps...

  Private Sub cmdExport_Click()

  Export2XL 1, CurrentProject.FullName, "[Enrollment Completion]"

  End Sub

  Also is the database password protected

  Not 100% sure if this will work but give it a go.

  
  
  

• Catalin Pop Sever

  Hi,

  What happens during the crash, do you get any more error messages

  You should replace the hard coded directory path with CurrentProject.FullName. It wouldn't matter where the database was stored then.

  Could you post the code of the method that contains

  If CN.State = 0 Then CN.Open

  What is CN, what is it used for

  
  
  

• cufunha

  Derek: Thanks for the suggestion. The database is not password protected.

  I substituted your code and got a debugging message box linked to this line of code:

  Set rs = cmd.Execute

  
  

• kateryn

  Thats good the connection is opening the database fine which is a start.

  Looks like there is a problem with the query. Is there a query or table called "[Enrollment Completion]" in the database

  You need to send me the value of strCMD... just before Set rs = cmd.Execute write

  MsgBox strCMD

  When the dialog pops up send me exactly what it says, you need to be accurate.

  
  
  

• Yanick

  Derek: This is looking much better. It will run from the C:\ drive or D:\ drive which is sweet.

  I have a question about the validate date code. The form has an input mask on the textboxes to make sure that the dates are input correctly in terms of data type, etc. When I ran the program with two dates very close together in the summer (low enrollment) the Excel sheet loaded with headers and no data (which is correct).

  However when I entered a date in the "From" textbox that was later than the "To" textbox (which should be invalid, I think), the spreadsheet filled with data between the two dates without regard for which should come first. For example:

  From: 6/6/2005

  To: 6/8/2004

  In terms of validation, does this matter I can imagine that the user might have mistyped the From intending it to be 2004 which would probably result in little or no records returned (for a period of a couple of days). But, in fact, the spreadsheet would contain almost a year's worth of records (as it did when I ran the test validation). I realize that the validation code you supplied was just a sample to get past the initial path coding problem. I just want to make sure I understand the validation process so that if I try changing it I will have a proper understanding of what I am trying to achieve. I am a Newbie at VB and this is all very interesting, but a little daunting too.

  Thanks again for your help.

  
  

• mminor8

  Yes, there is a query called "Enrollment Completion"

  I inserted your code as instructed and when the program ran the message box said:

  SELECT * FROM [Enrollment Completion] WHERE (((EnrollDate) BETWEEN #1/1/2005 And #1/1/2006#))

  There was an "OK" button which I clicked and the Excel spreadsheet began filling with data (they had all been blank before). So something good is happening, just a little inconvenient to have to click the button in the message box.

  
  

• S76

  I only get the full crash of Access when I change the directory path to C: and place the database file in a folder ("Applications") on the C: Drive. Then when I click on the button to open the form in the Switchboard I get the message box stating:

  Microsoft Access for Windows has encountered a problem an needs to close. We are sorry for the inconvenience.

  If I leave the code unchanged or open the file from another location, the program opens an empty spreadsheet in Excel with the debug message box that points to "If CN.State = 0 Then CN.Open"

  Here is the entire code for the form:

  Private Sub cmdExport_Click()

  Export2XL 1, "H:\Applications\AA92603.MDB", "[Enrollment Completion]"

  End Sub

  
  Public Function Export2XL(InitRow As Long, DBAccess As String, DBTable As String) As Long

  
  Dim CN As New ADODB.Connection ' Use for the connection string
  Dim cmd As New ADODB.Command ' Use for the command for the DB
  Dim rs As New ADODB.Recordset ' Recordset return from the DB
  Dim MyIndex As Integer ' Used for Index
  Dim MyRecordCount As Long ' Store the number of record on the table
  Dim MyFieldCount As Integer ' Store the number of fields or column
  Dim ApExcel As Object ' To open Excel
  Dim MyCol As String
  Dim Response As Integer
  Dim strCMD As String

  
  Set ApExcel = CreateObject("Excel.application") ' Creates an object

  ApExcel.Visible = True ' This enable you to see the process
  ' in Excel
  ApExcel.Workbooks.Add ' Adds a new book.

  ' Set the connection string
  CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBAccess
  
  ' Open the connection
  ' CN.Open

  ' Check that the connection is open
  If CN.State = 0 Then CN.Open
  Set cmd.ActiveConnection = CN
  
  ' Set up the criteria from the input
  ' selected on the form
  strCMD = "SELECT * FROM " & _
  DBTable & " WHERE (((EnrollDate) BETWEEN #" & _
  Me.txtEnrollFrom & "# And #" & _
  Me.txtEnrollTo & "#)"

  '--------------------------------------------
  ' For future implementation
  ' uncomment out the if else statement and
  ' make the line below a comment
  '--------------------------------------------
  strCMD = strCMD & ")"
  'If Not IsNull(Me.txtCompletedFrom) And _
  Not IsNull(Me.txtCompletedTo) Then
  ' strCMD = strCMD & " AND ((DateCompleted) Between # " & _
  Me.txtCompletedFrom & "# And #" & _
  Me.txtCompletedTo & "#))"
  
  'Else
  ' strCMD = strCMD & " AND (DateCompleted Is Null))"
  'End If
  '--------------------------------------------

  cmd.CommandText = strCMD
  cmd.CommandType = adCmdText
  Set rs = cmd.Execute
  
  ' Count the number of fields or column
  MyFieldCount = rs.Fields.Count

  ' Fill the first line with the name of the fields
  For MyIndex = 0 To MyFieldCount - 1
  '----------------------------------------
  ' Write Title to a Cell
  '----------------------------------------
  ApExcel.Cells(InitRow, (MyIndex + 1)).Formula = rs.Fields(MyIndex).Name
  ApExcel.Cells(InitRow, (MyIndex + 1)).Font.Bold = True
  ApExcel.Cells(InitRow, (MyIndex + 1)).interior.colorindex = 36
  ApExcel.Cells(InitRow, (MyIndex + 1)).WrapText = True
  Next

  'Draw border on the title line
  MyCol = Chr((64 + MyIndex)) & InitRow
  ApExcel.Range("A" & InitRow & ":" & MyCol).Borders.Color = RGB(0, 0, 0)
  MyRecordCount = 1 + InitRow

  'Fill the excel book with the values from the database
  Do While rs.EOF = False
  For MyIndex = 1 To MyFieldCount
  '------------------------------------
  ' Write Value to a Cell
  '------------------------------------
  If MyIndex = 1 Then
  ' Format as a date
  ApExcel.Cells(MyRecordCount, MyIndex).Formula = CDate(rs((MyIndex - 1)).Value)
  Else
  ApExcel.Cells(MyRecordCount, MyIndex).Formula = rs((MyIndex - 1)).Value
  End If
  ApExcel.Cells(MyRecordCount, MyIndex).WrapText = False 'Format the Cell
  Next
  MyRecordCount = MyRecordCount + 1
  rs.MoveNext
  ' for testing only display 50 records
  'If MyRecordCount > 50 Then
  ' Exit Do
  'End If
  Loop

  'Suggest to the user to save it's work
  Response = MsgBox("Save the Excel Sheet and clik OK", vbOKOnly, "Save your file")

  'Close the connection with the DB
  rs.Close
  CN.Close

  End Function
  

  
  
• VB Script Crashes Access When Exporting to Excel