Data not exporting to excel from access form.

Posted on 2011-04-27
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

Question by:jlcannon
    LVL 2

    Accepted Solution

    if it was already working and not now, then, should be any effect of upgradation of some application - Access/Excel

    also, check if you have the excel file in the concern path...

    otherwise, let me know what stage it stops working, support with some screenshot or files..

    LVL 26

    Expert Comment

    <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 Closing Comment

    Honestly its the simple things. someone had I am hoping inadvertantly deleted the blank excel template it called. thank you.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now