Merging data

[Guest]

I have a text file that is generated by another app and was wondering what
method would allow me to use its content to populate fields in an Excel
spreadsheet. Similar to merging word w/ access.

If you could point me to an example, website with explanations it would be
greatly appreciated!

Thank you.

Daniel P

 

[Guest]

Ther are lots of way to drop test into excel.

1) Just paste data into the speadsheet. Then use Data Menu - Text to columns.

2) Data Menu - Import external data - import data

3) The first two methods use similar menu options to break data into each
column such as Commar Delimited. Another metthod is open a text file in VBA
and write your own custom macro to read tthe data and put the data into cells.

4) I've used a fouth method occasionally. I write VBA code which open a
read text file and a write text file. I coverter the read data into CSV
(commar seperated values) and put the data into the write text file. Then I
open the CSV file and excel wil automatticaly put the data into the cells.

 

[Guest]

I need to do this using vba and completly automate the process. Could you
give me some basic code to get started?

Daniel P

 

[Guest]

It is hard to give code without seeing the data. The 1st thing I would try
is importing the data Using Data - Import External Data - Extternal Data and
see if this reads the data properly.

If this works, the capture the command by go to Tools Macro - Record New
Macro. the recorded macro can be used in your code.


Here is an example of a file that reads and writes text data. It doesn't
have code that puts data into the cells. It simply converts text data into
CSV. Then the CSV can be read into Excel

I'm very good at working with text files and will be glad to give you more
help. You request is very vague and really need to know more about the data
to give you a bettter answer.

Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "longtext.txt"
WriteFileName = "longtext.csv"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)


Do While tsread.atendofstream = False

InputLine = tsread.ReadLine


If (InStr(InputLine, "TEXT") > 0) Then

If Len(OutputLine) > 0 Then
tswrite.WriteLine OutputLine
OutputLine = ""

End If
Else
If Len(OutputLine) > 0 Then
OutputLine = OutputLine + "," + InputLine
Else
OutputLine = InputLine
End If
End If

Loop

tswrite.Close
tsread.Close

Exit Sub
End Sub