Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002

    Question Unanswered: GetObject - database placed in a state

    I am trying to open and manipulate an Excel file from MS Access. I have used the GetObject code in Help which looks for a running instance of Excel and opens it if it's not open or uses the current instance if it is available.

    I just want to open an Excel file which has some code to refresh links and formatting and then save it.

    However, I keep getting an error message 'The database has been placed in a state by user 'Admin' on machine x that prevents it from being opened or locked'.

    MS KB suggest this is caused by repeatedly opening and closing Access but I'm not doing this. Any ideas please?

    I've set the parameter in MyXL.Parent.Windows(2).Visible = True to 2 because when Excel opens it automatically opens a hidden file Personal.xls. However, I suspect my solution only works if only two files are open. Can I change the parameter from a number to the name of the file e.g. MyXL.Parent.Windows("Test.xls").Visible = True?

    ' Declare necessary API routines:
    Declare Function FindWindow Lib "user32" Alias _
    "FindWindowA" (ByVal lpClassName as String, _
    ByVal lpWindowName As Long) As Long

    Declare Function SendMessage Lib "user32" Alias _
    "SendMessageA" (ByVal hWnd as Long,ByVal wMsg as Long, _
    ByVal wParam as Long, _
    ByVal lParam As Long) As Long

    Sub GetExcel()
    Dim MyXL As Object ' Variable to hold reference
    ' to Microsoft Excel.
    Dim ExcelWasNotRunning As Boolean ' Flag for final release.

    ' Test to see if there is a copy of Microsoft Excel already running.
    On Error Resume Next ' Defer error trapping.
    ' Getobject function called without the first argument returns a
    ' reference to an instance of the application. If the application isn't
    ' running, an error occurs.
    Set MyXL = Getobject(, "Excel.Application")
    If Err.Number <> 0 Then ExcelWasNotRunning = True
    Err.Clear ' Clear Err object in case error occurred.

    ' Check for Microsoft Excel. If Microsoft Excel is running,
    ' enter it into the Running Object table.

    ' Set the object variable to reference the file you want to see.
    Set MyXL = Getobject("c:\TEST.XLS")

    ' Show Microsoft Excel through its Application property. Then
    ' show the actual window containing the file using the Windows
    ' collection of the MyXL object reference.
    MyXL.Application.Visible = True
    MyXL.Parent.Windows(1).Visible = True

    With MyXL
    .Application.Visible = True
    .Parent.Windows(2).Visible = True

    With Active.Workbook

    .Close SaveChanges:=True
    End With

    ' ...
    ' If this copy of Microsoft Excel was not running when you
    ' started, close it using the Application property's Quit method.
    ' Note that when you try to quit Microsoft Excel, the
    ' title bar blinks and a message is displayed asking if you
    ' want to save any loaded files.
    If ExcelWasNotRunning = True Then
    End IF

    Set MyXL = Nothing ' Release reference to the
    ' application and spreadsheet.
    End Sub

    Sub DetectExcel()
    ' Procedure dectects a running Excel and registers it.
    Const WM_USER = 1024
    Dim hWnd As Long
    ' If Excel is running this API call returns its handle.
    hWnd = FindWindow("XLMAIN", 0)
    If hWnd = 0 Then ' 0 means Excel not running.
    Exit Sub
    ' Excel is running so use the SendMessage API
    ' function to enter it in the Running Object Table.
    SendMessage hWnd, WM_USER + 18, 0, 0
    End If
    End Sub

  2. #2
    Join Date
    Aug 2002

    Cool Re: GetObject - database placed in a state

    Try this.

    Public Sub GetExcel()
    On Error GoTo Proc_Err
        Dim MyWS As Object
        Dim MyXL As Object
        ' Set the object variable to reference the file you want to see.
        Set MyWS = GetObject("C:\test.xls")
        Set MyXL = MyWS.Application
        With MyXL
            .Application.Visible = True
  "test.xls").Visible = True
            .windows("test.xls").Close SaveChanges:=True
        End With
        If Not MyXL Is Nothing Then
        End If
        Set MyXL = Nothing
        Set MyWS = Nothing
        Exit Sub
        MsgBox Err.Description, vbOKOnly, "Error: " & Err.Number
        Resume Proc_Exit
    End Sub

  3. #3
    Join Date
    Oct 2002
    Leicester - UK
    if you just want to open it and nothing else you could try the very simple

                With ' link is a hidden txt lable
                    .HyperlinkAddress = "./Investigation.doc"
                End With
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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