Below method gets any row from a provided Microsoft Excel worksheet as a String array. This can be quite handy, if you for example want to get all field names from a worksheet.

Source Code

The code references a Microsoft Excel API. It automates Excel in a background process and therefore requires Excel to be running as well as the parent workbook of a handed worksheet to be opened before calling this function.

' @Author - Alexander Bolte
' @ChangeDate - 2014-12-11
' @Description - Returns the header row of a provided Excel.Worksheet object
' as a zero based String() Array.
' @Param worksheet - an Excel.Worksheet object of an Excel.Workbook, 
' which has been opened before calling this function. 
' @Param row - a Long indicating the row, which field headers
' should be read from.
' @Returns a zero based String() Array containing the field header names.
' @Remarks - The method starts always in column one in a handed worksheet.
Function getExcelHeader(workSheet, row) ' As String()
  Dim c ' As Integer
  Dim header ' As String()
  Dim importRng ' As Range
 
  Set importRng = workSheet.UsedRange
  ReDim header(importRng.Columns.Count - 1)
  For c = 0 to UBound(header)
      header(c) = cStr(workSheet.Cells(row,c + 1).Value)
  Next ' column in worksheet.
  Set importRng = Nothing
 
  getExcelHeader = header
End Function

Last Updated: 30 July 2015

Hits: 2906

The following method allows you to save a Microsoft Excel file to a users hard disc.

Source Code

' @Author - Alexander Bolte
' @ChangeDate - 2015-01-05
' @Description - Saves provided excel file under given file path.
' Overwrites any existing file in same directory with same name.
' @Param xlFile - An object of type Excel.Workbook.
' @Param trgPath - A String holding a path to save a provided file to.
' @Returns true, if a provided excel file has been saved successfully
' else false.
Function saveXlFile(xlFile, trgPath) ' As Boolean
  Dim ret ' As Boolean
  Dim fso ' As Scripting.FileSystemObject
 
  On Error Resume Next
 
  ' Delete file if it already exists.
  deleteFileOnHD trgPath
  ' Save provided file under given file path.
  xlFile.SaveAs trgPath
  ret = True
 
  If Err.Number <> 0 Then
    Err.Clear
  End If
 
  saveXlFile = ret
End Function

Referenced Methods

Below list shows references to other methods.

VBScript to delete file

Last Updated: 19 August 2015

Hits: 2654

If you need to get the parent directory of a given path to a file or a directory, the following function might help.

Not the best performance I have to admit since it is relying on String operations.

Source Code

' @Author - Alexander Bolte
' @ChangeDate - 2015-02-10
' @Description - Returns the path to a parent directory for a handed path.
' @Param path - a String providing a path to a directory or a file.
' @Returns a String providing a path to a parent directory including the
' last path delimiter. If an error occurs, an empty String is returned.
Function getParentDirectoryPath(path) ' As String
  Dim dirPath ' As String
  Dim splitted ' As String
  Dim i ' As Integer

  On Error Resume Next

  dirPath = ""
  splitted = split(path, "\")
  For i = lBound(splitted) To uBound(splitted) - 1
     dirPath = dirPath & splitted(i) & "\"
  Next

  If Err.Number <> 0 Then
     dirPath = ""
     Err.Clear
  End If

  getParentDirectoryPath = dirPath
End Function

Last Updated: 10 February 2015

Hits: 2906