• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

Data not exporting to excel from access form.

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

1 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..

<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
jlcannonAuthor Commented:
Honestly its the simple things. someone had I am hoping inadvertantly deleted the blank excel template it called. thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now