Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the most efficient to check a file name is valid?
What is the most efficient way for checking that a potential file name is
valid ? File names cannot include some charaters like * / : \ ? < is there a function somewhere that checks a file name and pop's up the windows "invalid file name" dialogue? - Rm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the most efficient to check a file name is valid?
Is this so you can ask the user for a name and then save it as that name?
If yes, then I would think that the best thing to do would be to not test it at all. Just use application.GetSaveAsFilename and let the operating system take care of it. If you're gonna use a cell in a worksheet and want to validate that, I wouldn't bother. I'd just try saving the workbook with that name and see if there was an error. on error resume next with activesheet .parent.saveas filename:="C:\somefolder\" & .range("a1").value & ".xls", _ fileformat:=xlworkbooknormal end with if err.number < 0 then msgbox "it didn't save err.clear end if on error goto 0 ===== Not only are there characters that can't be used in filenames, there are some names that can't be used--the old DOS devices: LPT1, LPT2, CON, PRN and the like. === Well, I would check to see if cell was empty first--just in case. Robert Mulroney wrote: What is the most efficient way for checking that a potential file name is valid ? File names cannot include some charaters like * / : \ ? < is there a function somewhere that checks a file name and pop's up the windows "invalid file name" dialogue? - Rm -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the most efficient to check a file name is valid?
That's just the problem I thought that the GetSaveAsFilename would check the resulting string's validity but it doesn't appear to be doing it. The "Save As" dialogue just spits back whatever it's given. Am I doing something wrong? Dim TargetName as String TargetName = Application.GetSaveAsFilename(TargetName, _ fileFilter:="Excel Files (*.xls), *.xls") .. ' Some Other Stuff .. 'Save the file Call ActiveWorkbook.SaveAs(TargetName) ActiveWorkbook.Close - Rm "Dave Peterson" wrote: Is this so you can ask the user for a name and then save it as that name? If yes, then I would think that the best thing to do would be to not test it at all. Just use application.GetSaveAsFilename and let the operating system take care of it. If you're gonna use a cell in a worksheet and want to validate that, I wouldn't bother. I'd just try saving the workbook with that name and see if there was an error. on error resume next with activesheet .parent.saveas filename:="C:\somefolder\" & .range("a1").value & ".xls", _ fileformat:=xlworkbooknormal end with if err.number < 0 then msgbox "it didn't save err.clear end if on error goto 0 ===== Not only are there characters that can't be used in filenames, there are some names that can't be used--the old DOS devices: LPT1, LPT2, CON, PRN and the like. === Well, I would check to see if cell was empty first--just in case. Robert Mulroney wrote: What is the most efficient way for checking that a potential file name is valid ? File names cannot include some charaters like * / : \ ? < is there a function somewhere that checks a file name and pop's up the windows "invalid file name" dialogue? - Rm -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the most efficient to check a file name is valid?
What about using the SaveAs dialog box?
Application.Dialogs(xlDialogSaveAs).Show If Not ThisWorkbook.Saved Then _ MsgBox "Not Saved" "Robert Mulroney" wrote: That's just the problem I thought that the GetSaveAsFilename would check the resulting string's validity but it doesn't appear to be doing it. The "Save As" dialogue just spits back whatever it's given. Am I doing something wrong? Dim TargetName as String TargetName = Application.GetSaveAsFilename(TargetName, _ fileFilter:="Excel Files (*.xls), *.xls") . ' Some Other Stuff . 'Save the file Call ActiveWorkbook.SaveAs(TargetName) ActiveWorkbook.Close - Rm "Dave Peterson" wrote: Is this so you can ask the user for a name and then save it as that name? If yes, then I would think that the best thing to do would be to not test it at all. Just use application.GetSaveAsFilename and let the operating system take care of it. If you're gonna use a cell in a worksheet and want to validate that, I wouldn't bother. I'd just try saving the workbook with that name and see if there was an error. on error resume next with activesheet .parent.saveas filename:="C:\somefolder\" & .range("a1").value & ".xls", _ fileformat:=xlworkbooknormal end with if err.number < 0 then msgbox "it didn't save err.clear end if on error goto 0 ===== Not only are there characters that can't be used in filenames, there are some names that can't be used--the old DOS devices: LPT1, LPT2, CON, PRN and the like. === Well, I would check to see if cell was empty first--just in case. Robert Mulroney wrote: What is the most efficient way for checking that a potential file name is valid ? File names cannot include some charaters like * / : \ ? < is there a function somewhere that checks a file name and pop's up the windows "invalid file name" dialogue? - Rm -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the most efficient to check a file name is valid?
Thanks, but I have a particular reason for wanting to seperate the save
dialogue and the actual saving of the file. I'm going to copy a sheet from one workbook to another, after stripping the conditional formatting in each visible cell. There's a lot of calulations involved so I want to give the user a chance to cancel before doing the processing. At the moment I copy the use provided file name into a string and don't use it until the end of a long process. - Rm "JMB" wrote: What about using the SaveAs dialog box? Application.Dialogs(xlDialogSaveAs).Show If Not ThisWorkbook.Saved Then _ MsgBox "Not Saved" "Robert Mulroney" wrote: That's just the problem I thought that the GetSaveAsFilename would check the resulting string's validity but it doesn't appear to be doing it. The "Save As" dialogue just spits back whatever it's given. Am I doing something wrong? Dim TargetName as String TargetName = Application.GetSaveAsFilename(TargetName, _ fileFilter:="Excel Files (*.xls), *.xls") . ' Some Other Stuff . 'Save the file Call ActiveWorkbook.SaveAs(TargetName) ActiveWorkbook.Close - Rm "Dave Peterson" wrote: Is this so you can ask the user for a name and then save it as that name? If yes, then I would think that the best thing to do would be to not test it at all. Just use application.GetSaveAsFilename and let the operating system take care of it. If you're gonna use a cell in a worksheet and want to validate that, I wouldn't bother. I'd just try saving the workbook with that name and see if there was an error. on error resume next with activesheet .parent.saveas filename:="C:\somefolder\" & .range("a1").value & ".xls", _ fileformat:=xlworkbooknormal end with if err.number < 0 then msgbox "it didn't save err.clear end if on error goto 0 ===== Not only are there characters that can't be used in filenames, there are some names that can't be used--the old DOS devices: LPT1, LPT2, CON, PRN and the like. === Well, I would check to see if cell was empty first--just in case. Robert Mulroney wrote: What is the most efficient way for checking that a potential file name is valid ? File names cannot include some charaters like * / : \ ? < is there a function somewhere that checks a file name and pop's up the windows "invalid file name" dialogue? - Rm -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the most efficient to check a file name is valid?
What's in TargetName when you use that line?
What's in the targetname when it comes back? In my simple tests, I couldn't break it. Robert Mulroney wrote: That's just the problem I thought that the GetSaveAsFilename would check the resulting string's validity but it doesn't appear to be doing it. The "Save As" dialogue just spits back whatever it's given. Am I doing something wrong? Dim TargetName as String TargetName = Application.GetSaveAsFilename(TargetName, _ fileFilter:="Excel Files (*.xls), *.xls") . ' Some Other Stuff . 'Save the file Call ActiveWorkbook.SaveAs(TargetName) ActiveWorkbook.Close - Rm "Dave Peterson" wrote: Is this so you can ask the user for a name and then save it as that name? If yes, then I would think that the best thing to do would be to not test it at all. Just use application.GetSaveAsFilename and let the operating system take care of it. If you're gonna use a cell in a worksheet and want to validate that, I wouldn't bother. I'd just try saving the workbook with that name and see if there was an error. on error resume next with activesheet .parent.saveas filename:="C:\somefolder\" & .range("a1").value & ".xls", _ fileformat:=xlworkbooknormal end with if err.number < 0 then msgbox "it didn't save err.clear end if on error goto 0 ===== Not only are there characters that can't be used in filenames, there are some names that can't be used--the old DOS devices: LPT1, LPT2, CON, PRN and the like. === Well, I would check to see if cell was empty first--just in case. Robert Mulroney wrote: What is the most efficient way for checking that a potential file name is valid ? File names cannot include some charaters like * / : \ ? < is there a function somewhere that checks a file name and pop's up the windows "invalid file name" dialogue? - Rm -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the most efficient to check a file name is valid?
Okay despite my example below; targetName string is passed to the "saving" procedure as a parameter. It could be just about anything. I'm using this variable to pass a suggested or default file name. I'd like to have the filename include a cost centre's name in it. Unfortunately some of the cost centre names have ":"'s in them. I guess if you set targetName to "blah:blah.xls" in my example that would represent the problem I'm having. It doesn't error until I try to save the workbook. I want to be able to 'know' that a user has provided a valid filename in the saveAs dialogue before trying to save. many thanks for your help so far all. - Rm "Dave Peterson" wrote: What's in TargetName when you use that line? What's in the targetname when it comes back? In my simple tests, I couldn't break it. Robert Mulroney wrote: That's just the problem I thought that the GetSaveAsFilename would check the resulting string's validity but it doesn't appear to be doing it. The "Save As" dialogue just spits back whatever it's given. Am I doing something wrong? Dim TargetName as String TargetName = Application.GetSaveAsFilename(TargetName, _ fileFilter:="Excel Files (*.xls), *.xls") . ' Some Other Stuff . 'Save the file Call ActiveWorkbook.SaveAs(TargetName) ActiveWorkbook.Close - Rm "Dave Peterson" wrote: Is this so you can ask the user for a name and then save it as that name? If yes, then I would think that the best thing to do would be to not test it at all. Just use application.GetSaveAsFilename and let the operating system take care of it. If you're gonna use a cell in a worksheet and want to validate that, I wouldn't bother. I'd just try saving the workbook with that name and see if there was an error. on error resume next with activesheet .parent.saveas filename:="C:\somefolder\" & .range("a1").value & ".xls", _ fileformat:=xlworkbooknormal end with if err.number < 0 then msgbox "it didn't save err.clear end if on error goto 0 ===== Not only are there characters that can't be used in filenames, there are some names that can't be used--the old DOS devices: LPT1, LPT2, CON, PRN and the like. === Well, I would check to see if cell was empty first--just in case. Robert Mulroney wrote: What is the most efficient way for checking that a potential file name is valid ? File names cannot include some charaters like * / : \ ? < is there a function somewhere that checks a file name and pop's up the windows "invalid file name" dialogue? - Rm -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the most efficient to check a file name is valid?
When I did this:
Option Explicit Sub testme() Dim TargetName As String TargetName = "blah:blah.xls" TargetName = Application.GetSaveAsFilename(TargetName, _ fileFilter:="Excel Files (*.xls), *.xls") MsgBox TargetName End Sub Excel wouldn't accept that name when I hit the Save button. It yelled that: Unable to save to this URL location, try using an HTTP or FTP URL location instead. ========= Maybe there's a difference in the version of excel/windows you're using. I did my testing with xl2003 and winXP Home. Did that actually work for you? What are your versions of excel/windows? Robert Mulroney wrote: Okay despite my example below; targetName string is passed to the "saving" procedure as a parameter. It could be just about anything. I'm using this variable to pass a suggested or default file name. I'd like to have the filename include a cost centre's name in it. Unfortunately some of the cost centre names have ":"'s in them. I guess if you set targetName to "blah:blah.xls" in my example that would represent the problem I'm having. It doesn't error until I try to save the workbook. I want to be able to 'know' that a user has provided a valid filename in the saveAs dialogue before trying to save. many thanks for your help so far all. - Rm "Dave Peterson" wrote: What's in TargetName when you use that line? What's in the targetname when it comes back? In my simple tests, I couldn't break it. Robert Mulroney wrote: That's just the problem I thought that the GetSaveAsFilename would check the resulting string's validity but it doesn't appear to be doing it. The "Save As" dialogue just spits back whatever it's given. Am I doing something wrong? Dim TargetName as String TargetName = Application.GetSaveAsFilename(TargetName, _ fileFilter:="Excel Files (*.xls), *.xls") . ' Some Other Stuff . 'Save the file Call ActiveWorkbook.SaveAs(TargetName) ActiveWorkbook.Close - Rm "Dave Peterson" wrote: Is this so you can ask the user for a name and then save it as that name? If yes, then I would think that the best thing to do would be to not test it at all. Just use application.GetSaveAsFilename and let the operating system take care of it. If you're gonna use a cell in a worksheet and want to validate that, I wouldn't bother. I'd just try saving the workbook with that name and see if there was an error. on error resume next with activesheet .parent.saveas filename:="C:\somefolder\" & .range("a1").value & ".xls", _ fileformat:=xlworkbooknormal end with if err.number < 0 then msgbox "it didn't save err.clear end if on error goto 0 ===== Not only are there characters that can't be used in filenames, there are some names that can't be used--the old DOS devices: LPT1, LPT2, CON, PRN and the like. === Well, I would check to see if cell was empty first--just in case. Robert Mulroney wrote: What is the most efficient way for checking that a potential file name is valid ? File names cannot include some charaters like * / : \ ? < is there a function somewhere that checks a file name and pop's up the windows "invalid file name" dialogue? - Rm -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check valid Date value? | Excel Worksheet Functions | |||
Check if date is valid | Excel Programming | |||
To to check whether a string is a valid reference | Excel Programming | |||
Check for valid SQL server connection | Excel Programming | |||
check for valid file | Excel Programming |