Open fixed length text file into a template? 
Author Message
 Open fixed length text file into a template?

I want to open a fixed length field text file with Excel and have it put
into columns with the headers already there.  Assuming I create the
template with the headers and store it, would there be any way to do
this?  If I just type Excel filename.txt, it opens the file with
everything in one column.
--
Jim Ryan
Please CC: by mail


Sat, 31 Aug 2002 03:00:00 GMT  
 Open fixed length text file into a template?
Try:

OpenText Method
http://msdn.microsoft.com/library/officedev/off2000/xlmthopentext.htm

though I didn't see anything about handling the headers...

--
Michael Harris
MVP Scripting

I want to open a fixed length field text file with Excel and have it put
into columns with the headers already there.  Assuming I create the
template with the headers and store it, would there be any way to do
this?  If I just type Excel filename.txt, it opens the file with
everything in one column.
--
Jim Ryan
Please CC: by mail



Sat, 31 Aug 2002 03:00:00 GMT  
 Open fixed length text file into a template?

says...
Quote:
> Try:

> OpenText Method
> http://msdn.microsoft.com/library/officedev/off2000/xlmthopentext.htm

> though I didn't see anything about handling the headers...

> --
> Michael Harris
> MVP Scripting


> I want to open a fixed length field text file with Excel and have it put
> into columns with the headers already there.  Assuming I create the
> template with the headers and store it, would there be any way to do
> this?  If I just type Excel filename.txt, it opens the file with
> everything in one column.

That would be what I want, at least partially.  But now how do I launch
Excel and run the macro automatically?  Not every time I run Excel, mind
you, just when I want to.  Is there a startup switch or something to call
a particular macro?
--
Jim Ryan
Please CC: by mail


Sat, 31 Aug 2002 03:00:00 GMT  
 Open fixed length text file into a template?
You didn't mention any macro...

Is this something like what you're after?

<job>
<!--
Opens a new Excel Worksheet with 3 fixed width
left-aligned columns and the first row filled
with bold headers...
-->
<reference id="xlref"    object="Excel.Sheet" />
<object    id="xl"       progid="Excel.Application" />

<script language="VBScript">

xl.Visible = TRUE

xl.WorkBooks.Add

xl.Columns(1).ColumnWidth = 32
xl.Columns(2).ColumnWidth = 32
xl.Columns(3).ColumnWidth = 32

xl.Cells(1, 1).Value = "header 1"
xl.Cells(1, 2).Value = "header 2"
xl.Cells(1, 3).Value = "header 3"

xl.Range("A1:C1").Select
xl.Selection.Font.Bold = True

xl.Columns("A:A").Select
xl.Selection.HorizontalAlignment = xlLeft
xl.Columns("B:B").Select
xl.Selection.HorizontalAlignment = xlLeft
xl.Columns("C:C").Select
xl.Selection.HorizontalAlignment = xlLeft
xl.Cells(2,1).Activate
</script>

</job>

--
Michael Harris
MVP Scripting


says...

Quote:
> Try:

> OpenText Method
> http://msdn.microsoft.com/library/officedev/off2000/xlmthopentext.htm

> though I didn't see anything about handling the headers...

> --
> Michael Harris
> MVP Scripting


> I want to open a fixed length field text file with Excel and have it put
> into columns with the headers already there.  Assuming I create the
> template with the headers and store it, would there be any way to do
> this?  If I just type Excel filename.txt, it opens the file with
> everything in one column.

That would be what I want, at least partially.  But now how do I launch
Excel and run the macro automatically?  Not every time I run Excel, mind
you, just when I want to.  Is there a startup switch or something to call
a particular macro?
--
Jim Ryan
Please CC: by mail


Sat, 31 Aug 2002 03:00:00 GMT  
 Open fixed length text file into a template?
Jim,

One way to do this is to open the text file and parse it.  This will
create a spreadsheet *without* the headers.  Simply select the entire
spreadsheet, copy to the clipboard and paste it into your template,
right beneath the headers.

To open a text file: File | Open and change the Files of Type to Text
File.  This will give you a wizard that will walk you through opening
the text file.

If you open the same text file repeatedly, simply record a macro that
will mimic the wizard, then insert a row in row 1 and type in your
header. Stop recording.  This simple macro will open the same text file
every time.  When you receive a new text file in the same format, you
need to save it in the same name as the old text file.  (There are ways
around that, but this is more advanced VBA coding)

        Ofer

Quote:

> I want to open a fixed length field text file with Excel and have it put
> into columns with the headers already there.  Assuming I create the
> template with the headers and store it, would there be any way to do
> this?  If I just type Excel filename.txt, it opens the file with
> everything in one column.
> --
> Jim Ryan
> Please CC: by mail



Sat, 31 Aug 2002 03:00:00 GMT  
 Open fixed length text file into a template?
Jim,

One way to do this is to open the text file and parse it.  This will
create a spreadsheet *without* the headers.  Simply select the entire
spreadsheet, copy to the clipboard and paste it into your template,
right beneath the headers.

To open a text file: File | Open and change the Files of Type to Text
File.  This will give you a wizard that will walk you through opening
the text file.

If you open the same text file repeatedly, simply record a macro that
will mimic the wizard, then insert a row in row 1 and type in your
header. Stop recording.  This simple macro will open the same text file
every time.  When you receive a new text file in the same format, you
need to save it in the same name as the old text file.  (There are ways
around that, but this is more advanced VBA coding)

        Ofer

Quote:

> I want to open a fixed length field text file with Excel and have it put
> into columns with the headers already there.  Assuming I create the
> template with the headers and store it, would there be any way to do
> this?  If I just type Excel filename.txt, it opens the file with
> everything in one column.
> --
> Jim Ryan
> Please CC: by mail



Sat, 31 Aug 2002 03:00:00 GMT  
 Open fixed length text file into a template?

says...

Quote:
> You didn't mention any macro...

> Is this something like what you're after?

> <job>
> <!--
> Opens a new Excel Worksheet with 3 fixed width
> left-aligned columns and the first row filled
> with bold headers...
> -->
> <reference id="xlref"    object="Excel.Sheet" />
> <object    id="xl"       progid="Excel.Application" />

> <script language="VBScript">

> xl.Visible = TRUE

> xl.WorkBooks.Add

> xl.Columns(1).ColumnWidth = 32
> xl.Columns(2).ColumnWidth = 32
> xl.Columns(3).ColumnWidth = 32

> xl.Cells(1, 1).Value = "header 1"
> xl.Cells(1, 2).Value = "header 2"
> xl.Cells(1, 3).Value = "header 3"

> xl.Range("A1:C1").Select
> xl.Selection.Font.Bold = True

> xl.Columns("A:A").Select
> xl.Selection.HorizontalAlignment = xlLeft
> xl.Columns("B:B").Select
> xl.Selection.HorizontalAlignment = xlLeft
> xl.Columns("C:C").Select
> xl.Selection.HorizontalAlignment = xlLeft
> xl.Cells(2,1).Activate
> </script>

> </job>

> --
> Michael Harris
> MVP Scripting



> says...

Sorry.  I think this is kind of it.  Let me explain the whole thing just
to get us on the same page.  I have a .bat file:

___________________________________________________________
start /w wscript d:\downloads\scripts\ftp\GetCod.vbs

:loop
if exist c:\temp\finished.txt goto continue
goto loop

:continue
del c:\temp\finished.txt
d:
cd \downloads\scripts\ftp\

copy *.cod totalcod.txt

del *.cod

"c:\program files\microsoft office\office\excel.exe" totalcod.txt
______________________________________________________________

That launches a .vbs file:

______________________________________________________________
Dim Bridge
Bridge = InputBox("Bridge Name?")
repdate = InputBox("Date of report (Mmmdd) Use * for all reports?")
'Username = InputBox("Username?")
'Password = inputBox("Password?")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set CF = FSO.CreateTextFile("D:\downloads\scripts\ftp\CFG.TXT")
CF.Writeline "open" & " " & Bridge
'CF.Writeline Username
'CF.Writeline Password
CF.Writeline "username"
CF.Writeline "password"
CF.Writeline "lcd D:\downloads\scripts\ftp\"
CF.Writeline "cd /usr/dcb/codr/"
CF.Writeline "binary"
CF.Writeline "mget" & " " & repdate &".cod"
CF.Writeline "Bye"
CF.Close
Set CF = Nothing
'Set FSO = Nothing
'Yn = MsgBox("Execute?",vbyesno, "FTP")
'If Yn = 6 then
Set WS = CreateObject("WScript.Shell")
WS.Run "Ftp -i -s:D:\downloads\scripts\ftp\Cfg.txt", 0 ,1
Set WS = Nothing

Dim TotalFile
Set TotalFile = FSO.CreateTextFile("c:\temp\finished.txt", True)
TotalFile.WriteLine("All Done.")
TotalFile.Close

'TotalFile.CopyFile "D:\downloads\scripts\ftp\Feb*.cod",
"c:\temp\totalfile.txt"

'set excel = CreateObject("wscript.shell")
'excel.Run ("excel.exe ")
____________________________________________________________

...Excuse all the mess in there, I'm still working on it.  But
essentially the script goes out and FTP's a bunch of files from a network
device into a directory on my hard drive.  The batch file takes over and
concatinates all the files into one file.  At this point I can open Excel
and do a file/open of the one large file.  I simply take the defaults on
the text import (3 <CR>'s) and the files opens up into nice columns.

This works to this point.  What I would like to have happen is to have
Excel launch automatically, import the file, and put the appropriate
headers on the columns. The files will always be the same structure.  It
has no headers in the first row.

It's this last part I'm stumbling on.  I can make the getting and
concatination of the files work, albeit not in a pretty way, but it
works.  any help with the last part would be great.  Would I be better
off with Access?

Thanks for all the input.  I really do appreciate it.
--
Jim Ryan
Please CC: by mail



Sat, 31 Aug 2002 03:00:00 GMT  
 Open fixed length text file into a template?
Try this...

Const xlFixedWidth = 2
set xl = createobject("excel.application")
xl.workbooks.opentext "c:\xtest.txt",,,xlFixedWidth
xl.rows(1).insert
xl.cells(1,1) = "header 1"
xl.cells(1,2) = "header 2"
xl.cells(1,3) = "header 3"
xl.usercontrol = true
xl.visible = true

This is the xtest.txt file I used:

data1.1     data1.2     data1.3    
data2.1     data2.2     data2.3    
data3.1     data3.2     data3.3    

Here's another variation where the data field offsets and types are specified:

Const xlFixedWidth = 2
set xl = createobject("excel.application")
fieldinfo = array(array(0,1),array(7,1),array(14,1))
xl.workbooks.opentext "c:\xtest.txt",,,xlFixedWidth,,,,,,,,,fieldinfo
xl.rows(1).insert
xl.cells(1,1) = "header 1"
xl.cells(1,2) = "header 2"
xl.cells(1,3) = "header 3"
xl.usercontrol = true
xl.visible = true

Using this input:

data1.1data1.2data1.3    
data2.1data2.2data2.3    
data3.1data3.2data3.3    

--
Michael Harris
MVP Scripting


says...

Quote:
> You didn't mention any macro...

> Is this something like what you're after?

> <job>
> <!--
> Opens a new Excel Worksheet with 3 fixed width
> left-aligned columns and the first row filled
> with bold headers...
> -->
> <reference id="xlref"    object="Excel.Sheet" />
> <object    id="xl"       progid="Excel.Application" />

> <script language="VBScript">

> xl.Visible = TRUE

> xl.WorkBooks.Add

> xl.Columns(1).ColumnWidth = 32
> xl.Columns(2).ColumnWidth = 32
> xl.Columns(3).ColumnWidth = 32

> xl.Cells(1, 1).Value = "header 1"
> xl.Cells(1, 2).Value = "header 2"
> xl.Cells(1, 3).Value = "header 3"

> xl.Range("A1:C1").Select
> xl.Selection.Font.Bold = True

> xl.Columns("A:A").Select
> xl.Selection.HorizontalAlignment = xlLeft
> xl.Columns("B:B").Select
> xl.Selection.HorizontalAlignment = xlLeft
> xl.Columns("C:C").Select
> xl.Selection.HorizontalAlignment = xlLeft
> xl.Cells(2,1).Activate
> </script>

> </job>

> --
> Michael Harris
> MVP Scripting



> says...

Sorry.  I think this is kind of it.  Let me explain the whole thing just
to get us on the same page.  I have a .bat file:

___________________________________________________________
start /w wscript d:\downloads\scripts\ftp\GetCod.vbs

:loop
if exist c:\temp\finished.txt goto continue
goto loop

:continue
del c:\temp\finished.txt
d:
cd \downloads\scripts\ftp\

copy *.cod totalcod.txt

del *.cod

"c:\program files\microsoft office\office\excel.exe" totalcod.txt
______________________________________________________________

That launches a .vbs file:

______________________________________________________________
Dim Bridge
Bridge = InputBox("Bridge Name?")
repdate = InputBox("Date of report (Mmmdd) Use * for all reports?")
'Username = InputBox("Username?")
'Password = inputBox("Password?")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set CF = FSO.CreateTextFile("D:\downloads\scripts\ftp\CFG.TXT")
CF.Writeline "open" & " " & Bridge
'CF.Writeline Username
'CF.Writeline Password
CF.Writeline "username"
CF.Writeline "password"
CF.Writeline "lcd D:\downloads\scripts\ftp\"
CF.Writeline "cd /usr/dcb/codr/"
CF.Writeline "binary"
CF.Writeline "mget" & " " & repdate &".cod"
CF.Writeline "Bye"
CF.Close
Set CF = Nothing
'Set FSO = Nothing
'Yn = MsgBox("Execute?",vbyesno, "FTP")
'If Yn = 6 then
Set WS = CreateObject("WScript.Shell")
WS.Run "Ftp -i -s:D:\downloads\scripts\ftp\Cfg.txt", 0 ,1
Set WS = Nothing

Dim TotalFile
Set TotalFile = FSO.CreateTextFile("c:\temp\finished.txt", True)
TotalFile.WriteLine("All Done.")
TotalFile.Close

'TotalFile.CopyFile "D:\downloads\scripts\ftp\Feb*.cod",
"c:\temp\totalfile.txt"

'set excel = CreateObject("wscript.shell")
'excel.Run ("excel.exe ")
____________________________________________________________

...Excuse all the mess in there, I'm still working on it.  But
essentially the script goes out and FTP's a bunch of files from a network
device into a directory on my hard drive.  The batch file takes over and
concatinates all the files into one file.  At this point I can open Excel
and do a file/open of the one large file.  I simply take the defaults on
the text import (3 <CR>'s) and the files opens up into nice columns.

This works to this point.  What I would like to have happen is to have
Excel launch automatically, import the file, and put the appropriate
headers on the columns. The files will always be the same structure.  It
has no headers in the first row.

It's this last part I'm stumbling on.  I can make the getting and
concatination of the files work, albeit not in a pretty way, but it
works.  any help with the last part would be great.  Would I be better
off with Access?

Thanks for all the input.  I really do appreciate it.
--
Jim Ryan
Please CC: by mail



Sat, 31 Aug 2002 03:00:00 GMT  
 Open fixed length text file into a template?

says...
Quote:
> Try this...

> Const xlFixedWidth = 2
> set xl = createobject("excel.application")
> xl.workbooks.opentext "c:\xtest.txt",,,xlFixedWidth
> xl.rows(1).insert
> xl.cells(1,1) = "header 1"
> xl.cells(1,2) = "header 2"
> xl.cells(1,3) = "header 3"
> xl.usercontrol = true
> xl.visible = true

> This is the xtest.txt file I used:

> data1.1     data1.2     data1.3    
> data2.1     data2.2     data2.3    
> data3.1     data3.2     data3.3    

> Here's another variation where the data field offsets and types are specified:

> Const xlFixedWidth = 2
> set xl = createobject("excel.application")
> fieldinfo = array(array(0,1),array(7,1),array(14,1))
> xl.workbooks.opentext "c:\xtest.txt",,,xlFixedWidth,,,,,,,,,fieldinfo
> xl.rows(1).insert
> xl.cells(1,1) = "header 1"
> xl.cells(1,2) = "header 2"
> xl.cells(1,3) = "header 3"
> xl.usercontrol = true
> xl.visible = true

> Using this input:

> data1.1data1.2data1.3    
> data2.1data2.2data2.3    
> data3.1data3.2data3.3    

> --
> Michael Harris
> MVP Scripting



> says...
> > You didn't mention any macro...

> > Is this something like what you're after?

One thing I can't seem to find out is how to launch a macro when a
particular workbook/spreadsheet starts up.  I don't want a global startup
macro, but one associated with a particular file.
--
Jim Ryan
Please CC: by mail


Sun, 01 Sep 2002 03:00:00 GMT  
 Open fixed length text file into a template?
Hi Jim,

Open the workbook you'd like the autostart macro in, go into the VBE
(Alt+F11) and double-click the ThisWorkbook icon in the Project window.
Pull down the left dropdown above the main code window (should read
"(General)") and select Workbook.  This should create the following event
subroutine shell:

Private Sub Workbook_Open()

End Sub

This is a subroutine that will run everytime that particular workbook is
opened (unless macros are disabled).  So, just call the subroutine you'd
like to run from inside this routine.

Regards,
Jake Marx


Quote:
> One thing I can't seem to find out is how to launch a macro when a
> particular workbook/spreadsheet starts up.  I don't want a global startup
> macro, but one associated with a particular file.
> --
> Jim Ryan
> Please CC: by mail



Sun, 01 Sep 2002 03:00:00 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. Building a fixed length ASCII text file out of wrapping file

2. Newbie: creating a fixed-line-length file from a variable-line-length file

3. Fixed-length formatted text files...

4. Text File (fixed-length) to load Table

5. Data Access with fixed length text files

6. need help writing to fixed length text files

7. Crystal reports on fixed length text file

8. Attaching to a fixed length text file.

9. open a text file in VBScript having 500k characters in length and 5GB

10. NEWBIE - open fixed len text file ?

11. NEWBIE - open fixed len text file ?

12. Formatting fixed length text for SAP

 

 
Powered by phpBB® Forum Software