Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Hi Kevin,

The file extension needn't affect your macro's ability to read the *.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause other problems. Plus, you'll still need to understand those files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in message ...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:
Hi Kevin,

The file extension needn't affect your macro's ability to read the *.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause other problems. Plus, you'll still need to understand those files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read the *.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause other problems. Plus, you'll still need to understand those files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Kevin,

You should probably share your macro as it is now and explain what you want
to accomplish. If you have file names listed in a workbook or a text file
and they include the full path, it is certainly possible to alter the
extensions of the referenced files but it is also possible to get Excel to
regard them as the text files they are and act accordingly. Depending on
your goal and the scenario, the second option is probably far safer.

Steve Yandl


wrote in message
...
Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read the
*.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause other
problems. Plus, you'll still need to understand those files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in
...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Steve,

Given below is the macro I have. It looks for *.xls files in a folder
(Path) and then retrieves D1 to D20 values, places them in column A.
Likewise, E1 to E20 values are placed in column B.

I need to fetch those values from *.log files. I am not sure if the
macro would work, unless and until I change the *.log to *.xls

Kevin

Path = Dir(MyPath & "*.xls")
Set sh = Sheets("Sheet1")

Fnum = 0
Do While Path < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = Path
Path = Dir()
Loop

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

rnum = LastRow(sh)

Set destrange = sh.Cells(rnum + 1, "A")

GetData MyPath & MyFiles(Fnum), "Sheet1", "D1:D20",
destrange, False, False

Set destrange = sh.Cells(rnum + 1, "B")
GetData MyPath & MyFiles(Fnum), "Sheet1", "E1:E20",
destrange, False, False
Next
End If


On Apr 14, 4:51 pm, "Steve Yandl" wrote:
Kevin,

You should probably share your macro as it is now and explain what you want
to accomplish. If you have file names listed in a workbook or a text file
and they include the full path, it is certainly possible to alter the
extensions of the referenced files but it is also possible to get Excel to
regard them as the text files they are and act accordingly. Depending on
your goal and the scenario, the second option is probably far safer.

Steve Yandl

wrote in message

...

Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read the
*.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause other
problems. Plus, you'll still need to understand those files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in
...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Hi Kevin,

Here's a simple macro to read a delimited text file named "MyLog.Log" in the current folder. All the values are stored in an array
from which you can populate your destination worksheet. With large amounts of data this approach will be significantly faster (and
less problematic) than renaming the files, opening them as worksheets then copying from there to your destination worksheet. I've
read over 12 million records into arrays this way.

Sub Readfile()
Dim Data() ' Array
Dim DataSet
Dim i As Integer
Dim j As Integer
DataSet = ThisWorkbook.Path & "\MyLog.Log"
If Dir(DataSet) < "" Then
i = 1
Open DataSet For Input As #1
Do Until EOF(1)
ReDim Preserve Data(4, i)
Input #1, Data(1, i), Data(2, i), Data(3, i), Data(4, i)
i = i + 1
Loop
Close #1
End If
DataSet = ""
For j = 1 to i - 1
DataSet = DataSet & vbCrLf & Data(1, j) & " " & Data(2, j)
Next
Msgbox DataSet
End Sub

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in message ...
Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read the *.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause other problems. Plus, you'll still need to understand those
files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Kevin,

Do you know the format of the log files or does it vary? Specifically, are
the values comma separated, tab separated, space separated or something
else? If you want the 4th and 5th values (columns D and E) from the first
20 lines of some text files, there are ways to retrieve them, especially if
you know how the values are separated.


Steve



wrote in message
...
Steve,

Given below is the macro I have. It looks for *.xls files in a folder
(Path) and then retrieves D1 to D20 values, places them in column A.
Likewise, E1 to E20 values are placed in column B.

I need to fetch those values from *.log files. I am not sure if the
macro would work, unless and until I change the *.log to *.xls

Kevin

Path = Dir(MyPath & "*.xls")
Set sh = Sheets("Sheet1")

Fnum = 0
Do While Path < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = Path
Path = Dir()
Loop

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

rnum = LastRow(sh)

Set destrange = sh.Cells(rnum + 1, "A")

GetData MyPath & MyFiles(Fnum), "Sheet1", "D1:D20",
destrange, False, False

Set destrange = sh.Cells(rnum + 1, "B")
GetData MyPath & MyFiles(Fnum), "Sheet1", "E1:E20",
destrange, False, False
Next
End If


On Apr 14, 4:51 pm, "Steve Yandl" wrote:
Kevin,

You should probably share your macro as it is now and explain what you
want
to accomplish. If you have file names listed in a workbook or a text
file
and they include the full path, it is certainly possible to alter the
extensions of the referenced files but it is also possible to get Excel
to
regard them as the text files they are and act accordingly. Depending on
your goal and the scenario, the second option is probably far safer.

Steve Yandl

wrote in message

...

Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read the
*.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause
other
problems. Plus, you'll still need to understand those files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in
...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt
and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Macropod,

Thanks for the macro. It sure does help but I get the values in the
Msgbox.

How would I display the content in the worksheet instead of a Msgbox?

Range("A1").value = DataSet doesn't do the trick!

Please let me know

Thanks,
Kevin

On Apr 14, 5:34 pm, "macropod" wrote:
Hi Kevin,

Here's a simple macro to read a delimited text file named "MyLog.Log" in the current folder. All the values are stored in an array
from which you can populate your destination worksheet. With large amounts of data this approach will be significantly faster (and
less problematic) than renaming the files, opening them as worksheets then copying from there to your destination worksheet. I've
read over 12 million records into arrays this way.

Sub Readfile()
Dim Data() ' Array
Dim DataSet
Dim i As Integer
Dim j As Integer
DataSet = ThisWorkbook.Path & "\MyLog.Log"
If Dir(DataSet) < "" Then
i = 1
Open DataSet For Input As #1
Do Until EOF(1)
ReDim Preserve Data(4, i)
Input #1, Data(1, i), Data(2, i), Data(3, i), Data(4, i)
i = i + 1
Loop
Close #1
End If
DataSet = ""
For j = 1 to i - 1
DataSet = DataSet & vbCrLf & Data(1, j) & " " & Data(2, j)
Next
Msgbox DataSet
End Sub

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read the *.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause other problems. Plus, you'll still need to understand those
files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Steve,

The format of the log files doesnt vary. The values are tab
separated.

Please let me know the way to retrieve the values. If I change the
extension to *.xls I can accomplish way I am trying to do.

Kevin

On Apr 14, 5:39 pm, "Steve Yandl" wrote:
Kevin,

Do you know the format of the log files or does it vary? Specifically, are
the values comma separated, tab separated, space separated or something
else? If you want the 4th and 5th values (columns D and E) from the first
20 lines of some text files, there are ways to retrieve them, especially if
you know how the values are separated.

Steve

wrote in message

...

Steve,

Given below is the macro I have. It looks for *.xls files in a folder
(Path) and then retrieves D1 to D20 values, places them in column A.
Likewise, E1 to E20 values are placed in column B.

I need to fetch those values from *.log files. I am not sure if the
macro would work, unless and until I change the *.log to *.xls

Kevin

Path = Dir(MyPath & "*.xls")
Set sh = Sheets("Sheet1")

Fnum = 0
Do While Path < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = Path
Path = Dir()
Loop

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

rnum = LastRow(sh)

Set destrange = sh.Cells(rnum + 1, "A")

GetData MyPath & MyFiles(Fnum), "Sheet1", "D1:D20",
destrange, False, False

Set destrange = sh.Cells(rnum + 1, "B")
GetData MyPath & MyFiles(Fnum), "Sheet1", "E1:E20",
destrange, False, False
Next
End If

On Apr 14, 4:51 pm, "Steve Yandl" wrote:
Kevin,

You should probably share your macro as it is now and explain what you
want
to accomplish. If you have file names listed in a workbook or a text
file
and they include the full path, it is certainly possible to alter the
extensions of the referenced files but it is also possible to get Excel
to
regard them as the text files they are and act accordingly. Depending on
your goal and the scenario, the second option is probably far safer.

Steve Yandl

wrote in message

...

Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read the
*.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause
other
problems. Plus, you'll still need to understand those files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in
...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt
and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Hi Kevin,

The code was for illustration only. I thought you'd be able to adapt it to suit your needs. Anyway:

Sub Readfile()
Dim Data(2, 1) ' Array
Dim DataSet
Dim sh As Worksheet
Dim rnum As Integer
With ThisWorkbook
DataSet = .Path & "\MyLog.Log"
If Dir(DataSet) < "" Then
Set sh = .Sheets("Sheet1")
rnum = sh.Range("A65536").End(xlUp).Row
Open DataSet For Input As #1
Do Until EOF(1)
Input #1, Data(1, 1), Data(2, 1)
rnum = rnum + 1
sh.Cells(rnum, "A") = Data(1, 1)
sh.Cells(rnum, "B") = Data(2, 1)
Loop
Close #1
End If
End With
End Sub

I'll leave it to you to modify the code to support looping through the files in the folder.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in message ...
Macropod,

Thanks for the macro. It sure does help but I get the values in the
Msgbox.

How would I display the content in the worksheet instead of a Msgbox?

Range("A1").value = DataSet doesn't do the trick!

Please let me know

Thanks,
Kevin

On Apr 14, 5:34 pm, "macropod" wrote:
Hi Kevin,

Here's a simple macro to read a delimited text file named "MyLog.Log" in the current folder. All the values are stored in an
array
from which you can populate your destination worksheet. With large amounts of data this approach will be significantly faster
(and
less problematic) than renaming the files, opening them as worksheets then copying from there to your destination worksheet. I've
read over 12 million records into arrays this way.

Sub Readfile()
Dim Data() ' Array
Dim DataSet
Dim i As Integer
Dim j As Integer
DataSet = ThisWorkbook.Path & "\MyLog.Log"
If Dir(DataSet) < "" Then
i = 1
Open DataSet For Input As #1
Do Until EOF(1)
ReDim Preserve Data(4, i)
Input #1, Data(1, i), Data(2, i), Data(3, i), Data(4, i)
i = i + 1
Loop
Close #1
End If
DataSet = ""
For j = 1 to i - 1
DataSet = DataSet & vbCrLf & Data(1, j) & " " & Data(2, j)
Next
Msgbox DataSet
End Sub

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read the *.txt and *.log files' contents. In any event, changing
the
extension doesn't turn the files into Excel files and could cause other problems. Plus, you'll still need to understand
those
files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

On Apr 14, 7:00 pm, "macropod" wrote:
Hi Kevin,

The code was for illustration only. I thought you'd be able to adapt it to suit your needs. Anyway:

Sub Readfile()
Dim Data(2, 1) ' Array
Dim DataSet
Dim sh As Worksheet
Dim rnum As Integer
With ThisWorkbook
DataSet = .Path & "\MyLog.Log"
If Dir(DataSet) < "" Then
Set sh = .Sheets("Sheet1")
rnum = sh.Range("A65536").End(xlUp).Row
Open DataSet For Input As #1
Do Until EOF(1)
Input #1, Data(1, 1), Data(2, 1)
rnum = rnum + 1
sh.Cells(rnum, "A") = Data(1, 1)
sh.Cells(rnum, "B") = Data(2, 1)
Loop
Close #1
End If
End With
End Sub

I'll leave it to you to modify the code to support looping through the files in the folder.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Macropod,

Thanks for the macro. It sure does help but I get the values in the
Msgbox.

How would I display the content in the worksheet instead of a Msgbox?

Range("A1").value = DataSet doesn't do the trick!

Please let me know

Thanks,
Kevin

On Apr 14, 5:34 pm, "macropod" wrote:
Hi Kevin,

Here's a simple macro to read a delimited text file named "MyLog.Log" in the current folder. All the values are stored in an
array
from which you can populate your destination worksheet. With large amounts of data this approach will be significantly faster
(and
less problematic) than renaming the files, opening them as worksheets then copying from there to your destination worksheet. I've
read over 12 million records into arrays this way.

Sub Readfile()
Dim Data() ' Array
Dim DataSet
Dim i As Integer
Dim j As Integer
DataSet = ThisWorkbook.Path & "\MyLog.Log"
If Dir(DataSet) < "" Then
i = 1
Open DataSet For Input As #1
Do Until EOF(1)
ReDim Preserve Data(4, i)
Input #1, Data(1, i), Data(2, i), Data(3, i), Data(4, i)
i = i + 1
Loop
Close #1
End If
DataSet = ""
For j = 1 to i - 1
DataSet = DataSet & vbCrLf & Data(1, j) & " " & Data(2, j)
Next
Msgbox DataSet
End Sub

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read the *.txt and *.log files' contents. In any event, changing
the
extension doesn't turn the files into Excel files and could cause other problems. Plus, you'll still need to understand
those
files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Thanks Macropod. Will give this a try.

Kevin

On Apr 14, 7:00 pm, "macropod" wrote:
Hi Kevin,

The code was for illustration only. I thought you'd be able to adapt it to suit your needs. Anyway:

Sub Readfile()
Dim Data(2, 1) ' Array
Dim DataSet
Dim sh As Worksheet
Dim rnum As Integer
With ThisWorkbook
DataSet = .Path & "\MyLog.Log"
If Dir(DataSet) < "" Then
Set sh = .Sheets("Sheet1")
rnum = sh.Range("A65536").End(xlUp).Row
Open DataSet For Input As #1
Do Until EOF(1)
Input #1, Data(1, 1), Data(2, 1)
rnum = rnum + 1
sh.Cells(rnum, "A") = Data(1, 1)
sh.Cells(rnum, "B") = Data(2, 1)
Loop
Close #1
End If
End With
End Sub

I'll leave it to you to modify the code to support looping through the files in the folder.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Macropod,

Thanks for the macro. It sure does help but I get the values in the
Msgbox.

How would I display the content in the worksheet instead of a Msgbox?

Range("A1").value = DataSet doesn't do the trick!

Please let me know

Thanks,
Kevin

On Apr 14, 5:34 pm, "macropod" wrote:
Hi Kevin,

Here's a simple macro to read a delimited text file named "MyLog.Log" in the current folder. All the values are stored in an
array
from which you can populate your destination worksheet. With large amounts of data this approach will be significantly faster
(and
less problematic) than renaming the files, opening them as worksheets then copying from there to your destination worksheet. I've
read over 12 million records into arrays this way.

Sub Readfile()
Dim Data() ' Array
Dim DataSet
Dim i As Integer
Dim j As Integer
DataSet = ThisWorkbook.Path & "\MyLog.Log"
If Dir(DataSet) < "" Then
i = 1
Open DataSet For Input As #1
Do Until EOF(1)
ReDim Preserve Data(4, i)
Input #1, Data(1, i), Data(2, i), Data(3, i), Data(4, i)
i = i + 1
Loop
Close #1
End If
DataSet = ""
For j = 1 to i - 1
DataSet = DataSet & vbCrLf & Data(1, j) & " " & Data(2, j)
Next
Msgbox DataSet
End Sub

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro I
have references the column A and B of the excel file. Since, I have a
*.log file, I am not sure how my macro would associate the columns in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read the *.txt and *.log files' contents. In any event, changing
the
extension doesn't turn the files into Excel files and could cause other problems. Plus, you'll still need to understand
those
files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in ...
Hello group,

I have a macro to pull the values in row A and B of a workbook, to
another workbook. I have some values in the files that have *.txt and
*.log extension.

Is there any macro to change the extension of the file (*.txt and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Thanks Macropod, I will give this a try.

Kevin

Kevin,

You have a couple of options. I'd suggest using the 'OpenText' method of
the 'Workbooks' collection. You will have to feed the file name as an
argument but it really doesn't matter what the extension is.


Steve

wrote in message
...
Steve,

The format of the log files doesnt vary. The values are tab
separated.

Please let me know the way to retrieve the values. If I change the
extension to *.xls I can accomplish way I am trying to do.

Kevin

On Apr 14, 5:39 pm, "Steve Yandl" wrote:
Kevin,

Do you know the format of the log files or does it vary? Specifically,
are
the values comma separated, tab separated, space separated or something
else? If you want the 4th and 5th values (columns D and E) from the
first
20 lines of some text files, there are ways to retrieve them, especially
if
you know how the values are separated.

Steve

wrote in message

...

Steve,

Given below is the macro I have. It looks for *.xls files in a folder
(Path) and then retrieves D1 to D20 values, places them in column A.
Likewise, E1 to E20 values are placed in column B.

I need to fetch those values from *.log files. I am not sure if the
macro would work, unless and until I change the *.log to *.xls

Kevin

Path = Dir(MyPath & "*.xls")
Set sh = Sheets("Sheet1")

Fnum = 0
Do While Path < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = Path
Path = Dir()
Loop

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

rnum = LastRow(sh)

Set destrange = sh.Cells(rnum + 1, "A")

GetData MyPath & MyFiles(Fnum), "Sheet1", "D1:D20",
destrange, False, False

Set destrange = sh.Cells(rnum + 1, "B")
GetData MyPath & MyFiles(Fnum), "Sheet1", "E1:E20",
destrange, False, False
Next
End If

On Apr 14, 4:51 pm, "Steve Yandl" wrote:
Kevin,

You should probably share your macro as it is now and explain what you
want
to accomplish. If you have file names listed in a workbook or a text
file
and they include the full path, it is certainly possible to alter the
extensions of the referenced files but it is also possible to get
Excel
to
regard them as the text files they are and act accordingly. Depending
on
your goal and the scenario, the second option is probably far safer.

Steve Yandl

wrote in message

...

Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this
is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro
I
have references the column A and B of the excel file. Since, I have
a
*.log file, I am not sure how my macro would associate the columns
in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to
read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read
the
*.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause
other
problems. Plus, you'll still need to understand those files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in
...
Hello group,

I have a macro to pull the values in row A and B of a workbook,
to
another workbook. I have some values in the files that have
*.txt
and
*.log extension.

Is there any macro to change the extension of the file (*.txt
and
*.log) to *.xls? Please let know.

Thanks,
Kevin

Kevin,

Here is an example using the Scripting file system object rather than the
OpenText method.

It looks at the folder named "C:\Test". Inside that folder, it locates
every file that has the extension "log". It opens the text files with log
extension and reads them line by line. For lines before line 21, it creates
an array of strings considering the tab character as separator. It
retrieves the fourth and fifth values from those lines that have sufficient
data and uses those values to append columns A and B on Sheet 1 of the
active workbook.

_____________________________________

Sub DataFromLogFiles()
Const ForReading = 1

Dim strPath As String
Dim strLine As String
Dim R As Integer
Dim L As Integer

strPath = "C:\Test"

R = Sheets(1).UsedRange.Rows.Count + 1

Set FSO = CreateObject("Scripting.FileSystemObject")
Set fldr = FSO.GetFolder(strPath)

For Each myFile In fldr.Files
If FSO.GetExtensionName(myFile) = "log" Then
Set objFile = FSO.OpenTextFile(myFile, ForReading)
L = 0
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
L = L + 1
If L < 21 Then
arrFields = Split(strLine, vbTab)
If UBound(arrFields) = 5 Then
Sheets(1).Cells(R, 1).Value = arrFields(3)
Sheets(1).Cells(R, 2).Value = arrFields(4)
R = R + 1
End If
End If
Loop
objFile.Close
End If
Next myFile

Set fldr = Nothing
Set FSO = Nothing
End Sub


_____________________________________

Steve Yandl



wrote in message
...
Steve,

The format of the log files doesnt vary. The values are tab
separated.

Please let me know the way to retrieve the values. If I change the
extension to *.xls I can accomplish way I am trying to do.

Kevin

On Apr 14, 5:39 pm, "Steve Yandl" wrote:
Kevin,

Do you know the format of the log files or does it vary? Specifically,
are
the values comma separated, tab separated, space separated or something
else? If you want the 4th and 5th values (columns D and E) from the
first
20 lines of some text files, there are ways to retrieve them, especially
if
you know how the values are separated.

Steve

wrote in message

...

Steve,

Given below is the macro I have. It looks for *.xls files in a folder
(Path) and then retrieves D1 to D20 values, places them in column A.
Likewise, E1 to E20 values are placed in column B.

I need to fetch those values from *.log files. I am not sure if the
macro would work, unless and until I change the *.log to *.xls

Kevin

Path = Dir(MyPath & "*.xls")
Set sh = Sheets("Sheet1")

Fnum = 0
Do While Path < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = Path
Path = Dir()
Loop

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

rnum = LastRow(sh)

Set destrange = sh.Cells(rnum + 1, "A")

GetData MyPath & MyFiles(Fnum), "Sheet1", "D1:D20",
destrange, False, False

Set destrange = sh.Cells(rnum + 1, "B")
GetData MyPath & MyFiles(Fnum), "Sheet1", "E1:E20",
destrange, False, False
Next
End If

On Apr 14, 4:51 pm, "Steve Yandl" wrote:
Kevin,

You should probably share your macro as it is now and explain what you
want
to accomplish. If you have file names listed in a workbook or a text
file
and they include the full path, it is certainly possible to alter the
extensions of the referenced files but it is also possible to get
Excel
to
regard them as the text files they are and act accordingly. Depending
on
your goal and the scenario, the second option is probably far safer.

Steve Yandl

wrote in message

...

Macropod,

My macro uses

Set sh = Sheets("Sheet1")

So, its not working with *.log file. Now, I have to change the *.log
file to *.xls file.. I have no other option, I guess!

Kevin

On Apr 14, 4:38 pm, wrote:
Hi Macropod,

When I manually rename that *.log file to *.xls and open that excel
file, I find the excel file in good shape. I understand that this
is
not possible in all cases but in this particular case, it works.

The *.log file has data in 4 column, which when opened in a excel
file, is arranged in A, B, C and D columns respectively. The macro
I
have references the column A and B of the excel file. Since, I have
a
*.log file, I am not sure how my macro would associate the columns
in
the *.log file as A and B. I guess this is possible only if the log
file is changed into an excel file.

This being the case, per your reply, my macro should be able to
read
the values from *.log file? I am going to give this a shot.

Cheers
Kevin

On Apr 14, 4:26 pm, "macropod" wrote:

Hi Kevin,

The file extension needn't affect your macro's ability to read
the
*.txt and *.log files' contents. In any event, changing the
extension doesn't turn the files into Excel files and could cause
other
problems. Plus, you'll still need to understand those files'
structure and code accordingly.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

wrote in
...
Hello group,

I have a macro to pull the values in row A and B of a workbook,
to
another workbook. I have some values in the files that have
*.txt
and
*.log extension.

Is there any macro to change the extension of the file (*.txt
and
*.log) to *.xls? Please let know.

Thanks,
Kevin