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

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

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

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

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

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

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

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

Same error here using Excel 2002 SP2 and Windows 2000 Version 5.0 SP4.

HTH
Rowan

Dave Peterson wrote:
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

This is very odd, I got the same result as you for "blah:blah.xls" but
please try this which contains a potential file name from my system. Maybe it
has some thing to do with the space after the colon?

Option Explicit
Sub testme()

Dim TargetName As String
TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"

TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")

MsgBox TargetName

End Sub



"Dave Peterson" wrote:

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

Boy, that's disappointing, huh?

It came back with that invalid name for me, but the saveas failed.

Maybe you could check after the attempt to save???

Option Explicit
Sub testme()

Dim SaveOk As Boolean
Dim TargetName As Variant

TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"
SaveOk = False
Do
TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")

If TargetName = False Then
Exit Do 'but it wasn't saved!
End If

On Error Resume Next
ActiveWorkbook.SaveAs Filename:=TargetName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Workbook wasn't saved!"
TargetName = ""
Err.Clear
Else
SaveOk = True
End If
On Error GoTo 0

If SaveOk = True Then
Exit Do
End If

Loop

If SaveOk = False Then
'error messages here?
End If
End Sub

Amazingly, if (in the getsaveasfilename dialog) I just added a spacebar at the
end of the string, then deleted that space character, the dialog issued a
warning....

So I tried this...

Option Explicit
Sub testme()

Dim TargetName As Variant

TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"
SendKeys "{End} {backspace}"
TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")

If TargetName = False Then
Exit Sub
Else
MsgBox TargetName
End If

End Sub

And son of a gun (or !@#%!!), it seemed to work ok.


Robert Mulroney wrote:

This is very odd, I got the same result as you for "blah:blah.xls" but
please try this which contains a potential file name from my system. Maybe it
has some thing to do with the space after the colon?

Option Explicit
Sub testme()

Dim TargetName As String
TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"

TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")

MsgBox TargetName

End Sub

"Dave Peterson" wrote:

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


--

Dave Peterson

It came back with that invalid name for me, but the saveas failed.

I meant that I got the invalid filename back from that dialog, but that saveas
failed.

(it might not be any clearer, huh?)

Cool, so long as it isn't me just being stupid. I was beginning to wonder! I
think I will go with your suggestion and use the attempt to save as a check.
I don't fancy the idea of using sendkeys in my code though, I think it's
fraught with danger.

Is this an error that I should be reporting somewhere?



- Rm




"Dave Peterson" wrote:

Boy, that's disappointing, huh?

It came back with that invalid name for me, but the saveas failed.

Maybe you could check after the attempt to save???

Option Explicit
Sub testme()

Dim SaveOk As Boolean
Dim TargetName As Variant

TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"
SaveOk = False
Do
TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")

If TargetName = False Then
Exit Do 'but it wasn't saved!
End If

On Error Resume Next
ActiveWorkbook.SaveAs Filename:=TargetName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Workbook wasn't saved!"
TargetName = ""
Err.Clear
Else
SaveOk = True
End If
On Error GoTo 0

If SaveOk = True Then
Exit Do
End If

Loop

If SaveOk = False Then
'error messages here?
End If
End Sub

Amazingly, if (in the getsaveasfilename dialog) I just added a spacebar at the
end of the string, then deleted that space character, the dialog issued a
warning....

So I tried this...

Option Explicit
Sub testme()

Dim TargetName As Variant

TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"
SendKeys "{End} {backspace}"
TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")

If TargetName = False Then
Exit Sub
Else
MsgBox TargetName
End If

End Sub

And son of a gun (or !@#%!!), it seemed to work ok.


Robert Mulroney wrote:

This is very odd, I got the same result as you for "blah:blah.xls" but
please try this which contains a potential file name from my system. Maybe it
has some thing to do with the space after the colon?

Option Explicit
Sub testme()

Dim TargetName As String
TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"

TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")

MsgBox TargetName

End Sub

"Dave Peterson" wrote:

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


--

Dave Peterson

Yep, I understood because that's was the source of my query in the first
place. It took me 12 posts to get my meaning across! You doing well by
comparision.



- Rm

"Dave Peterson" wrote:

It came back with that invalid name for me, but the saveas failed.

I meant that I got the invalid filename back from that dialog, but that saveas
failed.

(it might not be any clearer, huh?)

I don't like sendkeys either...

I reported the "feature" to MS.



Robert Mulroney wrote:

Cool, so long as it isn't me just being stupid. I was beginning to wonder! I
think I will go with your suggestion and use the attempt to save as a check.
I don't fancy the idea of using sendkeys in my code though, I think it's
fraught with danger.

Is this an error that I should be reporting somewhere?

- Rm

"Dave Peterson" wrote:

Boy, that's disappointing, huh?

It came back with that invalid name for me, but the saveas failed.

Maybe you could check after the attempt to save???

Option Explicit
Sub testme()

Dim SaveOk As Boolean
Dim TargetName As Variant

TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"
SaveOk = False
Do
TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")

If TargetName = False Then
Exit Do 'but it wasn't saved!
End If

On Error Resume Next
ActiveWorkbook.SaveAs Filename:=TargetName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Workbook wasn't saved!"
TargetName = ""
Err.Clear
Else
SaveOk = True
End If
On Error GoTo 0

If SaveOk = True Then
Exit Do
End If

Loop

If SaveOk = False Then
'error messages here?
End If
End Sub

Amazingly, if (in the getsaveasfilename dialog) I just added a spacebar at the
end of the string, then deleted that space character, the dialog issued a
warning....

So I tried this...

Option Explicit
Sub testme()

Dim TargetName As Variant

TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"
SendKeys "{End} {backspace}"
TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")

If TargetName = False Then
Exit Sub
Else
MsgBox TargetName
End If

End Sub

And son of a gun (or !@#%!!), it seemed to work ok.


Robert Mulroney wrote:

This is very odd, I got the same result as you for "blah:blah.xls" but
please try this which contains a potential file name from my system. Maybe it
has some thing to do with the space after the colon?

Option Explicit
Sub testme()

Dim TargetName As String
TargetName = "Trend Report - 2NH-ENTRES: NH Entity Result (Campus).xls"

TargetName = Application.GetSaveAsFilename(TargetName, _
fileFilter:="Excel Files (*.xls), *.xls")

MsgBox TargetName

End Sub

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson