98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Changing ^t to ; using vba
Changing ^t to ; using vba
Resolved · High Priority · Version 2010
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
Wed 4 Jun 2014: Automatically marked as resolved.
Training information:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Hide separate columns in Excel 2010If you want to hide columns not adjacent to each other for example, Columns A, C and E then:- |