Tab delimitation

[ninash]

Hi,

I am trying to populate an excel spreadsheet from a tab delimited text file. Unfortunatly I have only done this with comma delimited files before and have no idea how to do it with a tab delimited file.

Could someone let me know how to identify a tab in VBA

Thanks in advance

 

[Kaah]

you cant indentify a tab character because in a text file a tab is just a a series of spaces.

You can either manually process the file through VBA to import it (tedious, slow, code intensive), or, you can record a macro which opens the file as a text file (fixed width obviously) and you set the column widths through excel. If you record this macro then you'll have a nifty little code segment which will open files of this format whenever you want.

This stuff may or may not be what you're after. I've done heaps of stuff with csv's and fixed width files so keep on my case if you need more details...

regards

Kaah.

 

[JustinEzequiel]

identify a tab in VBA --> vbTab

 

[JohnYingling]

The TAB character is a CHR(9). It certainly is not a series of spaces ALTHOUGH the VB Code editor converts TABS entered in code into a number of spaces that will "bump" up to the next even multiple of the tab specification.
If you are doing this in code, try Split.
Dim aryStr() As string
aryStr = Split(strWithTabs,Chr(9))

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort in VB or VBScript

 

[Kaah]

Use this command:

Workbooks.OpenText <parameters>

Like i said before, record the file being opened into a macro to get the correct parameter settings. This will open the file in excel with tab delimiters working correctly.

You wont have to do any coding at all using this solution. Never underestimate the power of being able to record a macro. Much of the time you'll find out all sorts of coding tricks by using this functionality.

Here's an example of the opentext command that was generated when I recorded the opening of a tab delimited file:

'|||||||||||||||||||||||||||||||||||||||||||||||||

Sub OpenTabDelimitedFile()
'
' OpenTabDelimitedFile Macro

'

'
ChDir &quot;C:\TCM\Database\Metrec\documents\James Docs 081200&quot;
Workbooks.OpenText FileName:= _
&quot;June00balsht1.txt&quot;, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
End Sub

'||||||||||||||||||||||||||||||||||||||||||||||||||||||

The only thing that would need to be changed would be the last parameter FileInfo which is an array of the sizes and formats of each field in the file. To get this parameter correct you should definately record the file open process and let excel generate it. The above statement is complicated and hard to follow, but it will save reams and reams of code that you'd need to do it any other way.

Hope this helps,

K.

 

[ninash]

Thanks guys
and even though I didn't get it to work exactly the way I wanted the end result works fine