Actually you can list all worksheet names in a workbook using the object path Workbook.Worksheets(index).Name.

I needed to have the names as strings in a VBA.Collection, so I developed a little function collecting the names.

' @Author - Alexander Bolte
' @Change Date - 26.12.2013
' @Description - Creates a list of all sheet names in given workbook.
' @Param wrk - Excel workbook the sheet name collection should be created from.
' @Returns - VBA.Collection holding all sheet names from given Microsoft Excel workbook.
Public Function getSheetNamesFromWorkbook(ByRef wrk As Workbook) As VBA.Collection
    Dim i As Integer
    Dim sheetNames As New VBA.Collection
    
    On Error GoTo errHandle:
    
    For i = 1 To wrk.Worksheets.Count
        sheetNames.Add wrk.Worksheets(i).Name
    Next i
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    
    Set getSheetNamesFromWorkbook = sheetNames
End Function

Last Updated: 07 June 2014

Hits: 4898

Recently a collegue had to import a SharePoint list into Excel for automated processing.

Below you can find the source code that I provided him with as an example.

Source Code

Sub LinkedSharePointList()
 
    ActiveSheet.ListObjects.Add SourceType:=xlSrcExternal, _
 
        Source:=Array(<"URL String">, <"ListNameString">, _
 
        <"{GUIDString}">), LinkSource:=True, Destination:=Range("A1")
 
End Sub

Last Updated: 25 July 2015

Hits: 4426

This function copies used range of handed worksheet and replaces it with values only.

Source Code

' @Author - Alexander Bolte
' @ChangeDate - 2014-10-09
' @Description - Copy / Pasting contents of handed Worksheets UsedRange
' as values into the same range.
' @Param trg - a Worksheet object holding data.
' @Remarks -
' UsedRange of worksheet will be overwritten by itself pasting only values and nothing else.
' Formulas will be lost.
Public Function copyPasteWorksheetValues(ByRef trg As Worksheet)
    trg.UsedRange.Copy
    trg.UsedRange.PasteSpecial xlPasteValues
End Function

Last Updated: 25 July 2015

Hits: 4796