How can you kill open file?

[Tarbuza]

I want to kill a local but open file from VBA so that I can copy an updated file in user's local hard drive from server.

Thanks in advance for any help.

 

[oharab]

You can't without closing it first.
What sort of file is it? Text, Mdb, xls?

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[Tarbuza]

It is mdb file. Let me clarify further. I have two mdb files x.mdb and y.mdb. From x.mdb, I go to y.mdb. While I am in y.mdb, I am trying to kill x.mdb but x.mdb is open. Is there a way to close x.mdb from y.mdb through code? Thanks in advance for any help.

 

[BillPower]

Hi,

This should close all Open instances of Access other than the one it is being called from.

Paste this into a New Standard Module, save the Module as anything you like:

Declare Function EnumWindows Lib "user32.dll" (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long
Declare Function GetWindowTextLength Lib "user32.dll" Alias "GetWindowTextLengthA" (ByVal hWnd As Long) As Long
Declare Function GetWindowText Lib "user32.dll" Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Declare Function GetActiveWindow Lib "user32.dll" () As Long
Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal hWnd As Long, ByVal Msg As Long, wParam As Any, lParam As Any) As Long

Public Const WM_CLOSE = &H10
Global ghWnd As Long
Global gCurrenthWnd As Long

Public Function EnumWindowsProc(ByVal hWnd As Long, ByVal lParam As Long) As Long
On Error Resume Next
Dim slength As Long, TitleBar As String
Dim retval As Long
Static winnum As Integer
winnum = winnum + 1
slength = GetWindowTextLength(hWnd) + 1
If slength > 1 Then
TitleBar = Space(slength)
retval = GetWindowText(hWnd, TitleBar, slength)
If InStr(TitleBar, &quot;Microsoft Access&quot And gCurrenthWnd <> hWnd Then
If hWnd <> ghWnd Then
Call SendMessage(hWnd, WM_CLOSE, 0&, 0&)
End If
End If
End If
EnumWindowsProc = 1
End Function

To test/use it put this in the On Click event of a Button while you have other Access DBs open:

gCurrenthWnd = GetActiveWindow
Call EnumWindows(AddressOf EnumWindowsProc, 0)

Just put this together, can't guarantee it will work, but logically it should.

Good Luck

Bill

 

[SgtJarrow]

While bill's code will do the trick, one question:

Do you open y.mdb from x.mdb....if so, in the code in x.mdb that opens y.mdb, add the following line after the line that opens y.mdb:

DoCmd.Quit

This will close the x.mdb database...after y.mdb has been opened.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[Tarbuza]

Thanks Bill and Robert.

I tried Bill's code but it is not closing instance. I checked line by line. Everything seems to be working except Call SendMessage(hWnd, WM_CLOSE, 0&, 0&). This has no effect.

I tried Robert's DoCmd.Quit but it is closing everything including y.mdb.

Any other ideas?

 

[Tarbuza]

Bill,

Sorry your code works. I made a typo mistake in Declare statement. It's working now. thanks a million.

 

[JoanneM]

Tarbuza

where was your typo

& Bill What version was this for

regards

Jo

 

[BillPower]

Hi JoanneM,

This should work for Access 97, 2000 and above, also Excel and Word, basically any application that supports VB or VBA.

Bill

 

[JoanneM]

BillPower

97 IS NOT COMPATIBLE WITH THIS CODE
below is copied from a website at the following URL

That star wasn't earned well - you didn't do your homework
when I asked about the versions.

http://www.mvps.org/access/api/api0031.htm

Warnings:

AddressOf is COMPLETELY UNSUPPORTED by Microsoft in Office 97 environment. Use it at your own risk!!

Entering debug mode is not recommended as it is likely to cause problems (GPFs etc.).

Make sure you backup your work and save before running any such code. Using this technique adds another level of instability since there are so many different ways to set up things wrong. Once you get it to work properly, everything should be ok.

Make sure you enter a On Error Resume Next at the top of any callback function. This is done to ensure that any errors within a callback function are not propagated back to its caller.

Be careful of ByVal or ByRef when passing arguments to the function. If you don't get this right, nothing's going to work.