scottL
asked on
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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
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
ASKER
That did the trick, thanks very much for your help!!
ASKER
If i = 5 Then
MsgBox "The name you entered was not found. Try again."
End If
What do you think? Thanks for your help!