Automatic conversion of text to columns

[Vlad2022]

Hi

I have a work PC, and whenever I open a .csv file by double-clicking on it it automatically opens in Excel and converts the data into columns. I have recently purchased a home PC, but whenever I open a .csv file on that one by double-clicking (same procedure as I do on the work PC) the file opens in Excel, but the information remains un-formatted (not in columns). I can then run the usual process by going into Data=>Text-to-columns, but since I work with many .csv file doing so every time is a bit of a hassle. Any ideas of how I can make my home PC convert the .csv information into columns automatically upon opening the file, just like my work PC does?

P.S. The .csv files I work with use ";" as separator, not commas.

 

[rlv01]

Check to be sure the Windows regional settings on your new home PC are the same as for your work PC. For example, on the PC I am using at the moment:

ANSI Code Page setting on this PC is 1252
OEM Console App Code Page setting on this PC is 437

The as-found decimal separator: The period [.] (Ascii 46)
The as-found list separator: The comma [,] (Ascii 44)
The as-found thousands separator: The comma [,] (Ascii 44)

Sub ListRegionalSettings()
    Dim Msg As String, SepCh As String, SepChDesc As String, SepType As String, SepMsg As String
    Dim ACP As String
    Dim OEMCP As String
    Dim CopySuccess As Boolean

    ACP = CreateObject("WScript.Shell").RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage\ACP")
    OEMCP = CreateObject("WScript.Shell").RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage\OEMCP")

    Msg = "ANSI Code Page setting on this PC is " & ACP & vbCr
    Msg = Msg & "OEM Console App Code Page setting on this PC is " & OEMCP & vbCr & vbCr
    SepType = xlDecimalSeparator
    GoSub SepDef
    SepType = xlListSeparator
    GoSub SepDef
    SepType = xlThousandsSeparator
    GoSub SepDef

    Select Case MsgBox(Msg & vbCr & vbCr & "Copy to windows clipboard?", vbQuestion + vbYesNo, "Windows Regional Settings")
    Case vbYes
        On Error Resume Next
        CopySuccess = CreateObject("htmlfile").ParentWindow.ClipboardData.SetData("Text", Msg)
        On Error GoTo 0
        If Not CopySuccess Then
            With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
                .SetText Msg
                .PutInClipboard
            End With
        End If
    End Select
    Exit Sub

SepDef:
    SepCh = CStr(Application.International(SepType))
    Select Case SepCh
    Case "."
        SepChDesc = "period"
    Case ","
        SepChDesc = "comma"
    Case ";"
        SepChDesc = "semi-colon"
    Case Else
        SepChDesc = "character"
    End Select

    Select Case SepType
    Case xlDecimalSeparator
        SepMsg = "decimal separator:" & Chr(9)
    Case xlListSeparator
        SepMsg = "list separator:" & Chr(9) & Chr(9)
    Case xlThousandsSeparator
        SepMsg = "thousands separator:" & Chr(9)
    Case Else
    End Select
    Msg = Msg & "The as-found " & SepMsg & "The " & SepChDesc & " [" & SepCh & "] (Ascii " & Asc(SepCh) & ")" & vbCr
    Return
End Sub

 

[Vlad2022]

Check to be sure the Windows regional settings on your new home PC are the same as for your work PC. For example, on the PC I am using at the moment:

ANSI Code Page setting on this PC is 1252
OEM Console App Code Page setting on this PC is 437

The as-found decimal separator: The period [.] (Ascii 46)
The as-found list separator: The comma [,] (Ascii 44)
The as-found thousands separator: The comma [,] (Ascii 44)

Sub ListRegionalSettings()
    Dim Msg As String, SepCh As String, SepChDesc As String, SepType As String, SepMsg As String
    Dim ACP As String
    Dim OEMCP As String
    Dim CopySuccess As Boolean

    ACP = CreateObject("WScript.Shell").RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage\ACP")
    OEMCP = CreateObject("WScript.Shell").RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage\OEMCP")

    Msg = "ANSI Code Page setting on this PC is " & ACP & vbCr
    Msg = Msg & "OEM Console App Code Page setting on this PC is " & OEMCP & vbCr & vbCr
    SepType = xlDecimalSeparator
    GoSub SepDef
    SepType = xlListSeparator
    GoSub SepDef
    SepType = xlThousandsSeparator
    GoSub SepDef

    Select Case MsgBox(Msg & vbCr & vbCr & "Copy to windows clipboard?", vbQuestion + vbYesNo, "Windows Regional Settings")
    Case vbYes
        On Error Resume Next
        CopySuccess = CreateObject("htmlfile").ParentWindow.ClipboardData.SetData("Text", Msg)
        On Error GoTo 0
        If Not CopySuccess Then
            With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
                .SetText Msg
                .PutInClipboard
            End With
        End If
    End Select
    Exit Sub

SepDef:
    SepCh = CStr(Application.International(SepType))
    Select Case SepCh
    Case "."
        SepChDesc = "period"
    Case ","
        SepChDesc = "comma"
    Case ";"
        SepChDesc = "semi-colon"
    Case Else
        SepChDesc = "character"
    End Select

    Select Case SepType
    Case xlDecimalSeparator
        SepMsg = "decimal separator:" & Chr(9)
    Case xlListSeparator
        SepMsg = "list separator:" & Chr(9) & Chr(9)
    Case xlThousandsSeparator
        SepMsg = "thousands separator:" & Chr(9)
    Case Else
    End Select
    Msg = Msg & "The as-found " & SepMsg & "The " & SepChDesc & " [" & SepCh & "] (Ascii " & Asc(SepCh) & ")" & vbCr
    Return
End Sub

Thank you very much for replying, I'll try! (I suspected myself that this has to do with regional settings of some sort)

 

[jkpieterse]

Or read this: https://jkp-ads.com/articles/importtext.asp