Importing Text Files in Excel

[mohsin74]

Hi there,

I have a data in text files. I need to write some macros / vba to import into Excel . The text files is base on command syntax, as this format;

Command-X;
Data1 Data2 Data3
--------- --------- --------
valuex valueb valuec

Command Executed

So My macro's have to search the command-X, search a Data then collect the value.

So any idea, how can i start with, or sample macro?!


Thank you for your help

[Killer42]

...So My macro's have to search the command-X, search a Data then collect the value.

So any idea, how can i start with, or sample macro?!

A good way to get started with this kind of thing is to tell Excel to record a new macro, then do the operation yourself. This generates the code for you. Then you can edit the macro and play with the code.

[mohsin74]

Hi killer

The problem is, the data is not the same order between the command syntax. So when imported it to excel, its difficult to arrange either using tab or delimeted.
But i will try to do per command syntax, which is have to extract manually first..

[Killer42]

The problem is, the data is not the same order between the command syntax. So when imported it to excel, its difficult to arrange either using tab or delimeted.
But i will try to do per command syntax, which is have to extract manually first..

Oh, so you mean your code needs to look at the command to decide what data to collect? Yes, that complicates things a bit.

Seems reasonably simple to do in VB, though. I'm sure we can come up with something. I'll get back to this at lunch time (in a few hours) and see if I can help.

[diSangro]

Hi killer

The problem is, the data is not the same order between the command syntax. So when imported it to excel, its difficult to arrange either using tab or delimeted.
But i will try to do per command syntax, which is have to extract manually first..

Hello Moshin74!

I think you should just read your text file , find a key string and copy the corresponding value on your worksheet. Here is an example....

suppose your file \text.txt
contains the following text:

=============
Command-X;
---Data1 Data2 Data3
Data1 Data2 Data3
--------- --------- --------
valuex valueb valuec
Command Executed



Then you can try this vb code :

Private Sub CommandButton1_Click()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fs, f
Dim i As Integer
Dim count, myLine ' string count,buffer for file reading
Dim Sfind 'string to find
i = 1
Sfind = "Data"

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("\text.txt", ForReading, TristateFalse)
myLine = " "
Do
myLine = f.Readline
MsgBox myLine, vbInformation ' show line buffer
count = InStr(1, myLine, Sfind, 1)
While count <> 0
Worksheets("MySheet").Cells(1, i) = Mid(myLine, count + Len(Sfind), 1)
count = InStr(count + Len(Sfind), myLine, Sfind, 1)
i = i + 1
Wend
Loop Until myLine = ""
f.Close

End Sub



It will search the keyword "Data" in your .txt, and it will store the following character in your worksheet ("MySheet") for each occurence (1,2,3,1,2,3) on the same line. If you add more details it can be improved !
Bye !