Have Access open up a specific excel document

[hardhitter06]

Hey Guys,

Access 2003.

I have a database that consists of one table and a input form. At the end of each month, a specific user will have to publish all of the records to a website in a spreadsheet templet.

So I dunno if this is the most logical or not, but on my form, I have a button that runs the query so it's in spreadsheet form. I also tied in opening Excel itself so this one user can copy and paste into excel.

What would be perfect is for Access to open up the specific document so that this one user can just copy and paste without having to locate the specific document and open it.

What would be even greater though, is for this input form to store directly into an excel document so that all this sole user would have to do is save and publish the document.

Here is the code I have for this button:

rivate Sub Command13_Click()
On Error GoTo Err_Command13_Click

Dim stDocName As String
Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True

stDocName = "QryMain"
DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub

Let me know if this is possible or if there is a more logical idea out there..Thank you in advance.

 

[KenHigg]

Not sure if I missed something but it seems you could just do a DoCmd.OutputTo ?

 

[hardhitter06]

Where exactly would I put that, I'm not very good with coding...

 

[KenHigg]

In the button click event - ?

Here's a little help on it:

http://msdn.microsoft.com/en-us/library/aa220433(office.11).aspx

 

[hardhitter06]

Something like this...getting an error but trying to get closer step by step

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click

Dim stDocName As String

stDocName = "QryMain"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.OutputTo "Excel.Application", "Date", "Procurement#", "Vendor", "Amount", "SubstantialReasons" _
acFormatxls, "singlesource.xls", True

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub

 

[KenHigg]

I think the following is all you need:

DoCmd.OutputTo acOutputQuery, "QryMain", acFormatXLS

???

 

[hardhitter06]

Haha, I'm stupid. Yeah you're right, what that does is it automatically exports the qrytable, well, gets it ready for the export, and you just have to pick a file to save it too. Pretty simple.

 

[hardhitter06]

Do you know if there is a way to put a password on this button, because I only want one user to be able to click and run the command?

 

[hardhitter06]

Nice, figured it out with this code

Private Sub Command13_Click()

'Attached to On Click event of cmdOpenEmpForm

Dim strPasswd

strPasswd = InputBox("Enter Password", "Restricted Form")

'Check to see if there is any entry made to input box, or if
'cancel button is pressed. If no entry made then exit sub.

If strPasswd = "" Or strPasswd = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Data"
Exit Sub
End If

'If correct password is entered open Employees form
'If incorrect password entered give message and exit sub

If strPasswd = "Josh" Then
DoCmd.OutputTo acOutputQuery, "QryMain", acFormatXLS
Else
MsgBox "Sorry, you do not have access to this form", _
vbOKOnly, "Important Information"
Exit Sub
End If
End Sub

Thanks for your help KEN!!

 

[Mike375]

Do you know if there is a way to put a password on this button, because I only want one user to be able to click and run the command?

A failry simple way to do that in general is to put a condtion there that is based on a textbox entry. the entry being your password. Make it an unbound textbox and at the end of the code have the code the set the value of the next box to null.