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
|