Find and Replace with Wildcards 1

[123FakeSt]

Hi, i have a VBA excel macro that opens a large fixed width ascii file in cells a:a. I need to replace the first 9 characters with text that the user will supply depending on the first letter of the text. The user will enter replacements for all of the possible starting characters.

The basic logic is ....

Dim SearchRange as Range, userpstring as String
Set SearchRange = Range("Sheet2!a:a")
userpstring = InputBox("Enter P String") ' There are 4 possibilities

'If the first letter is 'p' pull the first 9 characters, replace with userpstring & any spaces needed so character 10 is still character 10.

Thanks, i have already learned so much from these threads!
Rusty

 

[SkipVought]

Rusty,

This might work

Dim SearchRange As Range, userpstring As String * 10
Set SearchRange = Range("Sheet2!a:a")
userpstring = InputBox("Enter P String") ' There are 4 possibilities
    For Each s In SearchRange
        With s
            If UCase(Left(.Value, 1)) = "P" Then
                .Value = userpstring & Right(.Value, Len(.Value) - 10)
            End If
        End With
    Next

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[123FakeSt]

That did the trick, it would have been long hours to figure out the "String * 10" part for me. I guess i could just copy the If statement for the 4 possibilities (P,H,J,D). In C++ i would use the 'case' logic flow, but i've had no luck with it in VBA yet (it's been only a couple weeks).

Thanks Skip!

Rusty

 

[SkipVought]

Dim SearchRange As Range, userpstring(4) As String * 10
Set SearchRange = Range("Sheet2!a:a")
for i = 0 to 3
  select case i
  case 0: q="Enter P String"
  case 1: q="Enter Q String"
  case 2: q="Enter R String"
  case 3: q="Enter S String"
  end select
  userpstring(i) = InputBox(Q) ' There are 4 
next
possibilities
    For Each s In SearchRange
        With s
            select case Left(.value, 1)
             case "P": i=0
             case "Q": i=1
             case "R": i=2
             case "S": i=3
            end select
            
                .Value = userpstring(i) & Right(.Value, Len(.Value) - 10)
            End If
        End With
    Next

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[123FakeSt]

Alright! We've got an array and a select/case. I'm starting to see the light at the end of the VBA tunnel!

Everything is beautiful except Excel VBA feels the need to throw in a set of double-quotes when it finds a comma or apostrophe anywhere in the text file, which is screwing up the alignment. I could easily do a find for double-quotes and replace with nothing. Is there a better way?

Thanks,
Rusty

 

[PHV]

i have a VBA excel macro that opens a large fixed width ascii file in cells a:a
Can you please post the relevant code ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[123FakeSt]

Do you mean the part the imports it from Unix? I'm a little embarrased (i'm a beginner) but here it is.....


Shell "cmd /C Q:\3Import.bat" ' Script to FTP file labels.txt

Application.Wait Now + TimeValue("00:00:10")

Workbooks.OpenText FileName:="Q:\labels.txt", Origin:=xlWindows, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 2)
Cells.Select
Selection.Copy
Windows("Labels.txt").Activate
Windows("UpdateKeycodes.xls").Activate
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste

 

[123FakeSt]

Actually, the problem is Notepad.exe opening the file. Wordpad.exe and Excel.exe don't add the quotes.

Sorry ... not a VBA problem!

Rusty

 

[SkipVought]

Notepad? Where did that come from?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Are the double quotes in the UpdateKeycodes.xls only or already in the Labels.txt sheet ?
In other words is the Paste or the OpenText method the culprit ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[123FakeSt]

Well, the vendor requires a .txt fixed width file, which opens by default in Notepad. They are rather technically challenged (that coming from me!) thus all of the text manipulation. The double quotes push all of the characters over 1 position.

 

[123FakeSt]

To be specific, the labels.txt doc is quotelss. The updatekeycodes.xls is also quoteless. When i save the sheet as a .txt (i'll post the code) they appear, only on lines that contain a , or ' (probably other stuff as well).

Dim X As String
X = InputBox("Enter the File Name")
ActiveWorkbook.SaveAs FileName:="Q:\" & X & ".txt", FileFormat:=xlText, _
CreateBackup:=False
ActiveWorkbook.SendMail "contact@technicallychallengedvendor.com", X & " Catalog Request Export"

 

[PHV]

I've never heard Notepad will add itself double quotes when opening a file it thinks csv !

 

[SkipVought]

Guess what!

.csv == COMMA separated values

So...

if you have a VALUE containing a COMMA, Excel puts QUOTES around!

Get rid of commas in your data before exporting.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[123FakeSt]

Actually there's nothing about csv, everything i am working with is fixed width and .txt.

This is the code for that:

ActiveWorkbook.SaveAs FileName:="Q:\" & X & ".txt", FileFormat:=xlText, _
CreateBackup:=False

I'm going to investigate the ActiveWorkbook.SaveAs in help to see what i can do about it. I need the commas, as the contact is exported in SurName, GivenName format.

 

[PHV]

AFAIK, xlText is not a valid xlFileFormat value.
Try with FileFormat:=xlTextWindows

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[123FakeSt]

I went to the FileFormat Property Help and tried a bunch of them (xlCurrentPlatformText, xlTextWindows, etc.) , the only one that worked was xlTextPrinter. (i am using XL 97 so that may be the difference!)

I used that and all is well.

Thanks!

Rusty