Replacing a character in a string with another character

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to replace the 1st "." in
"(e-mail address removed)" with "_". Or I need to remove
it completely. This is a filename that I need to import, but this period is
causing the import to error out.

Actually I am writing a proc that will cycle through the .csv files in a
folder and I need to make the above replacement for all .csv files that start
with "PartnerContract". Below is the code I have so far. Any suggestions as
to how I can complete this would be greatly appreciated.

'Set file type.

strFile = Dir$(PathName & "*.csv*")

'Check if any .csv files are in Imports directory.

If Len(strFile) > 0 = False Then
MsgBox "There are no update files.", vbOKOnly, "WARNING"
Exit Sub
End If

'Import all .csv files in Imports directory.

Do While Len(strFile) > 0

If Left(strFile, 15) = "PartnerContract" Then

strFile = Dir$()

End If

Loop
 
To replace the the 1st "." with an "_" use this, where OriginalFileName is
what you start with and NewFileName is what you end up with:

Private Sub OriginalFileName_BeforeUpdate(Cancel As Integer)
Dim I As Integer
Dim BeforeFirstDot As String
Dim AfterFirstDot As String

BeforeFirstDot = Left(Me.OriginalFileName, (InStr(OriginalFileName, ".") - 1))

AfterFirstDot = Right(Me.OriginalFileName, Len(Me.OriginalFileName) - (InStr
(OriginalFileName, ".")))

NewFileName = BeforeFirstDot & "_" & AfterFirstDot

End Sub

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
 
You can use the Replace function to change just the first occurence of "."
to "_":

ModifiedName = Replace(OriginalFileName, ".", "_", 1, 1, 1)

To rename all .csv files in a folder, this code snippet should work:

Dim strFileOrig As String, strPathName As String, strFileNew As String
strPathName = "YourPathString\"
strFileOrig = Dir(strPathName & "*.csv")
Do While strFileOrig <> ""
strFileNew = Replace(strPathName & strFileOrig, ".", "_", 1, 1, 1)
Name strPathName & strFileOrig As strPathName & strFileNew
strFileOrig = Dir()
Loop
 
I need to replace the 1st "." in
"(e-mail address removed)" with "_". Or I need to remove
it completely. This is a filename that I need to import, but this period is
causing the import to error out.

Actually I am writing a proc that will cycle through the .csv files in a
folder and I need to make the above replacement for all .csv files that start
with "PartnerContract". Below is the code I have so far. Any suggestions as
to how I can complete this would be greatly appreciated.

'Set file type.

strFile = Dir$(PathName & "*.csv*")

'Check if any .csv files are in Imports directory.

If Len(strFile) > 0 = False Then
MsgBox "There are no update files.", vbOKOnly, "WARNING"
Exit Sub
End If

'Import all .csv files in Imports directory.

Do While Len(strFile) > 0

If Left(strFile, 15) = "PartnerContract" Then

strFile = Dir$()

End If

Loop
Click to expand...

Only the first dot?

If your version of Access includes the Replace function .....

Dim strString as String
strString = "Whatever"

If Left(strString,15) = "PartnerContract" then
Replace(strString, ".", "_", ,1)
End If

will return:
PartnerContract_clintonherman@yahoo_com.csv
 
Back
Top