Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • 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_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

0
jlcannon
Asked:
jlcannon
1 Solution
 
balatheexpertCommented:
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..

thanks
0
 
Nick67Commented:
<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
0
 
jlcannonAuthor Commented:
Honestly its the simple things. someone had I am hoping inadvertantly deleted the blank excel template it called. thank you.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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