David has attended:
No courses
Changing ^t to ; using vba
I'm having an issue with some coding regarding vba.
I start with a .txt file downloaded from SAP. I then have to open up the file in excel to make some changes. When the file is downloaded it is seperated by ; , but when I import this into excel it changes the ; to a tab character ^t.
Once I have made the changes I then save the file as .txt file. But I the have the issue of having to change the ^t back to ;. I'm using replace in word to currently do it, but it could be the case that in vba it's not the right way to do it?
I am trying to do this in word using vba at the moment, but it does not seem to work. My code is below:
Sub OpenOstomed1Word()
Const wdReplaceAll = 2
Dim objWord As Object
Dim objSelection As Object
Dim objDoc As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Open("I:\PC-HC\03.Operations\01.Departmental Access\05.Team Logistic\New Patient Uploader\Files\Ostomed1.txt")
Set objSelection = objWord.Selection
With objSelection.Find.Text = """"
objSelection.Find.Forward = True
objSelection.Find.MatchWholeWord = True
objSelection.Find.Replacement.Text = ""
objSelection.Find.Execute , , , , , , , , , , wdReplaceAll
End With
With objSelection.Find.Text = "^t"
objSelection.Find.Forward = True
objSelection.Find.MatchWholeWord = True
objSelection.Find.Replacement.Text = ";"
objSelection.Find.Execute , , , , , , , , , , wdReplaceAll
End With
RE: Changing ^t to ; using vba
Hi David,
Nice to hear from you.
Please try the code below. I have tested it in one of my Word files and it was working, but sometimes imported data can act a little strange.
Please let me know if it is working. If not I may need to have a file with some example data.
Sub RemoveTabCharacters()
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^t"
.Replacement.Text = ";"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu