Free Microsoft Excel 2013 Quick Reference

Does File / Workbook Exists

I'd appreciate some help with a macro to check if a file exists before saving. The code saves the file OK if the filename does not exist but fails if it does.

	VB:
	
 CheckFileName() 
    Dim FName As String 
    FName = ThisWorkbook.Name 
     
     'saves file
    Dim FPath As String 
    FPath = ThisWorkbook.Path & "" 
    Dim y As String 
     
    y = InputBox("Please give this file a name then click 'OK'.", "SET NEW FILE NAME") 
    If y = "" Then 
        MsgBox "Your file has not been saved. Use 'Save' to save the file with its original name, or 'Save as' to save with a new name and leave the original file unchanged.", vbOKOnly, "ERROR - file not saved" 
        Exit Sub 
    End If 
     
    If Dir(FPath, vbDirectory)  "y" Then 
        ActiveWorkbook.SaveAs Filename:=FPath & y 
        MsgBox "Your file has been saved.", vbOKOnly, "FILE SAVED AS: " & y 
    Else 
        MsgBox "That filename already exists!" 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Many thanks


Post your answer or comment

comments powered by Disqus
I have a routine as part of a large macro that takes the current open sheet and saves it as a text file. I included error handling in the event that the file already exists and the user does not want to replace the file so that a save as window will come up to select a new name. This code works perfectly unless the new name that is chosen is not unique and the user is once again prompted to chose to replace the file. If the user choses no at this point, an error occurs because it is already in the error handling statement. Is there a way i can make a loop so that no matter how many times the user choses a name and location that already exists, the save as box will continue to appear until a unique name is picked or they decide to overwrite another file. I included my code and perhaps there is a way without even using error handling that im not aware of. There is other code in there for other parts of the macro that you can ignore.

Code:
 
  Workbooks(sFilename).Activate
 
  Application.DefaultSaveFormat = xlTextPrinter
  On Error GoTo Incomplete
  ActiveWorkbook.SaveAs FileName:=Location & "" & sFilenametxt, FileFormat:=xlTextPrinter, CreateBackup:=False
 
  Application.DisplayAlerts = False
  ActiveWorkbook.Close
  Application.DisplayAlerts = True
  Kill "c:print.xls"
 
  ThisWorkbook.Worksheets("Input").Activate
  Range("b14") = Location & "" & sFilenametxt
  Exit Sub
 
Incomplete:
  ChDir (Location)
  FName = Application.GetSaveAsFilename(sFilenametxt, "Text Files (*.txt), *.txt")
  If FName = False Then
      MsgBox "Head File Not Created", vbCritical, "Head Create"
      HeadComplete.Incomplete
  Else
      ActiveWorkbook.SaveAs FileName:=FName, FileFormat:=xlTextPrinter, CreateBackup:=False
  End If
  Application.DisplayAlerts = False
  ActiveWorkbook.Close
  Application.DisplayAlerts = True
  Kill "c:print.xls"
 
  ThisWorkbook.Worksheets("Input").Activate
  Range("b14") = Location & "" & FName
 
End Sub
Thanks in advance,

Adam

I have code that grabs a range form one workbook and copies it to another.

I would like to add something to it that would check if the file exists.
If workbook exists, import the said range, If it does not, show some kind of message and exit.

Thank you.

Below is the code i want to modify.

Code:
Sub Import_BRADFORD()
'
' import_BRADFORD Macro
' Macro recorded 1/30/2008 by wwrobel
'

'
    myMSG = "This will clear the data and import information for Flex-N-Gate Bradford, Do you want to continue?"
    Response = MsgBox(myMSG, vbExclamation + vbYesNo, myTitle)
    Select Case Response
        Case Is = vbYes
            Range("A5:BM100").Select
            Selection.ClearContents
            ChDrive ActiveWorkbook.Path
            ChDir ActiveWorkbook.Path
            Dir ("*.xls")
         
            Workbooks.Open Filename:= _
            "WHITECHAPEL BRADFORD 2008.xls"
            Range("A5:BM100").Select
            Selection.Copy
            Range("A3").Select
            Windows("Ray_Hawkins.xls").Activate
            Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    Application.DisplayAlerts = False
            Windows("WHITECHAPEL BRADFORD 2008.xls").Close [SaveChanges], False
            Range("A3").Select
    Application.DisplayAlerts = True
        Case Is = vbNo
            Exit Sub
    End Select
    
End Sub


When I delete a file does it still exist in some form in the computer that a
hacker
could possiblly open ?

Hello

We are copying some data into Excel (2000) via VBA code in an Access
database (2000). The general idea is we copy the data into an existing
template spreadsheet, and as such we first open the existing template, and
then do a SaveAs to generate the desired results spreadsheet, and then in
later code we start chucking the data into it and doing various things,
followed by the final save.

For some reason we are getting very *very* different speeds based on whether
the SaveAs is overwritting an existing file (fast) cf the SaveAs making a
brand new file. The speed differences are ~10times greater.

As an example if I had an existing file called c:ExamplemyFile.xls, the
VBA code would open the template, execute the .SaveAs method which posts a
warning checking we want to overwrite the existing file, then we do our
processing and data manipulation etc and do a final .Save and close. This
takes ~10secs.
If however we did not have an existing file, the VBA code would open the
template, execute the .SaveAs method which will not post a warning about
overwriting the existing file as the existing file does not exist, then we
do our processing and data manipulation etc and do a final .Save and close.
This takes ~100secs

There is NO difference in execution paths in the code based on whether the
files exist or not, the .SaveAs is executed regardless of whether the file
existed or not. The only perceivable difference is that Excel pops a
warning if the file already existed. The code is posted below

Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.WorkBooks.Open(templateFile)
xlApp.Calculation = xlManual
If bPassword Then
tThisPassWord = GThisPassWord
Else
tThisPassWord = ""
End If

With xlWorkbook
.SaveAs FileName:=ExportFileName, PassWord:=tThisPassWord
End With

<Copy our data over and do some processing and formatting etc>

With xlWorkbook
.Save
.Close
End With

We are also getting the very slow execution if we do the following,
*regardless* of whether the file existed previously or not:
xlApp.DisplayAlerts = False
With xlWorkbook
.SaveAs FileName:=ExportFileName, PassWord:=tThisPassWord
End With
xlApp.DisplayAlerts = True
So if we turn off alerts the code execution is slow as well...

Can anyone help us understand what the issue here is? At this stage we are
not looking for workarounds (such as not doing the first saveAs until the
end), we are looking to trace the cause so that we can eliminate it from
this and any other apps we have.

Please reply to group, email is bogus due to spam killing my NG email
address

Thanks for help
Andrew


	VB:
	
 Button1_Click() 
    Config = vbYesNo + vbQuestion + vbDefaultButton2 
    Ans = MsgBox("Crank it?", Config) 
    If Ans = vbYes Then 
        Workbooks.Open "C:boombox" & Format(Date, "dd.mm.YYYY") & ".xlsx" 
         
    ElseIf Ans = vbNo Then 
         
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hi,

I was wondering about a way to modify this code to either
1) Find out if the workbook exists, and if it doesn't, return an error
OR EVEN BETTER
2) If it doesn't, go back in time day by day until a workbook DOES exist.
i.e. Workbooks.Open "C:boombox" & Format(Date, "-1dd.mm.YYYY") & ".xlsx"
or
Workbooks.Open "C:boombox" & Format(Date, "-2dd.mm.YYYY") & ".xlsx"

THANK YOU

Dear All,

The following code (thanks Dave) isnt doing what I want it to do. I would like it that if the file existed it would move to section of the sub named continue, if the file doesnt exist it runs a seperate sub that creates a new one.
Any ideas?

fName = Range("F3").Value & ".xls"
With Application.FileSearch
.LookIn = "G:johnpaulpeterdata"
.FileName = fName
If .Execute > 0 Then
GoTo conTinue
Else
NewKpo
Exit Sub
End If
End With

Hi All

I run a workbook an I want to save it by it's date .
In Simple word :
If the workbook did'nt exist save it, or
it's already exist.

	VB:
	
 SaveWorkbook() 
     
    Dim strFile As String 
    Dim FileName As String 
    Dim year As String 
     
     
    FileName = "Sales" 
    year = Sheets("Values").Range("G2").Value 
    strFile = FileName & year ' For Example: Sales2007
     
    ChDir "C:Sales" 
    If Dir(strFile) = "" Then 
        ActiveWorkbook.SaveAs FileName:=strFile, FileFormat:=xlNormal 
    Else 
         ' Don't Accept saving and exit sub.
        MsgBox "File already exist " & strFile, vbInformation 
        Exit Sub 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a workbook that searches for a file and formats it. I want to be able to have it search, and if found, format (have that part down). I also want it to search. and if not found, just exit. I've tried a few different things, but when the file is not found, VBA debugs. What language should I be using? Here's what I have (that doesn't work if file is not found).

	VB:
	
 Search() 
    Workbooks.Open Filename:="Q:IPAYPreiPay Pilot Report.xls" 
    If error Then 
        Exit 
    Else 
        Windows("iPay Pilot Report.xls").Activate 'plus additional formatting

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Greetings,

new member here. After about a day and a half of wrecking my brain I have decided to ask for help.
In the following macro I'm importing an Excel Order Form, rename it to a common naming scheme and save it, strip out a page, save the page as a new workbook. The new workbook is then later imported into our accounting software.
Currently the file is saved with a dat/time stamp but I want to change that to a revision number. Not a problem. The problem is to create a loop that iterates through the file location and checks to see if the file already exists and then increments the Revision Number automatically.

Any and every help on this is greatly appreciated!

	VB:
	
 InitialFileSave() 
     '
     'InitialFileSave Macro
     'Macro for saving an incoming PS Order in an appropriate file format and location
     '
    Dim COIDInput As String 'Variable for the Comapny ID
    Dim ThisPath As String 'File path variable for the whole Order
    Dim ThisFile As String 'Variable for the file name
    Dim ThisPathDetail As String 'File path variable for the Detail sheet
    Dim Sourcewb As Workbook 'Variable for the source workbook
    Dim Destwb As String 'Variable for the destination workbook
    Application.DisplayAlerts = False 'Turn off alerts
    COIDInput = InputBox("Please enter the Company ID") 'Declare variable through user input
    ThisPath = "[URL]http://intranet/marketing/Sales[/URL] Order Forms/" 'Declare file path for the whole order
    ThisFile = COIDInput & "_" & Format(Now(), "mmddyyyy_hhmm_AMPM") 'declare file name
    ThisPathDetail = "[URL]http://intranet/marketing/Sales[/URL] Order Detail Sheets/" 'declare file path for detail sheet
     
     'Following checks for user cancelation and, if not canceled, saves the whole order with new file name in declared
location as an .xlsx.
     'Additional parameters are not required.
    If COIDInput = "" Then 
        Goto ErrorHandler 
    End If 
    ActiveWorkbook.SaveAs FileName:=ThisPath & _ 
    ThisFile, FileFormat:=51, Password:="", WriteResPassword:= _ 
    "", ReadOnlyRecommended:=False, CreateBackup:=True 
    Set Sourcewb = ActiveWorkbook 'set variable to the active workbook
    Destwb = Replace(Sourcewb.Name, ".xlsx", "") 'Strip the xlsx extension from the active workbook file name
     'The following deletes the Summary sheet from the active workbook and saves the remaining sheet as an Excel 5.0 in
declared location
     'with old filename and adds "DTL" to the file name.
    ActiveWorkbook.Worksheets("Summary").Delete 
    ActiveWorkbook.SaveAs FileName:= _ 
    ThisPathDetail & Destwb & "_DTL" & ".xls", _ 
    FileFormat:=xlExcel5 
    ActiveWorkbook.Close 'Closes the workbook
    Application.DisplayAlerts = True 'Turns alerts back on
    Exit Sub 
     'Message displayed if user cancels the process.
ErrorHandler: 
    MsgBox "Operation canceled by User" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Is it possible to have an excel file open only if a certain file(s) exists somewhere in the hard drive? For example, someone trys to open the Data.xls file. The VBA in file will check if say data.txt file exists in the C: directory. If it does then Data.xls will open and if it does not it will open say a blank worksheet or prompt the user that it cannot open.

I dont want the user to know that it is not opening because of a missing file.

I want to keep my excel files from being taken from say workstation A and taken to workstation B etc.. I only want them to run on certain machines. Thanks in advance.

When I open an excel spreadsheet I get an error that says:
Microsoft Visual Basic
File already exists

Does anyone know why? I get this message with every sheet I open. I'd like to know why it's doing it and how I can get rid of it. I did some research and found it's a trappable message, but I really don't know what that means.

Thank you

I am using a macro to populate an existing file and then password protect the
file. Since the file already exists, I get the do you want to save over the
existing file message. I do want to save over the existing file. When I
recorded the macro, it does not include the response to the save over the
existing file question. Is there code to make the macro save over the
existing file?

Thanks

We went through a desktop refresh last week, and now SOMETIMES (I haven't been able to discern a pattern yet) when I open a workbook I get an error message from Microsoft Visual Basic saying "File Already Exists". In the lower left corner of my status bar it says "Opening ATPVBAEN.XLA". Any idea of why I'm getting this error?

I am running a macro that incorporates numerous workbooks. Sometimes some of those workbooks referenced in the macro do not exist (depending on the situation). Can someone please help me with some simple code to check whether a workbook exists?

For example:

	VB:
	
 
     'Do an action
Else 
     'Do a different action
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks!

not save a file if file already exists...

i would like to not save a file if it already exists (i currently get a prompt Yes, No, Cancel)

and i'm using the following code:


	VB:
	
Atmt.SaveAsFile FileName 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
????

thank you.

Use:

Application.DisplayAlerts = False

at the beginning of the code to suspend Excel from displaying any type of
alerts while executing the macro. Use:

Application.DisplayAlerts = True

at the end of your code to reinstate the display of alerts.

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh

"Bob" > wrote in message
...
> Hi
> How can I bypass "File already exists would you like to overwrite" and
save file over old file?
>
> Thanks
> Bob

Hello -

I have a quick question: I use the ThisWorkbook.SaveAs function to
save a file but if the file already exists, Excel shows a warning. The
problem is that if I say either No or Cancel, there is an exception
thrown. How can I catch the result of that MsgBox()?

Thanks!
Joe

Hi, I am trying to do something very simple: code up a "Save as" dialog with
a default filename. The code below nearly works.

With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = "C:TempTest.xls"
If .Show = -1 Then
.Execute
End If
End With

The problem is that if the file already exists, the user is asked a "do you
wish to overwrite?" question *twice* - the first time by the dialog and the
second by VBA on the "Execute". If the user responds "yes" followed by "no",
they get a runtime error "Method 'Execute' of 'FileDialog' failed."
What am I doing wrong?
I am using Excel 2002 Sp-2

Thanks in advance
MattM

I have the letters A - O in rows 1 - 15 of column A on my Excel spreadsheet. However, instead of the following routine making subfolders under C:RESERVES I get the error "file already exists". Can someone help?

Thanks,

Jim

Sub createfolders()
'
' CreateFolders Macro
' Macro recorded 3/22/2006 by JBW
'
Dim cou As Integer, FolderStr As String, FileSys
For cou = 1 To ActiveSheet.UsedRange.Rows.Count
FolderStr = Format(Trim(Str(cou)), "00#") + "_" + ActiveSheet.Cells(cou, 1)
Set FileSys = CreateObject("Scripting.FileSystemObject")
FileSys.createfolder "C:RESERVES"
Next
End Sub

hello,
i want to verify if a workbook exist in a disk with a macro???

regardes

Using Excel 97 SR-1.
I am using ActiveWorkbook.SaveAs to save an ".xls" file and I cannot
find any documentation on how to handle file-already-exists errors.

My intent is to try and save the file but if the filename is already
used then to alter the filename and save again, repeating with
different filenames until either the SaveAs works or the code chooses
to give up. Currently, Excel pops up a message asking whether I want to
replace the file, after clicking "no" I get another popup asking
whether I want to debug the code.

I do not want any pop ups. I just want to handle the
file-already-exists case in the code.

Hessey,

Sincere thanks!!

That was quite helpful. It works now!!

I am even able to open all the files.

Would you happen to know how I can alternate between excel "files/workbooks" without having to type in the exact name of every single file as there are hundreds..?

Thanks in advance.

Hammy

Hi,

I am trying to create macro in excel 2007.
I have excel document in folder together with other files (.pdf, .doc, .xls.....) in excel document I have column (i2,i3,i4...) inside that column I have exact file name (documentName.pdf, documentName.doc....). I need macro that would search for that particular file (if exist) in the same folder and add a link to that file. Something like: i2=documentName.pdf - if that file exist add hyperlink to k2 "link to file" (or "no file on disk" if file doesn't exist), and continue loop trough all "i" columns.

Thanks so much for help.

Hello Everyone,

I am trying to write a macro that will automatically add (2), (3), (4)....respectively to the end of a filename if the filename already exists. I currently am using a master file that starts up automatically and saves the file based on the date. My problem is if the computer is shut down and restarted, it recognizes that the file already exists for that date and will not automatically save the file. I would like to automatically save the file under the same name, but with the appropriate number at the end indicating there are more than one file with the same name.

If anyone knows how to do this with vba, I would greatly appreciate the help.

Thanks,
Jared


No luck finding an answer? You could always try Google.