changing t using

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Changing ^t to ; using vba

Changing ^t to ; using vba

resolvedResolved · 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 courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Hide separate columns in Excel 2010

If you want to hide columns not adjacent to each other for example, Columns A, C and E then:-

1) Click on the fist column to be hidden i.e. A

2) Press and hold down the CTRL key

3) While holding the CTRL key, left click on the rest of the columns you want to hide i.e. C and E

4) Right click and choose Hide

View all Excel hints and tips


Server loaded in 0.08 secs.