8/9/2016 - 10:24 AM

Excel VBA - Files Sheets Workbooks

Excel VBA - Files Sheets Workbooks

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
GetFolder = sItem
Set fldr = Nothing
End Function
MsgBox Workbooks("path-fullname.xls").Path
Dim MyFile As String
MyFile = Application.GetOpenFilename()

Workbooks.Open (MyFile)
Dim book As Workbook, sheet As Worksheet, text as String

For Each book In Workbooks
  text = text & "Workbook: " & book.Name & vbNewLine & "Worksheets: " & vbNewLine
  For Each sheet In book.Worksheets
    text = text & sheet.Name & vbNewLine
  Next sheet
  text = text & vbNewLine
Next book

MsgBox text
Dim directory As String, fileName As String, text As String

directory = "c:\test\"

'can use * for multiple and ? for single character
fileName = Dir(directory & "*.xl?")

Do While fileName <> ""
  text = text & fileName & vbNewLine
  'Dir is a special function. Use it again without arguments to get the next file
  fileName = Dir()

MsbBox text
Dim folderName As String

With Application.FileDialog(msoFileDialogFolderPicker)
  .AllowMultiSelect = False
  On Error Resume Next
    folderName = .SelectedItems(1)
  On Error GoTo 0
End With