Keep special character when importing through VBA

J

JoeRooney

Board Regular
Joined
Nov 27, 2017
Messages
173
Office Version
  1. 365
Hi ,

I have a problem when importing Spanish data , the data contains ó but when importing excel concerts this to ├│

The data is contained in column D and I have this set to TEXT when importing but still does not work

I have tried, "find and replace" and a formula to amend this which is fine but when I put this into VBA, VBA reads ├│ as ?? and does not convert it back to its original format.

Any suggestions on how to resolve this?

VBA Code: 
Sub Import_CX()

' Prompts user to select policy file to import

   Dim strFile As String
   Dim w As Workbook: Set w = ActiveWorkbook
   Dim i As Long, sh As Worksheet, ShName As String
    Application.DisplayAlerts = False
    
    Sheets.Add.Name = "Raw Data"


 
    MsgBox "Please select the CX file you wish to import..."

   With Application.FileDialog(msoFileDialogOpen)
      .AllowMultiSelect = False
      .InitialView = msoFileDialogViewList
      .Title = "Please select the policy file you wish to import...."
      .Show

      If .SelectedItems.Count = 1 Then
         strFile = .SelectedItems(1)
      End If

   End With

   If strFile = "" Then
      MsgBox "No file selected...", vbInformation, "Error"
      Exit Sub
   End If


   With ActiveSheet.QueryTables.Add(Connection:= _
      "TEXT;" & strFile, Destination:=Range("$A$1"))
        
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = ";"
        .TextFileColumnDataTypes = Array(1, 2, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
   

    Cells.Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    
    Dim Answer As Integer
    
    Answer = MsgBox("Would you also like to import more files?", vbYesNo + vbQuestion, "Import Files")
    
    If Answer = vbYes Then
    
    ActiveSheet.Cells.unmerge
    
'    Rows("1:1").Select
'    ActiveWindow.FreezePanes = True
    
    Cells.Select
    Cells.EntireColumn.AutoFit
    
    Range("A1").Select
    
    Range("A1").End(xlDown).Offset(1).Select
    
     Call Import_ItalyCX1
Else
     
     MsgBox "CX file successfully imported"
     
End If
    
    Application.DisplayAlerts = True

    End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
What about using another mechanism than a QueryTable to import the file? Since it's a semicolon delimited file, what happens if you open it as a text file using a semicolon delimiter?

VBA Code: 
    Workbooks.OpenText Filename:="C:\Users\xxxx\Documents\tmpsc.txt", _
    Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, _
    Space:=False, Other:=False, TrailingMinusNumbers:=True
 
Upvote 0
rlv01 said:
What about using another mechanism than a QueryTable to import the file? Since it's a semicolon delimited file, what happens if you open it as a text file using a semicolon delimiter?

VBA Code: 
    Workbooks.OpenText Filename:="C:\Users\xxxx\Documents\tmpsc.txt", _
    Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, _
    Space:=False, Other:=False, TrailingMinusNumbers:=True
Click to expand...

Hi, Thanks for suggestion but fortunately still the same result , special characters being transformed.
 
Upvote 0
JoeRooney said:
Hi, Thanks for suggestion but fortunately still the same result , special characters being transformed.
Click to expand...

The "Origin" parameter allows you to match the code page of the machine that generated the text file. The default 437 is OEM United States. If I were in your position, I would experiment using different code page integers for known latin/hispanic countries.

learn.microsoft.com

Code Page Identifiers - Win32 apps

The following table defines the available code page identifiers.
learn.microsoft.com
 
Upvote 0
Solution
rlv01 said:
The "Origin" parameter allows you to match the code page of the machine that generated the text file. The default 437 is OEM United States. If I were in your position, I would experiment using different code page integers for known latin/hispanic countries.

learn.microsoft.com

Code Page Identifiers - Win32 apps

The following table defines the available code page identifiers.
learn.microsoft.com
Click to expand...
Great suggestion , .TextFilePlatform = 65001 seems to have done the trick
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Change macro range from column A to B
2
Replies
10
Views
136
sachin483
S
R
  • Question Question
Compose Email VBA Help
Replies
1
Views
117
Logit
Logit
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
107
RoryA
RoryA
S
  • Question Question
Help with Pivot Table Macro
Replies
0
Views
31
SCPbrito
S
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
242
Jette3173
J

Forum statistics

Threads
1,224,505
Messages
6,179,147
Members
452,891
Latest member
JUSTOUTOFMYREACH

Share this page

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