PDA

View Full Version : Solved: if file is open or if file exist



white_flag
01-17-2012, 07:57 AM
Hello I have the following code (create an workbook):


Private Sub CommandButton116_Click()
Dim wb As Workbook
Set wb = Workbooks.Add
Application.DisplayAlerts = False
wb.SaveAs FileName:=ThisWorkbook.Path & "\QUO-.xls"

Dim LastRow As Long
Set wb = Workbooks.Open(ThisWorkbook.Path & "\QUO-.xls")
With wb
'copy range
End With
wb.save
End Sub



I like to add the following: if the workbook exist to add an suffix?

xld
01-17-2012, 08:03 AM
Private Sub CommandButton116_Click()
Dim wb As Workbook
Dim filename As String
Dim LastRow As Long

filename = ThisWorkbook.Path & "\QUO-.xls"
On Error Resume Next
Set wb = Workbooks(filename)
On Error GoTo 0
If wb Is Nothing Then

Set wb = Workbooks.Add
Application.DisplayAlerts = False
wb.SaveAs filename
End If

With wb
'copy range
End With
wb.Save
End Sub

Kenneth Hobs
01-17-2012, 08:07 AM
You can use DIR() to determine if the file exits.

To create a unique sequential name, see: http://www.vbaexpress.com/kb/getarticle.php?kb_id=1041

white_flag
01-17-2012, 08:18 AM
Hello Bob

I mean an suffix for file name like: QUO-1, QUO-2 etc

white_flag
01-17-2012, 08:21 AM
Kenneth, thx for suggestion but that level of vba is to high for me

xld
01-17-2012, 08:41 AM
Kenneth, thx for suggestion but that level of vba is to high for me

No, it's easy



filename = Dir(ThisWorkbook.Path & "\QUO-.xls", vbNormal)
If filename = "" Then

MsgBox "not found"
Else

MsgBox "found"
End If

xld
01-17-2012, 08:41 AM
Hello Bob

I mean an suffix for file name like: QUO-1, QUO-2 etc

Not sure what you mean.

white_flag
01-17-2012, 08:47 AM
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1008

this is what I need, Bob thx for suggestions.. here, it is nice and cold