Page 1 of 1 1
Topic Options
#88674 - 2002-10-15 01:36 AM Excel Importation Example
bleonard Offline
Seasoned Scripter
*****

Registered: 2001-01-19
Posts: 581
Loc: Chicago, IL
The following just proves why this board rocks. No, it's not my work, but what I learned.
I know zip about programming or COM objects, and from the manual and postings on this
board was able to build the following in short time.

For programmers, the following is a no-brainer.
For non-programmer types, this ties in a number of KiX posts into an example usable script
to import delimited data into an Excel workbook.

Script will find and open delimited text files stored in multiple locations, and read
the delimited data by row into columns on individual worksheets within the workbook.
Individual worksheets are created as necessary to handle locations exceeding the MS Excel
default (3 worksheets?)

This example script depends upon pipe-delimited files, with two rows of information in
each file. First row is the unload header, the second row is the usable data.

I only take credit for pulling together others work here - Radimus, Shawn, Kent - and others
who deserve mention but whose names escape me.

If this helps anyone, great.

Bill

PS: Extraneous ';' characters simply for posting display to ease vewing.

code:
  ; **  Define some variables for this script
$ColumnRange = ("A1:E1") ; ** Column ranges for all worksheets (match to # values in $Header)
$Delim = "|" ; ** Text file delimiter to use (pipe in this example)
$Header = "Column1","Column2","Column3","Column4","Column5" ; ** Worksheet column headers, loaded into an array
$IdxArray = 0 ; ** Index for location-specific arrays
$IdxArrayChk = 0 ; ** Check index for location-specific arrays to end loop process
$oXL = "" ; ** Variable for Excel COM objects
$SaveAs = "%temp%\Test.xls" ; ** Location and name for the newly created worksheet
$SrcFil = "*.txt" ; ** Define wildcard name for files to read for insertion
DIM $SrcArray[4] ; ** Array to hold values for locations of files to be inserted
DIM $WksArray[4] ; ** Array to hold values for names of Worksheets for each location
;
;
; ** Load arrays for locations of files, and worksheet names
$SrcArray[$IdxArray] = "\\Server1\Share1\Folder1"
$WksArray[$IdxArray] = "Location1"
$IdxArray = $IdxArray+1
$SrcArray[$IdxArray] = "\\Server2\Share2\Folder2"
$WksArray[$IdxArray] = "Location2"
$IdxArray = $IdxArray+1
$SrcArray[$IdxArray] = "\\Server3\Share3\Folder3"
$WksArray[$IdxArray] = "Location3"
$IdxArray = $IdxArray+1
$SrcArray[$IdxArray] = "\\Server4\Share4\Folder4"
$WksArray[$IdxArray] = "Location4"
$IdxArray = $IdxArray+1
;
; ** Create new Excel workbook
$oXL = CreateObject("Excel.application")
$RC = $oXL.Workbooks.Add ; ** Add a new workbook
;
; ** Perform loop for each defined location
DO
; ** Define active worksheet. If Workbook does not have enough worksheets, add as necessary
$CurSheet = "sheet" + ($IdxArrayChk + 1)
$RC = $oXL.Worksheets("$CurSheet").Activate
IF (@ERROR = "-2147352567") OR INSTR (@SERROR, 'COM exception error "Worksheets"') <> 0
$RC = $oXL.Worksheets.Add
$RC = $oXL.Worksheets("$CurSheet").Activate
ENDIF
$oXL.ActiveSheet.Name = $WksArray[$IdxArrayChk]
$oXL.Range("$ColumnRange").Value = $Header ; ** Load worksheet column headers from array
;
; ** Perform loop for each delimited file found within each location
$SrcDrv = $SrcArray[$IdxArrayChk]
$SrcFil = DIR ("$SrcDrv\$SrcFil") ; ** Obtain first file name to read
$IdxRow = 2 ; ** Define what ROW to start writing data into (1 below Header)
WHILE ($SrcFil <> "") AND (@ERROR = 0)
$SrcFil = $SrcDrv + "\" + $SrcFil
$con = OPEN (1, $SrcFil) ; ** Open file to read
IF (@ERROR <> 0) ; ** Confirm file was opened for reading
GOTO "NextFile"
ENDIF
$FileEnd = N ; ** Reset DO/UNTIL process termination variable
$IdxCol = 1 ; ** Define what COLUMN to start writing data into
$Line = READLINE (1) ; ** Read HEADER line, do not import into Excel
$Line = READLINE (1) ; ** Read DATA line, parse its values for Excel importation
;
; ** Perform loop for each field of data in delimited file
DO
IF LEN ($Line > 0) AND INSTR ($Line, $Delim) = 0 ; ** Last value in parsed string if no delimiter at end of unload line
$Data = $Line
$FileEnd = Y
ELSE
$Parse = INSTR ($Line, $Delim)
$Data = SUBSTR ($Line, 1, ($Parse-1))
$Line = SUBSTR ($Line, ($Parse+1), LEN ($Line))
ENDIF
$oXL.Cells ($IdxRow, $IdxCol).Value = $Data ; ** Load parsed data into the spreadsheet column
$IdxCol = $IdxCol+1
UNTIL ($FileEnd = Y)
;
$con = CLOSE (1)
$IdxRow = $IdxRow+1
:NextFile ; ** Do not increment row counter if file can not be opened for reading
$SrcFil = DIR () ; ** Obtain next file name to read
LOOP
;
$oXL.Range("$ColumnRange").Font.Bold = 1 ; ** Bold font for worksheet HEADER row
$RC = $oXL.Range("$ColumnRange").EntireColumn.AutoFit ; ** Autosize the width of the worksheet columns
$IdxArrayChk = $IdxArrayChk+1 ; ** Increment array counter, move on to next office
UNTIL ($IdxArrayChk >= $IdxArray)
;
; ** Save and close Excel file
$CurSheet = $WksArray[1]
$RC = $oXL.Worksheets("$CurSheet").Activate ; ** Set first worksheet as active one when opening new file
$oXL.UserControl = 1 ; ** Allow users full to access this new workbook
$oXL = $oXL.ActiveWorkbook.SaveAs("$SaveAs",-4143,"","",0,0,,,0)
$oXL = $oXL.close
;
EXIT


Top
#88675 - 2002-10-15 06:37 AM Re: Excel Importation Example
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11616
Loc: CA
Awesome Bill,

I have been postponing doing something like this from within Excel. Will have to give this a go and see what all it does. Nice to see new stuff like this put together and posted for others to use or learn from as well.

Thanks again for the post and not keeping it to yourself.

Top
#88676 - 2002-10-15 04:26 PM Re: Excel Importation Example
bleonard Offline
Seasoned Scripter
*****

Registered: 2001-01-19
Posts: 581
Loc: Chicago, IL
NTDOC - thanks for the comments.

Bill

(Editied this post - tested and confirmed posted code will properly set $FileEnd if final character is/is not the delimiter).

[ 15. October 2002, 20:11: Message edited by: bleonard ]

Top
#88677 - 2002-10-15 07:20 PM Re: Excel Importation Example
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11163
Loc: Boston, MA, USA
There might also be a way using a database driver for Excel. However, I've never used those.
_________________________
There are two types of vessels, submarines and targets.

Top
Page 1 of 1 1


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Allen) and 294 anonymous users online.
Newest Members
MacduMela, karimKix, NicoK, Kelly001, MichaelK72
17776 Registered Users

Generated in 0.051 seconds in which 0.021 seconds were spent on a total of 12 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org