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?
Bob Phillips
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
Bob Phillips
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
Bob Phillips
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.