For x = 1 To Len(strVar)
If Mid(strVar, x, 1) = " " Then
Mid(strVar, x, 1) = "_"
End If
Next x

I haven't tested this, but it works in theory. Let me know if you hav
any problems - Piku

--
Message posted from http://www.ExcelForum.com

Okay... I just tested it and it does work fine! :-) - Piku

--
Message posted from http://www.ExcelForum.com

Hi Pikus
why not use Replace :-)

--
Regards
Frank Kabel
Frankfurt, Germany


For x = 1 To Len(strVar)
If Mid(strVar, x, 1) = " " Then
Mid(strVar, x, 1) = "_"
End If
Next x

I haven't tested this, but it works in theory. Let me know if you
have any problems - Pikus


---
Message posted from http://www.ExcelForum.com/

He specifically said he was working with a string variable, not info i
a cell. Now the info may actually be in a cell, but without knowing
didn't want to assume. That being said, Replace only works on Rang
objects right? So it wouldn't work with a string... Please tell me i
I'm wrong about that. - Piku

--
Message posted from http://www.ExcelForum.com

In VBA6 (Excel 2000 and later) there is a Replace function that
replaces text within character strings. E.g.,

Dim S As String
S = "abcdefg"
S = Replace(S, "abc", "xyz")
Debug.Print S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"pikus " wrote in message
...
He specifically said he was working with a string variable, not
info in
a cell. Now the info may actually be in a cell, but without
knowing I
didn't want to assume. That being said, Replace only works on
Range
objects right? So it wouldn't work with a string... Please
tell me if
I'm wrong about that. - Pikus


---
Message posted from http://www.ExcelForum.com/

Thanks Chip! Is there anything you don't know? - Piku

--
Message posted from http://www.ExcelForum.com

Here is the code I used
-----------------------------------------

On Error Resume Nex

FileSaveName = Application.GetSaveAsFilename("K:\Excel_Keyword\",
fileFilter:="Text Files (*.txt), *.txt"

space_position = Application.WorksheetFunction.Find(" ", FileSaveName

If Err = 0 Then MsgBox "Spaces have been found in your file name," & vbCrLf &
"they will be replaced with underscores

Do While Err =
FileSaveName = Application.WorksheetFunction.Replace
(Arg1:=FileSaveName, Arg2:=space_position,
Arg3:=1, Arg4:="_"
space_position = Application.WorksheetFunction.Find(" ", FileSaveName
Loo

Err.Clea

Why so verbose and slow?

FileSaveName = Application.GetSaveAsFilename("K:\Excel_Keyword\", _
fileFilter:="Text Files (*.txt), *.txt")

FileSaveName = Application.Substitute(FileSaveName," ","_")

There is no need to test - because if there are no spaces, nothing will
happen, and if there are, they will be replaced.

You can also use the vba replace function if only working in xl2000 or
higher (or anyone who will use your code).

if you want a warning, so you need to check then


FileSaveName = Application.GetSaveAsFilename("K:\Excel_Keyword\", _
fileFilter:="Text Files (*.txt), *.txt")
if instr(1,FileSaveName," ",vbTextCompare) < 0 then
msgbox "warning . . . blah blah"
FileSaveName = Application.Substitute(FileSaveName," ","_")
end if


--
Regards,
Tom Ogilvy


"Simon Shaw" wrote in message
...
Here is the code I used:
------------------------------------------

On Error Resume Next

FileSaveName = Application.GetSaveAsFilename("K:\Excel_Keyword\", _
fileFilter:="Text Files (*.txt), *.txt")

space_position = Application.WorksheetFunction.Find(" ", FileSaveName)

If Err = 0 Then MsgBox "Spaces have been found in your file name," &
vbCrLf & _
"they will be replaced with underscores"

Do While Err = 0
FileSaveName = Application.WorksheetFunction.Replace _
(Arg1:=FileSaveName, Arg2:=space_position, _
Arg3:=1, Arg4:="_")
space_position = Application.WorksheetFunction.Find(" ",
FileSaveName)
Loop

Err.Clear

How would I know?

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"pikus " wrote in message
...
Thanks Chip! Is there anything you don't know? - Pikus


---
Message posted from http://www.ExcelForum.com/

Hi Pikus
Replace works also on string variables (see VBA help)

--
Regards
Frank Kabel
Frankfurt, Germany


He specifically said he was working with a string variable, not info
in a cell. Now the info may actually be in a cell, but without
knowing I didn't want to assume. That being said, Replace only works
on Range objects right? So it wouldn't work with a string... Please
tell me if I'm wrong about that. - Pikus


---
Message posted from http://www.ExcelForum.com/

Just excalimimg cuz I feel like SUCH an amature next to some of th
people that post here. I read a book about VBA like six months ago an
I really fell in love with programing and I'm soaking up information a
what feels like light speed, but the experience you guys bring i
awesome... Anyways, thanks again for the pointers. Talk to y'al
later. - Piku

--
Message posted from http://www.ExcelForum.com

Only because I didn't know about using substitute

thanks

Does the vba replace function exist in XL 97

I thought i used it there B4 but now having trouble.
Do I have to add references or something to the workbook to get th
function to work?

Thanks AL

--
Message posted from http://www.ExcelForum.com

The replace function was added in VBA6. xl2000 and later use VBA6. XL97
uses VBA5 which does not have a replace function.

It can not be added by changing references. However, you could write your
own.
--
Regards,
Tom Ogilvy

"ajlinnane " wrote in message
...
Does the vba replace function exist in XL 97

I thought i used it there B4 but now having trouble.
Do I have to add references or something to the workbook to get the
function to work?

Thanks AL.


---
Message posted from http://www.ExcelForum.com/

Just to add
in all versions you can use the substitute worksheet function

sStr = Application.substitute(sSt," ","")

You could possibly use conditional compilation since this would be slower.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
The replace function was added in VBA6. xl2000 and later use VBA6. XL97
uses VBA5 which does not have a replace function.

It can not be added by changing references. However, you could write your
own.
--
Regards,
Tom Ogilvy

"ajlinnane " wrote in message
...
Does the vba replace function exist in XL 97

I thought i used it there B4 but now having trouble.
Do I have to add references or something to the workbook to get the
function to work?

Thanks AL.


---
Message posted from http://www.ExcelForum.com/