Open a workbook using FileDialog and manipulate it in Excel VBA

by

Thankyou Frank.i got the idea.
Here is the working code.

Option Explicit
Private Sub CommandButton1_Click()

  Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
  Dim fd As Office.FileDialog

  Set fd = Application.FileDialog(msoFileDialogFilePicker)

  With fd
    .AllowMultiSelect = False
    .Title = "Please select the file."
    .Filters.Clear
    .Filters.Add "Excel 2003", "*.xls?"

    If .Show = True Then
      fileName = Dir(.SelectedItems(1))

    End If
  End With

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  Workbooks.Open (fileName)

  For Each sheet In Workbooks(fileName).Worksheets
    total = Workbooks("import-sheets.xlsm").Worksheets.Count
    Workbooks(fileName).Worksheets(sheet.Name).Copy _
        after:=Workbooks("import-sheets.xlsm").Worksheets(total)
  Next sheet

  Workbooks(fileName).Close

  Application.ScreenUpdating = True
  Application.DisplayAlerts = True

End Sub

More Related Contents:

  1. Loop through files in a folder using VBA?
  2. How to error handle 1004 Error with WorksheetFunction.VLookup?
  3. How can Excel VBA open file using default application
  4. How to compare two entire rows in a sheet
  5. Run same excel macro on multiple excel files
  6. how to detect whether VBA excel found something?
  7. VBA : save a file with UTF-8 without BOM
  8. Excel tab sheet names vs. Visual Basic sheet names
  9. Excel VBA looping through multiple worksheets
  10. When one should use Set [e.g for SpecialCells return value]?
  11. Saving a Excel File into .txt format without quotes
  12. Getting error Procedure too large in VBA Macros (Excel)
  13. VBA: How to delete filtered rows in Excel?
  14. How to add data validation to a cell using VBA
  15. How to do late binding in VBA?
  16. How to break long string to multiple lines
  17. How to copy a chart from Excel to PowerPoint?
  18. vba paste not working
  19. Loop through Excel Sheets
  20. mac excel vba loop : from list & then export as pdf
  21. Can Advanced Filter criteria be in the VBA rather than a range?
  22. How can I open this VBA workbook faster?
  23. In Excel VBA, what is the way to check if the web page is fully loaded?
  24. Excel Useform: How to hide application but have icon in the taskbar
  25. Reading Web Pages using Excel VBA
  26. Excel VBA: auto click and open file from website
  27. Run-time error ’91’ (Object variable or With block variable not set)
  28. How to have vba execute every 10 minutes?
  29. Read and write from/to registry in VBA
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment