MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.
Public xl As Excel.Application 'Object 'used for late binding Public wbk As Excel.Workbook 'Object 'used for late binding Public sht As Excel.Worksheet 'Object 'used for late binding Public Function Excel_Sheet_Names(Filename As String) As String Dim intLoop As Integer On Error GoTo ProcError OpenWorkbookHidden (Filename) For intLoop = 1 To wbk.Sheets.Count Excel_Sheet_Names = Excel_Sheet_Names & ";" _ & chr$(34) & wbk.Sheets(intLoop).Name & chr$(34) Next Excel_Sheet_Names = Mid(Excel_Sheet_Names, 2) ProcExit: If Not wbk Is Nothing Then wbk.Close False If Not xl Is Nothing Then xl.Quit Exit Function ProcError: Select Case Err.Number Case -2147221080 'Automation error - occurs when no workbook is already open Case Else Debug.Print Err.Number, Err.Description Resume ProcExit End Select End Function Public Sub OpenWorkbookHidden(Filename As String) On Error GoTo ProcError Set xl = GetObject(, "Excel.Application") 'Workbook Name is the file name (without the path) Set wbk = xl.Workbooks(Mid(Filename, InStrRev(Filename, "\") + 1)) ProcExit: Exit Sub ProcError: If Err.Number = 429 Then Set xl = CreateObject("excel.application") Resume Next ElseIf Err.Number = 9 Then 'Workbook is not already open Set wbk = xl.Workbooks.Open(Filename) Resume Next Else Debug.Print Err.Number, Err.Description End If End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|3,000 rows of comma separated values||14||31|
|Unique List in UserForm||3||25|
|Excel Import/Export Named Ranges and Values||4||32|
|How to create UDF to pull out Number Prefixes from Excel cell values?||10||25|
Join the community of 500,000 technology professionals and ask your questions.