text file

[oleg_v]

hi
i need away that will give me the way to pull data from specified place
in txt file to A1 cell in excel sheet3


thanks

Oleg

[Tinbendr]

Probably. We need lots more info about the text file.

Can you provide a sample?

[oleg_v]

hi

here:


--------------------------------------------------------------------------------
15-דצמבר-2009 13:44 8H-A51190-10 Page 2
--------------------------------------------------------------------------------
(mm) ACTUAL NOMINAL LO-TOL HI-TOL DEVIATION GRAPHIC ERROR
--------------------------------------------------------------------------------
Length-X 129.972 130.000 -0.500 +0.500 -0.028 ---*---
--------------------------------------------------------------------------------
DIM-1
Point:PNT005
Y-axis -30.155 -30.000 -0.200 +0.200 -0.155 -*-+---
--------------------------------------------------------------------------------
DIM-2
Point:PNT006
X-axis 30.106 29.750 -0.200 +0.200 0.356 ---+--> 0.156
--------------------------------------------------------------------------------
DIM-3
Point:PNT006--Point:PNT007
Length-X 60.275 59.500 -0.300 +0.300 0.775 ---+--> 0.475
--------------------------------------------------------------------------------
DIM-4
Plane:PLANE001
X-axis -39.936 -40.000 -0.100 +0.100 0.064 ---+-*-
--------------------------------------------------------------------------------
DIM-5
Plane:PLANE001--Plane:PLANE002
Length-X 80.053 80.000 -0.100 +0.100 0.053 ---+-*-
-------------------------------------------------------------------------

i need to copy the number in the "actual" column and paste it each number in the different cell in excel sheet


Tnx

[mikerickson]

Did my responce at
http://www.mrexcel.com/forum/showthread.php?t=437767
work for you?

[hardlife]

hi

or You can try this



Sub import_txt_file()


' Get the file pathname
Set oXL = CreateObject("Excel.Application")
'f_name = oXL.GetOpenFilename("All Files (*.*),*.*", , "Select file", False)
f_name = oXL.GetOpenFilename("Text Files (*.txt),*.txt", , "Select file", False)
If f_name = "False" Then
'user cancelled out of dialog box
Set oXL = Nothing
Exit Sub
End If
'store file pathname in the file_pathname variable
'MsgBox f_name
'ret = ActiveDocument.GetVariable("file_pathname").SetContent(f_name, False)
Set oXL = Nothing

Workbooks.OpenText Filename:= _
f_name, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1))
Cells.Select
Cells.EntireColumn.AutoFit
Range("B28").Select
End Sub



HTH :hi:

[lucas]

oleg_v, it is rude to post in other forums without providing a link.

Why would you let people who are offering to help you for free work on something that might already be resolved?

Please read this before posting here again (http://www.excelguru.ca/node/7).

[oleg_v]

Sorry

i will correct my self from know on

[GTO]

Oleg,

Might you please place a link in the other thread referencing this one?

Thank you,

Mark

[oleg_v]

here you go

http://www.mrexcel.com/forum/showthread.php?p=2163385#post2163385

[oleg_v]

Hi
i do not understated how to use this macro
it copies me the hole file

and i need each time different number

[GTO]

Hi Oleg,

As you mentioned not being sure how to implement mikerickson's or hardlife's code, I would suggest attaching an example text file and a copy of the workbook you have thus far.

Of course change any sensitive/private data in either, but ensure layout/parameters are same.

You can zip both into one zip file, and attach by first pressing the Go Advanced button underneath the Quick Reply box, then scroll down below the Reply box and press the Manage Attachments button.

Mark

[oleg_v]

Hi

here is the data
i will repeat what i am tying to accomplish:

1) open the txt file
2) find "dim-1"
3) move 2 lines down and copy the number in the "actual" column
4) paste this number in the excel file sheet1 cell A1 (nut the hole file only the number)

I am very sorry for a lot of changes in my request this because i am building the procces.

i am sorry

Many thanks

Oleg

[hardlife]

Hi Oleg, Your file extension for txt file was *.RES (tst.txt.RES)

me changed it to TXT file extension.

Macro is now in Module1 it is typical place for VBA code

see attachment, click on button find import file = tst.txt

have a nice day:hi:

[oleg_v]

hi
thanks

can you please refer to the changes that i asked i my previos post

[oleg_v]

hi

this attaches the hole data that in the file
can i attache only one number like i said in my post?

Many thanks

[hardlife]

hi Oleg,

do You can create example in excel for this TXT file
with "desired result" manually, for me to understand?

me is wishing You to have a nice day :hi:

[oleg_v]

Hi
i wrote in the excel file what you asked
If not to hard i want the macro to put the data in the same file and not
create a new one
thanks.

Did you have a Phone number for me to explain to you
exactly what i need
because i think i am not explaining my self very well.


Oleg

[hardlife]

Hi Oleg You can try this :hi:

[SWE321]

For getting text form textfiles I always use.

NameOfFile = Range("c6").Value
PlaceOfFile = Range("c5").Value
Filelocation = PlaceOfFile + "\" + NameOfFile
sText = ReadTextFileFetchDataMain(Filelocation)
Range("c10").Value = sText

[oleg_v]

Hi Oleg You can try this :hi:

Hi

it works but
why always a new file created??

[hardlife]

Hi Oleg

Good Night for now,

HTH :hi:

[oleg_v]

Thank you

if it not to hard can you explain to how is your macro working line by line

this is great

[oleg_v]

One more thing
if the data in text file is"0.000" the macro does not write

[oleg_v]

hi

i started to work with the macro and found some erros .
Say can i exlpain to you my problem verbally by the PHONE

[oleg_v]

Hi Oleg

Good Night for now,

HTH :hi:

Hi

I have a few problems with the macro
some numbers is in correct it seems that macro takes "----------"
and translate it to number because of that when i run the macro there are 2 similar numbers if i am not mistaken 0.5777
it just could not be

Thanks

[GTO]

Greetings,

In your example textfile, the data seems pretty consistent, excepting the additional rows / headings between 'pages'. If that is the case for your actual files, then I think this may work.


Option Explicit

Enum eFSO
ForReading = 1
End Enum

Sub exa()
Dim FSO As Object '<--- FileSystemObject
Dim fsoText As Object '<--- TextStream
Dim REX As Object '<--- RegExp
Dim rexMatches As Object '<--- MatchCollection
Dim strFullName As String
Dim strLine As String
Dim aryHorz As Variant
Dim aryTransposed As Variant
Dim i As Long
Dim bolExitNow As Boolean

'// Optional and change to suite; pick folder for dialog to start in. //
ChDir ThisWorkbook.Path

strFullName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt", _
Title:="Import From:", MultiSelect:=False)
'// If user did not pick a textfile, quit now. //
If strFullName = "False" Then Exit Sub
'// Set references to FSO, the textstream we open, and RegExp. //
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fsoText = FSO.OpenTextFile(Filename:=strFullName, IOMode:=ForReading)
Set REX = CreateObject("VBScript.RegExp")
'// Initially size a dynamic array to two "rows" high, by one column. This way we //
'// can increase the size of the array as we gather records. We'll transpose it //
'// later. //
ReDim aryHorz(1 To 2, 0 To 0)

With REX
.Global = False
.IgnoreCase = False

'// Loop as long as we're not at the end of the textfile we're reading. //
Do While Not fsoText.AtEndOfStream

strLine = fsoText.ReadLine
'// Pattern to find "DIM-*" //
.Pattern = "^\bDIM-\d+\b"

'// If DIM found... //
If .Test(strLine) Then
'// In first loop, we reset base and array still is one 'column'. //
'// Loops therafter increase 'columns' to hold more records. //
ReDim Preserve aryHorz(1 To 2, 1 To UBound(aryHorz, 2) + 1)
'// ...set a reference to the returned match collection, which will //
'// contain only one match, and plunk that match into array. //
Set rexMatches = .Execute(strLine)
aryHorz(1, UBound(aryHorz, 2)) = CStr(rexMatches(0).Value)

'// Set/reset flag //
bolExitNow = False
'// Nest our loop, so that we can look for either a number (should exist//
'// according to example data), or the next DIM in case no number. //
Do While Not fsoText.AtEndOfStream _
And Not bolExitNow

strLine = fsoText.ReadLine
'// to find either (one or the other, not both) DIM-* or the first //
'// number in a line meeting conditions: i) must have a space in //
'// front, ii) may have a negative sign/hyphen, iii) word boundary, //
'// iv) 1+ digits, v) a decimal point, vi) 1+ digits, vii) trailing //
'// word boundary. //
.Pattern = "^\bDIM-\d+\b|\ \-?\b\d+\.\d+\b"
If .Test(strLine) Then
Set rexMatches = .Execute(strLine)
'// If after a few lines we run into another DIM-*, then we didn't//
'// find a number to go with the last one - so, we'll put an //
'// empty string in for the last number, increase the array //
'// size, and put this DIM-* in the next record. //
If rexMatches(0).Value Like "DIM-*" Then
aryHorz(2, UBound(aryHorz, 2)) = vbNullString
ReDim Preserve aryHorz(1 To 2, 1 To UBound(aryHorz, 2) + 1)
aryHorz(1, UBound(aryHorz, 2)) = CStr(rexMatches(0).Value)
'GoTo NoNumber
Else
'// Else we found a number, so plunk it in and get out of //
'// the inner loop. //
aryHorz(2, UBound(aryHorz, 2)) = _
CDbl(Replace(rexMatches(0).Value, Chr(32), vbNullString))
bolExitNow = True
End If
End If
NoNumber: Loop
End If
Loop
fsoText.Close

'// Size our transposed array based on size of aryHorz //
ReDim aryTransposed(1 To UBound(aryHorz, 2), 1 To 2)

'// Fill second array from first. //
For i = 1 To UBound(aryHorz, 2)
aryTransposed(i, 1) = aryHorz(1, i)
aryTransposed(i, 2) = aryHorz(2, i)
Next
End With

'// To go with your example wb. //
Range("A6:B" & Rows.Count).Clear
With Range("A6").Resize(UBound(aryTransposed), 2)
.Columns(1).NumberFormat = "@"
.Columns(2).NumberFormat = "0.000"
.Value = aryTransposed
End With
End Sub

Hope that helps,

Mark

[hardlife]

Hi Oleg,

please try it now in my country is decimal separator ","

in Your country is probably decimal separator "."

this is probably now fixed.

Good Night for now, have a nice day, Pavel:hi: