Automatic Save, But Ask For New Name If One Exists

Jan 30, 2008

i have my excel spreeadhseet setup so that it asks me for a clients name
and then saves the file as the clients name in there folder

example:

c:work est est.xls

very often i do several differnt quotes for the same name

so when i open up and type in test as the client name, it says one called test already exists, do i want to overite it

if i do no, it ends my template
if i click yes it overrides it

how can i make it so that it says something like
the file name is already taken,
please select a new name, so id type 'test walk' or something like that, but it would know to save it in the test folder

here is the script i have now

Private Sub Workbook_Open()
MsgBox "Make sure you thank Ryan for this wonderful program which HE has created!!", vbOKOnly + vbInformation, "Notification Message"

Dim sName As String, fs As Object, sPath As String, bExists As Boolean

Set fs = CreateObject("Scripting.FileSystemObject")
sPath = "C:Documents and SettingsuserMy DocumentsWork"
If InStr(UCase(ThisWorkbook.Name), ".XLT") > 0 Then 'ask for name only if it's a template
sName = InputBox("Enter the Clients last name.", "Name Input Dialog")
If Len(sName) = 0 Then Exit Sub

View 9 Replies


ADVERTISEMENT

Check If Directory Exists Before Save

Oct 2, 2007

I am looking to see if a directory exists, and if it does, to save a file in it, otherwise create it, then save the file. My IsFileThere function works fine on files, but does not detect directories. The len(Dir(Filename)) statement always returns a 0, with the Dir statement always simply populating with "". It works fine on filenames - so can anyone tell me why it does not pick up directories?
Shouldn't the Dir function on a directory name return "."? I have excerpted some of my code to include here: Global Const StoredJobsSubdirectory = "Jobs"

Dim AppPath As String: AppPath = ActiveWorkbook.Path
'other code in here - activeworkbook changed, hence previous line
If IsFileThere(AppPath & "" & StoredJobsSubdirectory) Then
ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _
"" & NameString & ".xls"
Else
MkDir AppPath & "" & StoredJobsSubdirectory
ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _
"" & NameString & ".xls"
End If............................

View 2 Replies View Related

Save Workbook To Desktop If File Doesn't Exists

Sep 22, 2009

I'm having a slight problem with this script. What I'm looking to do is before the workbook is closed is check to see if the workbook already exisits on the users desktop if it doesnt then save it to the users desktop. if is does exists then just exit the sub. Here is what I have so far but for some reason it kept displaying the aleart message that the file already exists and wants to save it again.

I just added the displayalerts = false in but the script still does not know that file already exists. So when the file is closed it keeps saying file has been placed on your desktop.

View 3 Replies View Related

How To Save With Automatic Filename Plus Today's Date

Jul 28, 2007

Is there a way to save a file and have it automatically put today's date in the file name?

Example: original File name = test.xls
desired file name = test072807.xls, or test.072807.xls, or test.07 28 07.xls

So, I open the file, do whatever, and then click save.
When I click save, it does one of the above, given that today is 7 28 07.

View 9 Replies View Related

Ignore File Name / Save File Name Different If Other Exists?

Jun 6, 2014

My macro pulls data from a set of files in a folder and creates an output based on my formatting rules.

Ran into 2 issues though. The first being is after this macro is run it saves a file in the folder with the name "Data Export" and before I had it do this if "Data Export" was already there

Code:
'Check if file name to save exists If Dir$(vFolder & "" & sSubFolder & ".xls", vbNormal) = "" Then
wb.SaveAs vFolder & "" & sSubFolder & ".xls"
MsgBox "Complete!", vbOKOnly
Else
MsgBox "File already exists, could not save!", vbInformation, "COMPLETE!"
End If

What I want it to do instead is change itself and save it as Data Export A, and then also loop for A each time, if A is there go to B , so on and so forth.

-Alternate solution if this is way to challenging, can I have it pop up the Save Message box to let the user try a new name if "Data Export" exists. I was trying to think through that too..

2nd Issue is when I loop the files in the folder I selected in my macro, I want it to ignore "Data Export" files. Before I just took a shortcut and had it save outside of the data folder, which was fine but now we are running on so many files a day that we need it inside the same folder for organization purposes.

That is done with the following

Code:
'Loop through files in folder sFileName = Dir$(vFolder & "")
Do Until sFileName = ""

I was thinking maybe I can add an error handler to the loop? Before Do Until? Something like this? But how I can add it into the loop?

Code:
If sFileName Like "????.?.xls" Then

View 4 Replies View Related

Excel 2010 :: Save Open Worksheet To File And Include Date Of Save?

Mar 11, 2014

am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....

I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....

I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...

View 6 Replies View Related

Excel 2003 :: Unable To Allow Users To Make Changes To Workbook But Only Be Able To Do Save As Not Save

Dec 3, 2012

I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.

View 4 Replies View Related

Excel Save As Should Default File Save Type To XLSX Using VBA

Aug 21, 2012

What I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.

All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.

View 2 Replies View Related

Macro To Check If Saved Then Save Else Bring Up Save As Dialog

Sep 6, 2012

I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .

The macro itself is saved in personal.xlsx.

View 1 Replies View Related

Customize Look In/Save In (My Places) In Open/Save As Dialog Boxes

May 10, 2008

I would like to add some icons on the left side of excel open file pane to faciliate my work. Because i need to load some files under the same folder many times a day. Does anybody know how to do that? I've seen people has more icons on the pane before. The defaut setting has only 'History', 'My Documents', 'Favorites', 'Desktop' and ' My nutwork places' on it.

View 2 Replies View Related

VBA - Use Command Button To Obtain Save As Screen And Save As PDF

Jun 22, 2014

I have a UserForm in which i have inserted a CommandButton. I also have a Table on a Sheet in Excel that i would like to save as PDF.

When i click the CommandButton on the UserForm i would like the SaveAs screen to come up and have the Table (or Used Range in the Excel Sheet) as the selection to be Saved as .PDF format.

View 3 Replies View Related

Code, That Disables Save, Save As And Close Commands

Nov 2, 2009

Each of the worksheets in my model use A1 as a control cell for any errors and inconsistencies. My aim is to disable save and close commands in case A1 is not equal to 0 in any of the worksheets.

The code I currently use for that purpose is as follows.

View 2 Replies View Related

Save Workbook Using Variable For Save As And For A New Folder Name

Jul 31, 2009

I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.

View 6 Replies View Related

Save As Macro :: Save File To Folder

Jan 17, 2010

I have a macro that copy one sheet of the Active workbook and sends it via email.

I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.

View 10 Replies View Related

Save Back Up Copy Online At Each Save

Mar 27, 2008

I have a sheet which I would like to back up online every time it is saved.

I have a sub, OnlineFile(), which I have used many times without any problems, which creates a .BAT file to use ftp to upload files onto our server. As arguments, it takes the local file path that you want to upload (or download to), the name of the file as you would like it stored, the online file path, and whether you want to upload the file onto the server, or download it off the server.

It should be very easy to combine the two: what I tried was:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "" & "Rubbish.xls"
Call OnlineFile(ThisWorkbook.Path & "" & "Rubbish.xls", "Rubbish.xls", "wwwrootexcelfilesMasters", "Upload")
Kill ThisWorkbook.Path & "" & "Rubbish.xls"
End Sub

Every time I ran this, the file got uploaded correctly, but Excel had a series of problems. Often the code ran fine the first time it was run, but crashed the second time round.

After a fair bit of troubleshooting, I found that the problem came about when the file was trying to save itself - i.e. after the end of the sub. If you pause any macros from running, it still saved fine, but when the code ran, it found errors, froze or closed excel.

My thought at this point was that the code was fragmented or something. I made a new sheet, and retyped just the relevant bits of code (i.e. the BeforeSave sub, the OnlineFile sub and created the form called by OnlineFile). The problem persisted.

I've come up with a workaround for this now (which uses the same OnlineFile sub & form, and works fine), but it's driving me crazy - why does this cause excel to die? Particularly on the second time through? The code seems far too simple to cause such problems - is there a bug in Excel perhaps (I'm using Excel 2000 on Windows XP).

View 9 Replies View Related

Run Macro 1 If Value X Exists Or Macro B If Value X Exists Twice

Jul 15, 2009

This is quite a complicated one so please feel free to help in chunks rather than the whole at once...

From Row 9 onwards column A and C are checked.

Condition 1

If in column A and column C the same values exists in the next row down e.g. A9 = JONES C9 = Peter and A10 = JONES C10 = Peter then the data in column E is checked for those two rows.

In the two checked rows in Column E, data which contains the words Pre-int, Upper-int, Int, Elementary, Advanced or Beginner is then inserted into D5 on spreadsheets titled "Schedule A GE2" and "Schedule B GE2".......

View 12 Replies View Related

Look If Value Exists In Range

Sep 10, 2009

I have a form, when I fill in the order no, I want it to look if the value exists in a range and then notify me with a msgbox. Here is my code - it doesn't work no matter what I insert into the textbox it tells me that it exist.

View 11 Replies View Related

Sum Smallest Value Per Row If Value Exists In Column A

Jul 24, 2009

The whole "story" is explained, in details, inside the attached WB.

View 3 Replies View Related

Formula To Indicate Match Exists

Aug 20, 2009

to creating formulas in Excel!

Basically, all I want is for a formula to tell me if I have matching data in two separate cells. What I have is data entered in cell A1. What I would like is one single formula to check if the same entry has been entered in cells A2:A10 and if so, return a 1, and if not return 0.

View 9 Replies View Related

If Value Exists In A Range Then Move To Next Row

Jun 18, 2014

Basically, if a cell value exists in a list on another worksheet then move to next row. If the cell value does NOT exist then look at columns J, K, L and M and see if a specific value exists in each. If any of these cells contain this criteria then copy the row and add it to the bottom of a growing list on another sheet. Here is what I have so far.

Sub OrdersHeld()

Application.ScreenUpdating = False

Dim x As Double
Dim ws As Worksheet
Dim numberofrows As Double

[Code] ......

View 11 Replies View Related

Delete Row If File Exists?

Aug 22, 2014

I have a list of numbers and I have corresponding text files which I named by using these numbers as reference

is there a possible macro to delete those rows which have corresponding text files by checking in a specific folder if that file name exists

View 2 Replies View Related

Check To See If A File Exists

Nov 8, 2007

I have an excel spreadsheet used to populate word documents based on a template file that is loaded like this....

View 9 Replies View Related

How To Check If Range Exists

Jan 13, 2009

Hi all, this might sound really easy but I have a Named Range in excel 2003.

Now I'd like to write a VBA code to check if this range exists before carrying on futher computations.

View 4 Replies View Related

Jeanie For Exists For Bbcode?

Mar 1, 2009

Is there anything like Excel jeanie that exists for bbcode?

View 2 Replies View Related

Check If Shape Exists

Mar 30, 2009

how do i check if a shape exists?

i have a shape created by a macro.
sShape

so when i get rid of this shape i use

View 3 Replies View Related

If Product Exists Then Copy To That Row

May 9, 2009

The workbook I have created will allow user to set up records for customer pricing. The code below is the module that will check if the file already exists and if so it will add the new record to that file and If it does not exist it will go to a nother module to create a new workbook.

View 3 Replies View Related

Determining If A Worksheet Exists

Jun 11, 2009

I try to do the following I get an error 'Subscript out of range'

View 3 Replies View Related

Open A File If It Exists

Sep 7, 2009

Through excel I run a dos program which spits out a results file and my macro then opens this file in excel. The only problem is the macro tries to open the file before the other program has writen it. I have tried to fix this using the

View 5 Replies View Related

Test If List Exists

Nov 16, 2009

is there a way to test if a list exists on a sheet? I'd like to do something like this:

View 2 Replies View Related

LOOKUP Giving #N/A If No Value >0 Exists Yet

Mar 22, 2009

This formula has been working fine for me...until I have the condition where no value >0 has been entered in my LOOKUP range as yet. Or at least that is what I have managed to test so far. I've verified that my values have the same format (General) and I've verified that my ranges are all of the same size.

I'm looking in one worksheet for a work order meter reading that will be entered in another sheet when the Unit # for the equipment matches. I discovered if there is a work order entered for and no meter reading has ever been recorded yet, I get the #N/A error. If I go in and put a fictitious meter reading in of 1, the error goes away. It doesn't like a blank or zero meter reading and I need to know how to get around this. Here's the current calculation:

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved