11-11-02, 09:40 #1Registered User
- Join Date
- Nov 2002
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
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
'MY CODE HERE
.Application.Visible = True
.Parent.Windows(2).Visible = True
' 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
Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.
' 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.
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
11-12-02, 16:18 #2Registered User
- Join Date
- Aug 2002
Re: GetObject - database placed in a state
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 .Parent.windows("test.xls").Visible = True '... .windows("test.xls").Close SaveChanges:=True End With If Not MyXL Is Nothing Then MyXL.Application.Quit End If Proc_Exit: Set MyXL = Nothing Set MyWS = Nothing Exit Sub Proc_Err: MsgBox Err.Description, vbOKOnly, "Error: " & Err.Number Resume Proc_Exit Resume End Sub
11-13-02, 05:29 #3Registered User
- Join Date
- Oct 2002
- Leicester - UK
if you just want to open it and nothing else you could try the very simple
With Me.link ' link is a hidden txt lable .HyperlinkAddress = "./Investigation.doc" .Hyperlink.Follow End WithDefinition of a Beginner, Someone who doesn't know the rules.
Definition of an Expert, Someone who knows when to ignore the rules.