Replacing characters from a copied text file

Solari

New Member
Joined
Jun 1, 2018
Messages
16
Hi

My first post in this glorious forum so please be gentle :)

I'm trying to get Excel to copy data from a text file and then arranging it for me, and it works like a charm. Except for 2 things!
The characters ┼ and ─ seems impossible to replace :(

I have figured out that they should be ANSI 197 and 196 but I still won't succeed!

Please guide me :)

My code:

Sub Skapa_ANLU()




Dim ret




ret = Application.GetOpenFilename()






Range("A1").Select
If ret <> False Then
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & ret, Destination:=Range("$A$1"))

' .Name = "test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
' .TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If


Columns("M:S").Select
Selection.Delete Shift:=xlToLeft

Range("A:C").Select


Selection.Replace "õ", "ä", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False


Selection.Replace What:="Õ", Replacement:="å", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False


Selection.Replace What:="Í", Replacement:="Ö", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False


Selection.Replace Chr(197), "Å", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

Selection.Replace What:="÷", Replacement:="ö", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False


Selection.Replace Chr(196), Replacement:="Ä", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False


Columns("E:M").Select
Selection.NumberFormat = "#,##0"

Range("A1").Select

End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try Chrw(9472) & ChrW(9532)
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Hold down the Alt key & type 196 into a cell.
then I ran this
Code:
?ascw(activecell.Value)
In the Immediate window of the VBE & it gave the the number
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top