Open text file and write to excel file

[Saladsamurai]

Hey folks

I thought this should be an easy one, but my searches are turning up very lengthy codes for what seems like what should be a simple task. Using Excel 2003 btw.

I have a .lvm file which is an over glorified .txt file that Excel is capable of opening. I would simply like to have excel take what is in the .lvm file and write it to an excel file where I can process the data.

Any thoughts? Thanks.

[p45cal]

perhaps a small sample of sucha .lvm file?

[Saladsamurai]

Hey p45cal! I had been trying to upload one, but it is not an accepted file type. I will just copy and paste all of the data to a .txt file since my computer won't let me "save as" .txt for some reason.

Ok, guess what? VBAX won't allow me to upload ".txt" either. Any suggestions?

[Tinbendr]

VBAX won't allow me to upload ".txt" either. Any suggestions?

Zip it.

[Saladsamurai]

Zip it.

Nice! Uploaded original .lvm file.

[vzachin]

i usually use something like this

[vba]Sub text1()
Dim i As Long
Open "C:\TestFile1.lvm" For Input As #1

With ActiveSheet
i = 1
Do While Not EOF(1)
Line Input #1, dat
.Cells(i, 1) = dat
i = i + 1
Loop
End With
Close #1
End Sub[/vba]

zach

[p45cal]

or perhaps this might do it for you?:
[vba]Sub blah()
fname = Application.GetOpenFilename("*.lvm,*.lvm", , "Choose an lvm file")
If fname <> False Then
Workbooks.OpenText Filename:=fname, Origin:=xlMSDOS, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
Space:=False, 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), _
Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True
Set lvmWb = ActiveWorkbook
Columns("A:K").EntireColumn.AutoFit
newfname = Left(fname, Len(fname) - 4)
lvmWb.SaveAs Filename:=newfname, FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End If
End Sub
[/vba]

[Saladsamurai]

Hey, thanks guys! Hey p45cal, can you maybe explain what all of the "array()" references are? Just curious!

[p45cal]

Hey, thanks guys! Hey p45cal, can you maybe explain what all of the "array()" references are? Just curious!

Hey, Saladsamurai, Excel Help says it better than I:
FieldInfo Optional Variant:
An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

where xlcolumndatatype:
Name Value Description
xlDMYFormat 4 DMY date format.
xlDYMFormat 7 DYM date format.
xlEMDFormat 10 EMD date format.
xlGeneralFormat 1 General.
xlMDYFormat 3 MDY date format.
xlMYDFormat 6 MYD date format.
xlSkipColumn 9 Column is not parsed.
xlTextFormat 2 Text.
xlYDMFormat 8 YDM date format.
xlYMDFormat 5 YMD date format.

Since it's optional you may get away without it altogether.

[Saladsamurai]

Thanks again p45cal! I forgot to install my VBA help after the new hard drive install! Must find disc!