Filename creation problem in macro

[BrianG]

I've created a purchase order form in which I automate the filename
creation on save. I create the filename by appending the sequentially
created "purchase order" number with the first 5 characters of the
vendor name. I've run into a problem with vendor names which contain a
slash (/) character, an invalid character in a filename. Does anyone
have any suggestions on a workaround?

Here is the pertinent snippet of my code:
==============
'***************** Assign values to variables ************************
strVendName = Range("E9") 'Assign vendor name (from cell E9)
strPONum = Range("AA2") 'Assign PO number (from cell AA2)
intNumLength = Len(strPONum) 'Assign length of the PO number
strShortVendName = Left(strVendName, 5) 'Assign 1st 5 char of vendor
name
strDestFileName = strPONum & "_" & strShortVendName & ".xls"
'*********************************************************************
Application.EnableEvents = False
ActiveWorkbook.SaveAs filename:=strDestPath & strDestFileName,
FileFormat:=xlNormal
===============

Note: The variable strDestPath is previously assigned a value of
"p:\users\to_share\purchase orders\"

TIA

BrianG

 

[Guest]

strShortVendName = Left(strVendName, 5)

to

strShortVendName = Replace(Left(strVendName, 5),"\","_")

 

[LFCFan]

Do some character replacement:

In this sub i've searched a string for "/" and replaced them with "-".

Sub rStrings()

rStr = "8/23/2006"


newStr = ""

counter = 0

For cChars = 1 To Len(rStr)
counter = counter + 1
currChar = Mid(rStr, counter, 1)
rFind = InStr(1, currChar, "/")


If rFind > 0 Then currChar = "-"
newStr = newStr & currChar

Next

MsgBox (newStr)


End Sub


HTH
Joe

 

[BrianG]

Using "Replace" looked like a simple answer however I could not get
"Replace" to work as suggested. Compile error "sub or function not
defined" was received when using this line of code. I assume this
error means a function named "Replace" needs to be defined. I knew I
was in over my head when I looked at the help file on defining
functions.

BrianG

 

[Dave Peterson]

Replace was added in xl2k.

If you're still using xl97, then try this version:

strShortVendName = application.substitute(Left(strVendName, 5),"\","_")

If you're using xl2k or higher, it's a different problem.