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: 406
  • Last Modified:

Access automation

I have an Access application that consists of multiple tables/queries and reports.  

I would like to add a command button to my Access form that allows the user to enter the name of a query or report to be analyzed with Excel.  Upon entering the query or report name, Excel would be launched and the new Excel file opened for analysis.

Basically I want to automate the following steps into a command button:

1. Use the database window to select the table, query, form,    or report to save & load into MS Excel
2. Select the Tools menu, point to Office Links, and click
   "analyze it with MS Excel"

Does anybody have some VB code I could put in a command button to accomplish this?

Thanks!
0
scottL
Asked:
scottL
  • 4
  • 2
1 Solution
 
dovholukCommented:
This should work for you. If not let me know and i'll tweak it.  If i could have sent you an attachment it would have been easier to get the answer to you.  Just make a button with the 'onclick' property set to "=ExportToExcel()"
Function ExportToExcel()
On Error GoTo ErrorHandler
Dim exportname As String, i As Integer, a(5) As Integer
Dim t As TableDef, q As QueryDef, r As Report
i = 0
exportname = InputBox("Please enter the object name:", "Analyze with Excel")
If exportname = "" Then
    MsgBox "Nothing entered. Quitting"
    Exit Function
End If
Set t = CurrentDb.TableDefs(exportname)
i = i + 1
Set q = CurrentDb.QueryDefs(exportname)

If a(0) = 0 Then
    DoCmd.OpenTable exportname
    DoCmd.RunCommand acCmdOutputToExcel
    DoCmd.Close
    Exit Function
End If
If a(2) = 0 Then
    DoCmd.OpenQuery exportname
    DoCmd.RunCommand acCmdOutputToExcel
    DoCmd.Close
    Exit Function
End If
If i = 3 Then
    DoCmd.OpenReport exportname, acViewPreview
    DoCmd.RunCommand acCmdOutputToExcel
    DoCmd.Close acReport, exportname
End If
If i = 5 Then
    MsgBox "The name you entered was not found. Try again."
End If
Exit Function
ErrorHandler:
a(i) = 1
i = i + 1
Resume Next
End Function
0
 
scottLAuthor Commented:
This works great!  The only problem I'm having is that an invalid object entered still results in Excel being launched.  It doesn't seem to fall into the logic:

If i = 5 Then
 MsgBox "The name you entered was not found. Try again."
End If

What do you think?  Thanks for your help!
0
 
dovholukCommented:
sorry about that. here you go.  edit the last few lines so they read like this instead.
this should work well

have a good one!

If i = 3 Then
    DoCmd.OpenReport exportname, acViewPreview
    If i = 4 Then
        MsgBox "The name you entered was not found. Try again."
        Exit Function
    End If
    DoCmd.RunCommand acCmdOutputToExcel
    DoCmd.Close acReport, exportname
End If
Exit Function
ErrorHandler:
a(i) = 1
i = i + 1
Resume Next
End Function
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
dovholukCommented:
sorry about that. here you go.  edit the last few lines so they read like this instead.
this should work well

have a good one!

If i = 3 Then
    DoCmd.OpenReport exportname, acViewPreview
    If i = 4 Then
        MsgBox "The name you entered was not found. Try again."
        Exit Function
    End If
    DoCmd.RunCommand acCmdOutputToExcel
    DoCmd.Close acReport, exportname
End If
Exit Function
ErrorHandler:
a(i) = 1
i = i + 1
Resume Next
End Function
0
 
dovholukCommented:
sorry about that. here you go.  edit the last few lines so they read like this instead.
this should work well

have a good one!

If i = 3 Then
    DoCmd.OpenReport exportname, acViewPreview
    If i = 4 Then
        MsgBox "The name you entered was not found. Try again."
        Exit Function
    End If
    DoCmd.RunCommand acCmdOutputToExcel
    DoCmd.Close acReport, exportname
End If
Exit Function
ErrorHandler:
a(i) = 1
i = i + 1
Resume Next
End Function
0
 
scottLAuthor Commented:
That did the trick, thanks very much for your help!!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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