Rob van der Woude's Scripting Pages
Powered by GeSHi

Source code for excelrd.vbs

(view source code of excelrd.vbs as plain text) 

  1. Option Explicit
  2.  
  3. Dim arrSheet, intCount
  4.  
  5. ' Read and display columns A,B, rows 2..8 of "ReadExcelTest.xlsx"
  6. arrSheet = ReadExcel( "ReadExcelTest.xlsx", "Sheet1", "A1", "B8", True )
  7. For intCount = 0 To UBound( arrSheet, 2 )
  8. 	WScript.Echo arrSheet( 0, intCount ) & vbTab & arrSheet( 1, intCount )
  9. Next
  10.  
  11. WScript.Echo "==============="
  12.  
  13. ' An alternative way to get the same results
  14. arrSheet = ReadExcel( "ReadExcelTest.xlsx", "Sheet1", "A2", "B8", False )
  15. For intCount = 0 To UBound( arrSheet, 2 )
  16. 	WScript.Echo arrSheet( 0, intCount ) & vbTab & arrSheet( 1, intCount )
  17. Next
  18.  
  19.  
  20. Function ReadExcel( myXlsFile, mySheet, my1stCell, myLastCell, blnHeader )
  21. ' Function :  ReadExcel
  22. ' Version  :  3.00
  23. ' This function reads data from an Excel sheet without using MS-Office
  24. '
  25. ' Arguments:
  26. ' myXlsFile   [string]   The path and file name of the Excel file
  27. ' mySheet     [string]   The name of the worksheet used (e.g. "Sheet1")
  28. ' my1stCell   [string]   The index of the first cell to be read (e.g. "A1")
  29. ' myLastCell  [string]   The index of the last cell to be read (e.g. "D100")
  30. ' blnHeader   [boolean]  True if the first row in the sheet is a header
  31. '
  32. ' Returns:
  33. ' The values read from the Excel sheet are returned in a two-dimensional
  34. ' array; the first dimension holds the columns, the second dimension holds
  35. ' the rows read from the Excel sheet.
  36. '
  37. ' Written by Rob van der Woude
  38. ' http://www.robvanderwoude.com
  39. 	Dim arrData( ), i, j
  40. 	Dim objExcel, objRS
  41. 	Dim strHeader, strRange
  42.  
  43. 	Const adOpenForwardOnly = 0
  44. 	Const adOpenKeyset      = 1
  45. 	Const adOpenDynamic     = 2
  46. 	Const adOpenStatic      = 3
  47.  
  48. 	' Define header parameter string for Excel object
  49. 	If blnHeader Then
  50. 		strHeader = "HDR=YES;"
  51. 	Else
  52. 		strHeader = "HDR=NO;"
  53. 	End If
  54.  
  55. 	' Open the object for the Excel file
  56. 	Set objExcel = CreateObject( "ADODB.Connection" )
  57. 	' With IMEX=1 numbers won't be ignored; tip by Thomas Willig.
  58. 	' Connection string updated by Marcel Niënkemper to open Excel 2007 (.xslx) files.
  59. 	objExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
  60. 	            & myXlsFile & ";Extended Properties=""Excel 12.0;IMEX=1;" _
  61. 	            & strHeader & """"
  62.  
  63. 	' Open a recordset object for the sheet and range
  64. 	Set objRS = CreateObject( "ADODB.Recordset" )
  65. 	strRange = mySheet & "$" & my1stCell & ":" & myLastCell
  66. 	objRS.Open "Select * from [" & strRange & "]", objExcel, adOpenStatic
  67.  
  68. 	' Read the data from the Excel sheet
  69. 	i = 0
  70. 	Do Until objRS.EOF
  71. 		' Stop reading when an empty row is encountered in the Excel sheet
  72. 		If IsNull( objRS.Fields(0).Value ) Or Trim( objRS.Fields(0).Value ) = "" Then Exit Do
  73. 		' Add a new row to the output array
  74. 		ReDim Preserve arrData( objRS.Fields.Count - 1, i )
  75. 		' Copy the Excel sheet's row values to the array "row"
  76. 		' IsNull test credits: Adriaan Westra
  77. 		For j = 0 To objRS.Fields.Count - 1
  78. 			If IsNull( objRS.Fields(j).Value ) Then
  79. 				arrData( j, i ) = ""
  80. 			Else
  81. 				arrData( j, i ) = Trim( objRS.Fields(j).Value )
  82. 			End If
  83. 		Next
  84. 		' Move to the next row
  85. 		objRS.MoveNext
  86. 		' Increment the array "row" number
  87. 		i = i + 1
  88. 	Loop
  89.  
  90. 	' Close the file and release the objects
  91. 	objRS.Close
  92. 	objExcel.Close
  93. 	Set objRS    = Nothing
  94. 	Set objExcel = Nothing
  95.  
  96. 	' Return the results
  97. 	ReadExcel = arrData
  98. End Function
  99.  

page last modified: 2024-04-16; loaded in 0.0076 seconds