Imports System
Imports System.IO
Imports Microsoft.VisualBasic.FileIO
Imports Microsoft.Office.Interop
Module Program
    Dim oxl As Excel.Application
    Dim owbs As Excel.Workbooks
    Dim owb As Excel.Workbook
    Dim osheets As Excel.Worksheets
    Dim osheet As Excel.Worksheet
    Dim owr As Excel.Range

    Dim tempName As String

    Sub Main()
        oxl = CreateObject("Excel.Application")
        oxl.Visible = False
        Dim path As String = "G:\Matthews Asia\Matthews Raw Data"
        Dim names As String() = Directory.GetFiles(path, "*.xlsx")
        Dim newDetails(,) As Object

        'Get the new names and the boundaries of the data set
        newDetails = getNewNames(names)

        'Printing the detials to check getNewNames works or not - works fine
        printNewDetails(newDetails) 'Working fine

        'Rename files
        rename(names, newDetails)

        Console.ReadLine()
    End Sub

    Function getNewNames(ByVal names() As String) As Object(,)
        'Declare Object type array to be returned with the details
        Dim newDetails(names.Length - 1, 2) As Object
        Dim lastRow, lastColumn As Integer

        For i =0 To names.GetUpperBound(0)
            'point to the excel file
            owb = CType(oxl.Workbooks.Open(names(i)), Excel.Workbook) 'Sometimes error comes here
            osheet = CType(owb.Worksheets("Holdings"), Excel.Worksheet)
            owr = CType(osheet.Range("A7"), Excel.Range)

            'Pick new name of file and add the excel extension
            tempName = CStr(owr.Value) & ".xlsx"

            'row & column number of last data point in the dataset
            lastColumn = CType(osheet.Range("A13").End(Excel.XlDirection.xlToRight), Excel.Range).Column
            lastRow = CType(osheet.Range("A13").End(Excel.XlDirection.xlDown), Excel.Range).Row
            newDetails(i, 0) = tempName
            newDetails(i, 1) = lastRow
            newDetails(i, 2) = lastColumn
        Next
        owb.Close()

        Return newDetails
    End Function

    Function printNewDetails(ByVal details As Object(,)) As Integer
        For i = 0 To details.GetUpperBound(0)
            Console.WriteLine("New name: {0}", details(i, 0))
            Console.WriteLine("Last row: {0}", details(i, 1))
            Console.WriteLine("Last Column: {0}", details(i, 2))
        Next
        Return 1
    End Function

    Sub rename(ByVal oldName As String(), ByVal tempArray As Object(,))
        For i = 0 To oldName.GetUpperBound(0)
            FileSystem.RenameFile(oldName(i), CStr(tempArray(i, 0))) 'Error Here
        Next
    End Sub

End Module

i am trying to rename some excel files all of which is in a particular directory. The code does the following:

1. It opens each file which has just one sheet
2. Then it picks the string in cell A7 in each of those files
3. It also finds out the last row and last column of the data set (cell A13 is the starting point of the dataset in each of the files)
4. Finally, in an object array newDetails we store the string in cell A7 in the first column, the last row of the dataset (column 2) and last column of the dataset (column 3). Each row has data corresponding to one excel file
5. After that, the code renames the files using the rename subroutine -- the idea is to swap the old names which is stored in the names array with the string value in the first column of the newDetails array.

But When I run the code, the following error message comes: The process cannot access the file because it is being used by another process. I have opened task manager, manually closed all excel processes and even restarted the computer - even then this error comes. Have attached the screenshot of the error. Requesting help.

Strangely, when I run the code more than once, sometimes I am getting the error in the line owb = CType(oxl.Workbooks.Open(names(i)), Excel.Workbook) and that error warns me to check if the files are corrupted or not. The files are not corrupted because when I manually open them there is no problem.

CodePudding user response：

When a filename starts with ~$, it usually indicates that the file is already open (in Excel). However, sometimes this file doesn't get deleted. If you're sure that Excel is no longer running, such as after a reboot, and such a file exists, one can delete it. Of course, one could also just ignore it when getting a list of files.

You haven't mentioned if you're using .NET or .NET Framework and which version. VS 2019 supports .NETCore 3.1, .NET 5 (no longer supported), and .NET Framework versions.

One may consider using NuGet package DocumentFormat.OpenXml or ClosedXml instead. However, if one desires to use Excel Interop, try the following:

Add a reference: Microsoft Excel xx.x Object Library (ex: Microsoft Excel 16.0 Object Library)

• Project
• Add Project Reference...
• COM
• Microsoft Excel xx.x Object Library (ex: Microsoft Excel 16.0 Object Library)
• OK

Create a class (name: XLInfo.vb)

Public Class XLInfo
    Public Property OriginalFilename As String
    Public Property LastRow As Integer
    Public Property LastColumn As Integer
    Public Property RenamedTo As String
End Class

Create a module (name: HelperExcel.vb)

Imports Microsoft.Office.Interop
Imports System.IO

Module HelperExcel
    Private Function GetExcelFilenames(folderPath As String) As List(Of String)
        Dim filenames As List(Of String) = New List(Of String)

        For Each fqFilename As String In Directory.GetFiles(folderPath, "*.xlsx")
            'get only the filename
            Dim fn As String = Path.GetFileName(fqFilename)

            If Not fn.StartsWith("~") Then
                Debug.WriteLine($"Info: adding '{fqFilename}'...")
                filenames.Add(fqFilename) 'add
            End If
        Next

        Return filenames
    End Function

    Public Function ProcessExcelFiles(folderPath As String) As List(Of XLInfo)
#Disable Warning CA1416
        Dim infos As List(Of XLInfo) = New List(Of XLInfo)

        Dim oxl As Excel.Application = Nothing
        Dim owbs As Excel.Workbooks = Nothing
        Dim owb As Excel.Workbook = Nothing
        Dim osheets As Excel.Worksheets = Nothing
        Dim osheet As Excel.Worksheet = Nothing
        Dim owr As Excel.Range = Nothing

        'get filenames
        Dim names As List(Of String) = GetExcelFilenames(folderPath)

        Try
            'create new instance
            oxl = New Excel.Application()

            oxl.Visible = False

            For i As Integer = 0 To names.Count - 1
                'create new instance
                Dim info As XLInfo = New XLInfo()

                'create reference
                Dim fn As String = names(i)

                'set value
                info.OriginalFilename = fn

                'open workbook
                'owb = oxl.Workbooks.Open(Filename:=fn, [ReadOnly]:=True)
                owb = oxl.Workbooks.Open(Filename:=fn)

                'open worksheet
                osheet = owb.Worksheets(1)

                'set value - this is the new filename
                info.RenamedTo = Path.Combine(Path.GetDirectoryName(fn), $"{osheet.Range("A7").Value.ToString()}.xlsx")

                'ToDo: get last column
                'set value - last column
                'info.LastColumn = DirectCast(osheet.Range("A13").End(Excel.XlDirection.xlToRight), Excel.Range).Column

                'ToDo: get last row
                'set value - last row
                'info.LastRow = DirectCast(osheet.Range("A13").End(Excel.XlDirection.xlDown), Excel.Range).Row

                'add
                infos.Add(info)

                If osheet IsNot Nothing Then
                    'release all resources
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(osheet)

                    'set value
                    osheet = Nothing
                End If

                If owb IsNot Nothing Then
                    'save
                    owb.SaveCopyAs(info.RenamedTo)
                    'owb.SaveAs2(Filename:=info.RenamedTo)

                    'close
                    owb.Close(False)

                    'release all resources
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(owb)

                    'set value
                    owb = Nothing
                End If
            Next
        Finally
            If osheet IsNot Nothing Then
                'release all resources
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(osheet)

                'set value
                osheet = Nothing
            End If

            If owb IsNot Nothing Then
                'close
                owb.Close(False)

                'release all resources
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(owb)

                'set value
                owb = Nothing
            End If

            If oxl IsNot Nothing Then
                'quit
                oxl.Quit()

                'release all resources
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oxl)

                'force garbage collection
                GC.Collect()
            End If
        End Try

#Enable Warning CA1416

        'sleep
        System.Threading.Thread.Sleep(250)

        'delete original filenames

        If Not Directory.Exists(Path.Combine(folderPath, "Original Files")) Then
            'create folder if it doesn't exist
            Directory.CreateDirectory(Path.Combine(folderPath, "Original Files"))
        End If

        For i As Integer = 0 To names.Count - 1
            If File.Exists(names(i)) Then
                'move file to .\Original Files\<filename>
                File.Move(names(i), Path.Combine(folderPath, "Original Files", Path.GetFileName(names(i))), True)
                Debug.WriteLine($"File moved to '{Path.Combine(folderPath, "Original Files", Path.GetFileName(names(i)))}'")

                'ToDo: if one desires to delete the original filenames,
                'uncomment the line below
                'delete file
                'File.Delete(names(i))
            End If
        Next

        Return infos
    End Function
End Module

Note: The code above was tested with VS 2022 (.NET 6) since .NET 5 is no longer supported. See here for more info. If using .NET Framework, one can remove #Disable Warning CA1416 and #Enable Warning CA1416.

Usage:

Sub Main(args As String())
    'ToDo: replace folder name with desired folder name
    Dim infos As List(Of XLInfo) = ProcessExcelFiles("C:\Temp")

    For Each info As XLInfo In infos
        Dim msg As String = $"OriginalFilename: '{info.OriginalFilename}' RenamedTo: '{info.RenamedTo}' LastRow: '{info.LastRow}' LastColumn: '{info.LastColumn}'"
        Debug.WriteLine(msg)
        Console.WriteLine(msg)
    Next
End Sub

Resources:

• Excel Interop
• What is .NET Framework
• Microsoft .NET Framework
• Microsoft .NET and .NET Core
• System.IO.File
• System.IO.Path
• Interpolated Strings (Visual Basic Reference)
• Collections (Visual Basic)
• Objects and classes in Visual Basic
• Option Strict Statement

Additional Resources

• Programmatically getting the last filled excel row using C#