bleonard
(Seasoned Scripter)
2002-10-15 01:36 AM
Excel Importation Example

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



NTDOCAdministrator
(KiX Master)
2002-10-15 06:37 AM
Re: Excel Importation Example

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.


bleonard
(Seasoned Scripter)
2002-10-15 04:26 PM
Re: Excel Importation Example

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 ]


Sealeopard
(KiX Master)
2002-10-15 07:20 PM
Re: Excel Importation Example

There might also be a way using a database driver for Excel. However, I've never used those.