Database Search

TechHome

• Alternate Data Stream UDFs
• CD Drive Locker
• Checksum Calculations
• Count Number of Lines in File UDF
• Delete if Older Than
• Delete old TMP files
• Disable Offline Attribute
• Display a Files Property Sheet
• Export to Excel or CSV UDFs
• Extracting last lines of a file UDS
• Fast File Count
• File Read Line
• FileCount UDF
• Find a File
• Find Line Number of Keyword in a Text File
• Get a Signature of a File
• Get Summary File Properties
• Get the Compressed File Size
• Getting True Filenames and Directories UDF
• List all Files Older Than
• Make Available Offline
• PathStripToRoot UDF
• Recursive FileAttrSet
• Recursive List of Files or Directories
• UDF - Find Newest File
• udfArrFileOlderThan
• udfAskDirectory
• udfFileCopyFlat
• XCOPY with some Excludes

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Export to Excel or CSV UDFs

 Keywords: Export to Excel or CSV UDF 

;*******************************

; Export to Excel or CSV UDF's

;

; By Iain Dickason 2003

;*******************************



#DefineFunction ExportToExcel(Array,File_Name)

   xlNormal = -4143

   objXL = ObjectOpen("Excel.Application")

   objXL.Visible = @FALSE

   objXL.DisplayAlerts = @FALSE

   Workbooks = objXL.WorkBooks

   Workbook = Workbooks.Add

   xCount = ArrInfo (Array, 1)

   yCount = ArrInfo (Array, 2)

   For x = 0 To xCount - 1

      For y = 0 To yCount - 1

         Cell=objXL.Cells(y + 1, x + 1)

         Cell.Value=Array[x,y,0]

         CellFont = Cell.Font

         CellFont.FontStyle = Array[x,y,1]

         CellFont.size = Array[x,y,2]

         CellFont.ColorIndex = Array[x,y,3]

         ObjectClose(Cell) 

         ObjectClose(CellFont) 


      Next y

   Next x

   Workbook.SaveAs(File_Name)

   ObjectClose(WorkBook)

   Workbooks.Close()

   ObjectClose(WorkBooks)

   objXL.Quit()

   ObjectClose(objXL)

   Return

#EndFunction



#DefineFunction ExportToCSV(Array,File_Name)

   mode = ErrorMode(@OFF)

   LastError()

   report = FileOpen(File_Name,"WRITE")

   ErrorMode(mode)

   If LastError() Then

      Message("Error!","Unable to open file, please check that it is not open and re-run export.")

      Return

   EndIf

   xCount = ArrInfo (Array, 1)

   yCount = ArrInfo (Array, 2)

   For y = 0 To yCount - 1

      Line = ""

      For x = 0 To xCount - 1

      Line = ItemInsert(Array[x,y,0], -1, Line,",")

      Next x

      FileWrite(report,Line)

   Next y

   FileClose(report)

   Return

#EndFunction



filetypes = "Excel Workbook|*.xls|CSV files|*.csv|All Files|*.*|"

FileName = "export.xls"

file = AskFilename("Export Filename", "", filetypes, FileName, 0)



SoftwareArray = ArrDimension(6,10,4)

; Array[x,y,0] = Data to export

; Array[x,y,1] = Font Style (BOLD etc)

; Array[x,y,2] = Font Size (needs to be set to a number, Can't be "")

; Array[x,y,3] = Font Colour (needs to be set to a number, Can't be "")



ArrInitialize(SoftwareArray, "")

For x = 0 To 5

   For y = 0 To 9

      If Random(5) == 1 Then SoftwareArray[x,y,1] = "BOLD"

      SoftwareArray[x,y,2] = Random(10)+5

      SoftwareArray[x,y,3] = Random(10)

      SoftwareArray[x,y,0] = Random(100)

   Next y

Next x 



If StrLower(FileExtension(file)) == "xls" Then

   ExportToExcel(SoftwareArray,file)

EndIf

If StrLower(FileExtension(file)) == "csv" Then

   ExportToCSV(SoftwareArray,file)

EndIf





Exit

Article ID:   W15731

File Created: 2003:11:19:10:15:48

Last Updated: 2003:11:19:10:15:48