• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

Avoid problem that file is already existing

Hi,
I want to save the current newly created Excel file like

wbTrg.SaveAs ThisWorkbook.Path & "\Departure List (of People already left).xlsx"

Open in new window


How to avoid the problem that, Departure List (of People already left).xlsx, maybe is already being opened?
0
HuaMin Chen
Asked:
HuaMin Chen
  • 4
  • 3
2 Solutions
 
Roy CoxGroup Finance ManagerCommented:
Here's a Function that will check if a file or folder exists, I think this was written by Ken Puls

Public Function FileFolderExists(sFullPath As String) As Boolean

    If Not Dir(sFullPath, vbDirectory) = vbNullString Then FileFolderExists = True

End Function

Public Sub TestFolderExistence()
 
    If FileFolderExists("C:\Accounts") Then
        MsgBox "Folder exists!"
    Else: MkDir ("C:\Accounts")
    End If

End Sub

Public Sub TestFileExistence()

    If FileFolderExists("C:\Accounts\TestWorkbook.xls") Then
        MsgBox "File exists!"
    Else
        MsgBox "File does not exist!"
    End If

End Sub

Open in new window


Or
Option Explicit

''///Check if the required excel files exist.
Private Sub ChkExcelFiles()
    Dim filPath As String, filName As String

    filName = "MyWorkBook"
    filPath = Dir(ThisWorkbook.Path & Application.PathSeparator & filName)

    ''///If there are no Excel files exit the sub
    If filPath = "" Then MsgBox "File not found - " & filName, vbCritical
  
End Sub

Open in new window

0
 
HuaMin ChenSystem AnalystAuthor Commented:
Hi,
Your details is to see if relevant file is existing or not, right? How to detect if relevant file IS BEING opened?
0
 
Roy CoxGroup Finance ManagerCommented:
Try this, Im going out for a while but I'll check back later

Option Explicit
Function wbOpen(wbName As String) As Boolean
' returns TRUE if the workbook is open
    wbOpen = False
    On Error GoTo wbNotOpen
    If Len(Application.Workbooks(wbName).Name) > 0 Then
        wbOpen = True
        Exit Function
    End If
wbNotOpen:
End Function


Sub TestOpen()
If Not wbOpen("Suppliers.xls") Then    Workbooks.Open "Suppliers.xls"

End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HuaMin ChenSystem AnalystAuthor Commented:
I put your event, and it cannot detect that Excel is being opened, by these codes

            Dim Fl0 As String: Fl0 = ThisWorkbook.Path & "\Departure List (of People already left).xlsx"
            If wbOpen(Fl0) Then
                MsgBox "Please close your current open file (Departure List (of People already left).xlsx) and then re-try.", vbOKOnly + vbExclamation
                Exit Function
            End If

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
I have you included the Function in your code?

Try it withount the path as I have
0
 
Roy CoxGroup Finance ManagerCommented:
Looks like it worked. Pleased to help.
0
 
HuaMin ChenSystem AnalystAuthor Commented:
Appreciated!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now