Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Access Report in Excel (2002 - SP2)

    Is there a way to open an Access database form in Excel and pass a value to it so that a text box is populated with that value (similar to form openArgs argument in the form Open process)?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    The subject of your post mentions "Access Report", but the body is about an "Access database form". Which one do you mean?

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    Hans,

    The database report I'm trying to run is in Access and is presently produced by entering the account number you wish to report in a text box and the from triggers the report. I'm trying to bypass that step by having Excel pass the value to the form.

    So, what I really am trying to do is have Excel start up Access and pass a value to a form. Once I get the data to the form, I'll have the from trigger the report.

    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    You can use Automation to start Access and open the report. Here is code to open a database named Test.mdb, open a report rptCustomers in preview mode, and set a where-condition using a cell value:

    Sub Test()
    Dim objAcc As Object

    On Error GoTo ErrHandler

    Set objAcc = CreateObject("Access.Application")
    With objAcc
    .OpenCurrentDatabase "Test.mdb"
    .DoCmd.OpenReport "rptCustomers", 2, , "strCountry=" & _
    Chr(34) & ActiveSheet.Range("A1") & Chr(34)
    .DoCmd.Maximize
    .Visible = True
    End With

    ExitHandler:
    Set objAcc = Nothing
    Exit Sub

    ErrHandler:
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    Resume ExitHandler
    End Sub

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    Han,

    Thanks for that code. When I try to run it in Excel, the program seems to pause and then I get a message box with the message "Microsoft Excel is waiting for another application to complete an OLE Action."

    Clicking OK causes Excel to repeat this message again.

    I Appreciate the help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    Perhaps the record source of the report has a parameter?

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    I think that is what he ment when he said he wanted to open an Access form and fill in a text box. Filling in the text box was to pass the parameter required by the report.
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    Yes, but there are various ways to do that in Access - using the text box as a parameter in a query, or passing a where-condition to the report.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    The way I read the original message is that he has an Access data base that has a form with a text box. You enter a parameter in the text box, and probably push a button to get the report based on that parameter that he wants to pop up from VBA in Excel.
    Legare Coleman

  10. #10
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    Hans,

    I am stepping through the code line by line and the error "Microsoft Excel is waiting for another application to complete an OLE Action" appears while Excel is trying to execute the line about opening the database. The database never opens.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    So the error occurs on the line .OpenCurrentDatabase "Test.mdb" (with the appropriate path/filename substituted)?

    Is your database password protected?
    Does your database have something in its startup options (Tools | Startup... in Access), or an AutoExec macro?

  12. #12
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    Hans,

    Yes, the error occurs on the line .OpenCurrentDatabase "Test.mdb" .

    You were correct in assuming password and security parameters were hindering the opening of the db.

    I created a Test..mdb in the generic workgroup and it opened and worked fine.

    So now the question is how do I pass user and password information to the db while I'm in the secure workgroup? I didn't see anything about that in the docs for the CreateObject command.

    Thanks for the help.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    MSKB article ACC2000: Using Microsoft Access as an Automation Server (valid for Access 2002 too) explains how to use Shell in combination with GetObject instead of CreateObject to open a secured database. See the section Using a Secured Workgroup (it's quite a way down from the top.)

  14. #14
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    Hans,

    Read the MSKB article: Using the code below the db opens fine but now I can't figure out how to pass parameters to it - my original need.
    I'll send two examples of code I've tried - the first from the article and the second similar to your first example.

    Public Sub Test(Optional varUser As Variant, Optional varPW As Variant)
    Dim cmd As String
    On Error Resume Next
    Set objAccess = GetObject(, "Access.Application")

    If Err <> 0 Then
    If IsMissing(varUser) Then varUser = "TestAdmin"
    cmd = "C:Program FilesMicrosoft OfficeOffice10MSACCESS.EXE" & " C:Test.mdb"
    cmd = cmd & " /nostartup /user " & varUser
    If IsMissing(varPW) Then
    cmd = cmd & " /pwd " & "TestPW"
    End If
    Shell pathname:=cmd, windowstyle:=3

    Do
    Err = 0
    Set objAccess = GetObject(, "Access.Application")
    Loop While Err <> 0
    End If

    End Sub

    Using this code - similar to the one you gave me previously, I'm able to run a report but I am required to enter the password.
    Dim objAccess As Object
    On Error GoTo Test2_ErrorHandler
    Err = 0
    Set objAccess = CreateObject("Access.Application")
    With objAccess
    .opencurrentdatabase filepath:="C:Test.mdb"
    .docmd.openreport reportname:="Test"
    End With
    Set objAccess = Nothing
    Exit Sub
    Test2_ErrorHandler:
    Stop

    ============

    Thanks for the help,

  15. #15
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Access Report in Excel (2002 - SP2)

    Hans,

    I further modified the code to have it run a macro on opening the db.

    This allows the report to run while bypassing the password login dialog. I'm almost there!

    Public Sub Test(Optional varUser As Variant, Optional varPW As Variant)
    Dim cmd As String, intCycle As Integer
    On Error Resume Next
    Set objAccess = GetObject(, "Access.Application")
    Err = 1
    If Err <> 0 Then
    If IsMissing(varUser) Then varUser = "TestAdmin"
    cmd = "C:Program FilesMicrosoft OfficeOffice10MSACCESS.EXE" & " C:Test.mdb"
    cmd = cmd & " /nostartup /user " & varUser
    If IsMissing(varPW) Then
    cmd = cmd & " /pwd " & "TestPW"
    End If
    cmd = cmd & " /x PrintAccount" ' Passing Macro Name
    Shell pathname:=cmd, windowstyle:=3
    Do
    Err = 0
    Set objAccess = GetObject(, "Access.Application")
    Loop While Err <> 0
    End If

    End Sub

    Now, I only need to know how to pass a "where" value with the Shell command.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •