We help IT Professionals succeed at work.

Data not exporting to excel from access form.

Last Modified: 2012-05-11
I have a form and on this form is a button that will take the data input above and export it to excel. the code for the button is the first peice of code listed. and the routine called by the button code is the second. this code has worked everyday for over 3 years and now suddenly quit. any ideas why?
Private Sub Command36_Click()
On Error GoTo Err_Command36_Click

Call CreateInputWB(Me.Plant, Me.Business, Me.Location, Me.Site, Me.Catagory)
    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command36_Click
End Sub


Public Sub CreateInputWB(Plant As String, Business As String, Location As String, Site As String, Category As Integer)
On Error GoTo Err_Handler

    Dim MYXL As Object
    Dim ReportName As String
    'Check to see if Excel is open.
    'And tell user if it is.
    'Excel being open causes problems for the database.
    On Error Resume Next
    Set MYXL = GetObject(, "Excel.Application")
    If Err.Number = 0 Then
        MsgBox "Please Close Excel."
        Exit Sub
    End If
    Set MYXL = GetObject("\\txnt38\puwtc\5-Point\Database\ESRA Database Input Form.xls")
    MYXL.Application.Visible = True
    MYXL.Parent.Windows(1).Visible = True
    With MYXL.Worksheets("Plant_Information")
        .Cells(3, 3).Value = Plant
        .Cells(4, 3).Value = Business
        .Cells(5, 3).Value = Location
        .Cells(6, 3).Value = Site
        .Cells(7, 3).Value = Category
    End With
   'ReportName = "s:\my documents\" & Plant & " - " & Year(Now()) & " ESRA Form.xls"
    ReportName = "C:\ESRA\" & Plant & " - " & Year(Now()) & " ESRA Form.xls"
    MYXL.Application.ActiveWorkbook.SaveAs (ReportName)
    Set MYXL = Nothing
    MsgBox "Saved workbook as " & ReportName
    Exit Sub

    MsgBox Err.Description
End Sub

Open in new window

Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
Most Valuable Expert 2014

<now suddenly quit.>
Define that.
Doesn't create a file?
Doesn't exit cleanly?
Something else?

First, comment out your error handler
'On Error GoTo Err_Handler

Next, throw a breakpoint on every single line of code.
Run it.
F5 every single breakpoint, and tell us where the code stops abruptly or throws an error.

For the hell of it run a Compact and Repair, too


Honestly its the simple things. someone had I am hoping inadvertantly deleted the blank excel template it called. thank you.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.