Check path folder by vb

Question

I am trying to get folderpath and then looping for Excel sheet in this folder

Imports Excel= Microsoft.Office.Interop.Excel

Public Sub Combine()

    Dim objApp As Excel.Application
    Dim objBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
    Dim myrange As Excel.Range
    Dim folderPath As String
    Dim fileName As String

    folderPath = @"FolderPath"
    fileName = Dir(folderPath & "*.xls")

    Do While (fileName <> "")

        objBook = objApp.Workbooks.Open(Filename:=Txt_GetPath.Text & fileName = True)
        objBook.Activate()

        For Each objSheet In objBook.Sheets
            objSheet.Copy(After:=objBook.Sheets(1))
        Next objSheet

        objBook.Close()

    Loop

End Sub

But at the line objBook =objApp.Workbooks.open(...) I get an error of

The path must be full

What is wrong with the code?

At line objBook =objApp.Workbooks.open(...) This error appears — Ekram Mohamed, Dec 01 '18 at 13:21
The `Dir` function will only give you the first Excel file in the directory. Is that what you want, or do you want to process all the Excel files in the directory? — Andrew Morton, Dec 01 '18 at 13:26

Andrew Morton · Answer 1 · 2018-12-01T16:53:30.063

2

To process all the files in the directory, you can make use of the Directory.EnumerateFiles function.

It is not completely straightforward to dispose of the Excel application when the code has finished using it, so I added some in for you from The proper way to dispose Excel com object using VB.NET?.

I do not know what you intended with the = True in the command to open the workbook, so I left it out.

Option Strict On

Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel

Public Sub DoCombine()

    Dim objApp As New Excel.Application
    Dim objBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet

    Dim folderPath As String = Txt_GetPath.Text

    For Each excelFile In Directory.EnumerateFiles(folderPath, "*.xls")

        objBook = objApp.Workbooks.Open(Filename:=excelFile)
        objBook.Activate()

        For Each objSheet In objBook.Sheets
            objSheet.Copy(After:=objBook.Sheets(1))
        Next objSheet

        objBook.Close()

    Next

    objApp.Quit()

End Sub

Public Sub Combine()

    ' Call the method to do the actual work
    DoCombine()

    ' Now Let the GC clean up (twice, to clean up cycles too)
    GC.Collect()
    GC.WaitForPendingFinalizers()
    GC.Collect()
    GC.WaitForPendingFinalizers()

End Sub

Use the Combine() method - it will call the code you use in the DoCombine() method and make sure that there are not lots of copies of Excel left running on the computer - you would see them in Task Manager even if they were not on the screen.

It is (almost) always a good idea to use Option Strict On because it lets Visual Studio show you where some things are wrnog in code.

edited Dec 01 '18 at 16:53

answered Dec 01 '18 at 14:09

Andrew Morton

24,203
9
60
84

For Each excelFile In Directory.EnumerateFiles(folderPath, "*.xls") objBook = objApp.Workbooks.Open(Filename:=Txt_GetPath.Text) ' error in this line objBook.Activate() – Ekram Mohamed Dec 01 '18 at 14:42
@EkramMohamed What is `Txt_GetPath.Text`? The variable `excelSheet` has the full path of an Excel document. – Andrew Morton Dec 01 '18 at 14:51
folderpath = Txt_GetPath.Text – Ekram Mohamed Dec 01 '18 at 15:01
this is the full program to combine excel sheet https://docs.google.com/document/d/1wHmg3HlY3M142GZAb9lMSlE_Nc_z-DqoXklkLZDKM4I/edit?usp=sharing – Ekram Mohamed Dec 01 '18 at 15:06
@EkramMohamed 1) Delete the line `fileName = Dir(folderPath & "*.xls")`. 2) Change `objBook = objApp.Workbooks.Open(Filename:=”FolderPath”)` to `objBook = objApp.Workbooks.Open(Filename:=excelFile)`. – Andrew Morton Dec 01 '18 at 15:09
@EkramMohamed I edited the code in my answer to use the textbox for the directory to use. If you use the code *exactly* as I have shown, it really should work: I have tested it as working on Windows 10 with the Excel in Office 365. – Andrew Morton Dec 01 '18 at 16:56

score 0 · Answer 2 · answered May 31 '20 at 13:09

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim strFileName As String
    Dim xl As New Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
        Dim Directory = FolderBrowserDialog1.SelectedPath
        Dim Files() As System.IO.FileInfo
        Dim DirInfo As New System.IO.DirectoryInfo(Directory)
        Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
        For Each File In Files
            wb = xl.Workbooks.Open(File.FullName)
            For Each ws In wb.Sheets
                strFileName = Microsoft.VisualBasic.Left(File.Name, 
        (InStrRev(File.Name, ".", -1, vbTextCompare) - 1))
                If ws.Name = "SCHEDULE" Then
                    ws.Range("S7", "S50").Value2 = strFileName
                End If
            Next
            wb.Close()
        Next
        xl.Quit()
    End If
    End Sub

Check path folder by vb

2 Answers2

Linked