Re: replace special character for filename save

I get a 1004 error message whenever I run it and the vendor name has either
an & or an / or an *in the name. I thought those were big No No's for
filenames? This is intel/windows xp/vista workstations on a MS server
2000/2003. I can get names with the "-" to save. There may be others that
make it bomb so I just want to blanket kill the special characters and maybe
replace them with either a space or underscore. Any other ideas??? I would
agree with you if it only bombed on one particular vendor name, but I have
found more. And it does work correctly for "normal" vendor names.

Appreciate the help

"Dave Peterson" wrote:

Maybe it's not the "&" that's causing the problem.

I can save a file using a name with an ampersand in it.

(If you're using a Mac, maybe it's different?)

anduare2 wrote:

Some of my venname data contains an "&" in them. When one is selected from a
dropdown list, it populates cell B2. When the macro runs to save the file I
need to replace the & with the text "and" so my file save routine will not
bomb. But if there is no & in the name I need it to continue on to the save.
It seems like a simple if:then:if:endif would fix it, but I just can't get a
handle on the dim/set value items to get it to replace / what / replacement
code to work for just one instance. Maybe I am looking at it all backwards
again? Here is a list of the macro/code I have pieced together

'Procedure to save the Sales Workbook to the Sharepoint Library

Sub SaveWork()

Dim venname As String
Dim vennumber As String
Dim venyear As String
Dim venperiod As String

venname = Worksheets("Sales").Range("B2")
vennumber = Worksheets("Sales").Range("B3")
venyear = Worksheets("Data").Range("J15")
venperiod = Worksheets("Data").Range("J16")

ActiveWorkbook.SaveAs
Filename:="HTTP://teams.kedc.org/mst/Sales_Reports/"; & venname & "_" &
vennumber & "_" & venyear & "_" & venperiod & ".xls"
MsgBox "File was saved as " & ActiveWorkbook.Name
End Sub

If possible please include as much detail in example, I am slightly macro
literate but vb ignorant.

Much Appreciated

Martin

--

Dave Peterson

.