Close an Excel File which is already opened using vb

Close an Excel File which is already opened using vb, While executing

Page 1 of 1

0 Replies - 4805 Views - Last Post: 29 April 2008 - 06:38 AM Rate Topic: -----

#1 srinivas.alwala  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 29-April 08

Close an Excel File which is already opened using vb

Posted 29 April 2008 - 06:38 AM

Private Sub Form_Load()
Dim FSO As Object
	Set FSO = CreateObject("Scripting.FileSystemObject")
	Dim Fl As Object
	strFolder = "C:\Documents and Settings\DMISAdmin\Desktop\"  ' path remains same always.

	strfile = "tst.txt"  ' filename remains same always.
	Do While strfile <> ""
		If strfile <> "." And strfile <> ".." Then
			Set Fl = FSO.GetFile(strFolder & "\" & strfile)
			MsgBox "File name is: " & strfile	'<-- works okay
			MsgBox "Date created is: " & Fl.DateCreated  '<-- works okay
			MsgBox "Date last modified is: " & Fl.DateLastModified  '<-- works okay
			
		   ' following line works okay so long as the file is opend on the local drive
		   ' is there someting like .ldb is for .mdb files to detect
		   ' this (whether it is open and the current user)
			MsgBox "Filecurrently is open: " & WorkbookIsOpen(strfile)   '<-- Need help *****
			MsgBox "Current User is: " & "????"	 '<-- Need help *****
			Set Fl = Nothing
		End If
		strfile = Dir()
	Loop
	Set FSO = Nothing
	
'This is the WorkbookIsOpen() function....
End Sub

Private Function WorkbookIsOpen(wbName) As Boolean
   '   Returns TRUE if the workbook is open
   Dim x As Workbook
   On Error Resume Next
   Set x = Excel.Workbooks(wbName)  ' error here as subscript out of range.
   If Err.Number = 0 Then WorkbookIsOpen = True _
		Else WorkbookIsOpen = False
End Function




Is This A Good Question/Topic? 0
  • +

Page 1 of 1