We help IT Professionals succeed at work.

Data not exporting to excel from access form.

205 Views
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_Command36_Click:
    Exit Sub

Err_Command36_Click:
    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."
        Err.Clear
        Exit Sub
    Else
        Err.Clear
    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")
        .Select
        .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)
    MYXL.Application.Quit
    Set MYXL = Nothing
    MsgBox "Saved workbook as " & ReportName
    Exit Sub

Err_Handler:
    MsgBox Err.Description
End Sub

Open in new window

Comment
Watch Question

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

Commented:
<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

Author

Commented:
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.

OR

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.