PDA

View Full Version : Open text file and write to excel file



Saladsamurai
12-22-2010, 03:32 PM
Hey folks :hi:

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
12-22-2010, 03:57 PM
perhaps a small sample of sucha .lvm file?

Saladsamurai
12-22-2010, 04:05 PM
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
12-22-2010, 04:09 PM
VBAX won't allow me to upload ".txt" either. Any suggestions?Zip it.

Saladsamurai
12-22-2010, 04:11 PM
Zip it.

Nice! Uploaded original .lvm file.

vzachin
12-22-2010, 07:09 PM
i usually use something like this

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

zach

p45cal
12-22-2010, 07:50 PM
or perhaps this might do it for you?:
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

Saladsamurai
12-23-2010, 03:54 PM
Hey, thanks guys! Hey p45cal, can you maybe explain what all of the "array()" references are? Just curious!

p45cal
12-23-2010, 04:49 PM
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 (http://www.vbaexpress.com/forum/HV10072405.htm) 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
12-30-2010, 05:06 PM
Thanks again p45cal! I forgot to install my VBA help after the new hard drive install! :SHOCKED: Must find disc!