Import Text file with more than one text separator in to excel

B

bennyys

New Member
Joined
Jun 22, 2017
Messages
17
Hi,
I am working on input text file in to excel.
I got code from Cpearson to this purpose

Code: 
[COLOR=#333333][FONT=monospace]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' ImportTextFile[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' This imports a text file into Excel.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Public Sub ImportTextFile(FName As String, Sep As String)[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]Dim RowNdx As Long[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Dim ColNdx As Integer[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Dim TempVal As Variant[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Dim WholeLine As String[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Dim Pos As Integer[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Dim NextPos As Integer[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Dim SaveColNdx As Integer[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]'On Error GoTo EndMacro:[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]SaveColNdx = ActiveCell.Column[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]RowNdx = ActiveCell.row[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]Open FName For Input Access Read As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] [/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]While Not EOF(1)[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , WholeLine[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]If Right(WholeLine, 1) <> Sep Then[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]WholeLine = WholeLine & Sep[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]End If[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]On Error Resume Next[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]ColNdx = SaveColNdx[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Pos = 1[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]NextPos = InStr(Pos, WholeLine, Sep)[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]While NextPos >= 1[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]TempVal = Mid(WholeLine, Pos, NextPos - Pos)[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Cells(RowNdx, ColNdx).value = TempVal[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Pos = NextPos + 1[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]ColNdx = ColNdx + 1[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]NextPos = InStr(Pos, WholeLine, Sep)[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Wend[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]RowNdx = RowNdx + 1[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Wend[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]EndMacro:[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]On Error GoTo 0[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] [/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' END ImportTextFile[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]End Sub[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]'Since ImportTextFile takes input parameters, it should be called from other VBA code:[/FONT][/COLOR]

Vba Code for calling ImportTextFile

Code: 
[COLOR=#333333][FONT=monospace]Sub DoTheImport()[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Dim FileName As Variant[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Dim Sep As String[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]FileName = Application.GetOpenFilename(FileFilter:="Text File (*.vpd),*.vpd") '(*.txt),*.txt")[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]If FileName = False Then[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' user cancelled, get out[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]End 'Exit Sub[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]End If[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]'=====================================================================[/FONT][/COLOR]


[COLOR=#333333][FONT=monospace]'Dim Msg, Style, Title, Help, Ctxt, Response, MyString[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]Msg = "You are about to Open File " & FileName & _[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]" Do you want to continue? " ' Define message.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]title = "File Confirmation" ' Define title.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]help = "DEMO.HLP" ' Define Help file.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Ctxt = 1000 ' Define topic[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' context.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' Display message.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Response = MsgBox(Msg, Style, title, help, Ctxt)[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]If Response = vbYes Then ' User chose Yes.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]MyString = "Yes" ' Perform some action.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]'====================[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]'clear all data and calculations[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]4 Range("A19:J50000").Select[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Selection.ClearContents[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]With Selection.Interior[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].Pattern = xlNone[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' .TintAndShade = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' .PatternTintAndShade = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]End With[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]With Selection.Font[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].ColorIndex = xlAutomatic[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].TintAndShade = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]End With[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].HorizontalAlignment = xlCenter[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]End With[/FONT][/COLOR]


[COLOR=#333333][FONT=monospace]' Range("C19:F20").Select[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' Selection.ClearContents[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]Range("A19").Select[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]'====================[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]GoTo 5[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]Else ' User chose No.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]MyString = "No" ' Perform some action.[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]End 'Exit Sub 'GoTo 20[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]End If[/FONT][/COLOR]

[COLOR=#333333][FONT=monospace]5 Sep = Chr(9) 'vbTab 'Application.InputBox("Enter a separator character.", Type:=2)[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]If Sep = vbNullString Then[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' user cancelled, get out[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Exit Sub[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]End If[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]Debug.Print "FileName: " & FileName, "Separator: " & Sep[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]ImportTextFile FName:=CStr(FileName), Sep:=CStr(Sep)[/FONT][/COLOR]



[COLOR=#333333][FONT=monospace]End Sub[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]' END DoTheImport[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]

My question is if I want to import textfile with more than one separator character, in my case "tab"(chr(9) ascii) and "space"(chr(32) ascii)?
I guess that this part need to modify but so far I failed it

Code: 
[COLOR=#333333][FONT=monospace]5 Sep = Chr(9) 'vbTab 'Application.InputBox("Enter a separator character.", Type:=2)[/FONT][/COLOR]

Thank you in advance for all your help.

Regards,
Benny
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

L
  • Question Question
vba code adjustment when importing csv file into excel
Replies
4
Views
1K
londa_vba
L
L
  • Solved
Help! Importing CSV file into Excel using VBA but I do not want the first 10 rows imported
Replies
1
Views
672
John_w
J
Y
  • Question Question
VBA - Text Qualifier - removal
Replies
1
Views
3K
GWteB
GWteB
J
  • Question Question
Automatically import .txt files into separate sheets in Excel
Replies
1
Views
724
John_w
J
L
  • Solved
Import multiple text files to separated sheets onto current workbook
Replies
6
Views
1K
llbac
L

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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