Close CSV File using VBA
- Thread starter zezo2021
- Start date
- Local time
- Today, 15:31
- Joined
- Oct 29, 2018
- Messages
- 22,514
I guess therein lies the rub. You can't close a file. You'll have to close the application that opened it.
I found this code but did not close the file
Code:
Sub CloseCSV(CSVName As String)
Dim xl As Excel.Application
Dim wkbk As Excel.Workbook
Dim wk As Excel.Worksheet
'On Error Resume Next
Set xl = GetObject(, "Excel.Application")
On Error GoTo 0
xl.Visible = True
With xl
.Workbooks(CSVName).Close SaveChanges:=False ' Insert Excel code here
End With
Set wk = Nothing
Set wkbk = Nothing
Set xl = Nothing
End Sub
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Tomorrow, 06:31
- Joined
- May 7, 2009
- Messages
- 20,264
you can use API to close the program.
copy to New Module:
For example D:\myfile.CSV is open by excel or notepad,
to close it:
copy to New Module:
Code:
Option Explicit
' Module Name: ModFindWindowLike
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 02/06/2005
'
' mODIFIED bY aRNELgP FOR x64 aCCESS
'
#If VBA7 Then
Private Declare PtrSafe Function EnumWindows Lib "user32" (ByVal lpEnumFunc As LongPtr, ByVal lParam As LongPtr) As Long
Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
'Could use global variables instead, but this is nicer.
'Custom structure for passing in the parameters in/out of the hook enumeration function
'Could use global variables instead, but this is nicer.
Private Type FindWindowParameters
strTitle As String 'INPUT
hwnd As LongPtr 'OUTPUT
End Type
#Else
Private Declare Function EnumWindows Lib "user32" _
(ByVal lpEnumFunc As Long, _
ByVal lParam As Long) As Long
Private Declare Function GetWindowText Lib "user32" _
Alias "GetWindowTextA" _
(ByVal hwnd As Long, _
ByVal lpString As String, _
ByVal cch As Long) As Long
'Could use global variables instead, but this is nicer.
'Custom structure for passing in the parameters in/out of the hook enumeration function
'Could use global variables instead, but this is nicer.
Private Type FindWindowParameters
strTitle As String 'INPUT
hwnd As Long 'OUTPUT
End Type
#End If
'''experimental''''''''''''''''
Private Const WM_CHAR = &H102
Private Const BM_CLICK As Long = &HF5&
''' close the window
Private Const WM_SYSCOMMAND = &H112
Private Const SC_CLOSE = &HF060
Private Const WM_CLOSE = &H10
#If VBA7 Then
Private Declare PtrSafe Function SendMessageBynum Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long
#Else
private Declare Function SendMessageBynum Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As any) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If
'''''''''''''''''''''
#If VBA7 Then
Public Function FnFindWindowLike(strWindowTitle As String) As LongPtr
#Else
Public Function FnFindWindowLike(strWindowTitle As String) As Long
#End If
'We'll pass a custom structure in as the parameter to store our result...
Dim Parameters As FindWindowParameters
Parameters.strTitle = UCase(strWindowTitle) ' Input parameter
#If VBA7 Then
Call EnumWindows(AddressOf EnumWindowProc, VarPtr(Parameters))
#Else
Call EnumWindows(AddressOf EnumWindowProc, VarPtr(Parameters))
#End If
FnFindWindowLike = Parameters.hwnd
End Function
#If VBA7 Then
Private Function EnumWindowProc(ByVal hwnd As LongPtr, _
lParam As FindWindowParameters) As LongPtr
#Else
Private Function EnumWindowProc(ByVal hwnd As Long, _
lParam As FindWindowParameters) As Long
#End If
Dim strWindowTitle As String
strWindowTitle = Space(260)
Call GetWindowText(hwnd, strWindowTitle, 260)
strWindowTitle = UCase(TrimNull(strWindowTitle)) ' Remove extra null terminator
If strWindowTitle Like lParam.strTitle Then
lParam.hwnd = hwnd 'Store the result for later.
EnumWindowProc = 0 'This will stop enumerating more windows
Else
EnumWindowProc = 1
End If
End Function
Private Function TrimNull(strNullTerminatedString As String)
Dim lngPos As Long
'Remove unnecessary null terminator
lngPos = InStr(strNullTerminatedString, Chr$(0))
If lngPos Then
TrimNull = left$(strNullTerminatedString, lngPos - 1)
Else
TrimNull = strNullTerminatedString
End If
'Debug.Print TrimNull
End Function
Public Function killWindow(ByVal strWindowTitle As String)
#If VBA7 Then
Dim hwnd As LongPtr
#Else
Dim hwnd As Long
#End If
hwnd = FnFindWindowLike(strWindowTitle & "*")
If hwnd <> 0 Then
Call SendMessageBynum(hwnd, WM_CLOSE, 0, 0)
End If
End FunctionFor example D:\myfile.CSV is open by excel or notepad,
to close it:
Code:
Call killWindow("myfile")
oleronesoftwares
Passionate Learner
- Local time
- Today, 15:31
- Joined
- Sep 22, 2014
- Messages
- 1,160
Hi,
This code can close all open excel files from ms access, its been placed under the command button on click event
This code can close all open excel files from ms access, its been placed under the command button on click event
Code:
Private Sub Command14_Click()
Dim obj As Object
On Error GoTo ExitSub
Dim i As Integer
For i = 0 To 10000
Set obj = GetObject(, "Excel.Application")
obj.Quit
Next i
ExitSub:
End Sub
- Local time
- Today, 17:31
- Joined
- Feb 28, 2001
- Messages
- 30,104
There is an inherent problem in closing a file in the way you mentioned.
If Access didn't close the file then you don't own the file locks. Therefore, to close the file, you need to find the task that DOES own the file locks, and this usually is the task that opened the file. But it gets more complicated than that. You cannot open a session of the correct utility program to close the file because that NEW SESSION doesn't own the file locks either. So just using Notepad or Excel or Wordpad or Access to close the file won't help.
The only solution, as demonstrated by arnelgp's offering, is to find the session window that has the file open and kill that session. Note, however, that IF you are referring to a file that is potentially being shared with another person at the time AND that person is editing the file, that you will abort that person's edit, thus leading to potential data loss - IF the system even allows you to touch the session in question owned by that possible other person.
Windows is not oriented towards allowing you to interfere with another user's process. Some years (actually, decades) ago, Microsoft adopted some rules to satisfy U.S. Government security requirements for the (then so-called) C2 level of security. This was done because if it had not been done, we would all be running UNIX systems now. Windows would have been disqualified from future government sales.
One of the rules for C2 was "strict user session isolation." IF you have admin-level privileges, you MIGHT be able to perform this type of task control. However, if you are an ordinary user (with respect to privilege level), you cannot do this. Windows will enforce the logic barrier that exists between processes created by different users. In fact, if you are an ordinary user, you might not even be able to SEE the other sessions.
If and only if another task window that YOU opened is the one that currently has the file open, you might get away with this. Otherwise, the answer is "you can't if you have neither privilege at task-control level nor ownership of the file or session."
If Access didn't close the file then you don't own the file locks. Therefore, to close the file, you need to find the task that DOES own the file locks, and this usually is the task that opened the file. But it gets more complicated than that. You cannot open a session of the correct utility program to close the file because that NEW SESSION doesn't own the file locks either. So just using Notepad or Excel or Wordpad or Access to close the file won't help.
The only solution, as demonstrated by arnelgp's offering, is to find the session window that has the file open and kill that session. Note, however, that IF you are referring to a file that is potentially being shared with another person at the time AND that person is editing the file, that you will abort that person's edit, thus leading to potential data loss - IF the system even allows you to touch the session in question owned by that possible other person.
Windows is not oriented towards allowing you to interfere with another user's process. Some years (actually, decades) ago, Microsoft adopted some rules to satisfy U.S. Government security requirements for the (then so-called) C2 level of security. This was done because if it had not been done, we would all be running UNIX systems now. Windows would have been disqualified from future government sales.
One of the rules for C2 was "strict user session isolation." IF you have admin-level privileges, you MIGHT be able to perform this type of task control. However, if you are an ordinary user (with respect to privilege level), you cannot do this. Windows will enforce the logic barrier that exists between processes created by different users. In fact, if you are an ordinary user, you might not even be able to SEE the other sessions.
If and only if another task window that YOU opened is the one that currently has the file open, you might get away with this. Otherwise, the answer is "you can't if you have neither privilege at task-control level nor ownership of the file or session."
