無法重命名excel檔案-有解無憂

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

我正在嘗試重命名一些 excel 檔案，所有這些檔案都在特定目錄中。該代碼執行以下操作：

它打開每個只有一張紙的檔案
然后它在每個檔案中選擇單元格 A7 中的字串
它還找出資料集的最后一行和最后一列（單元格 A13 是每個檔案中資料集的起點）
最后，在一個物件陣列中，newDetails我們將字串存盤在單元格 A7 的第一列、資料集的最后一行（第 2 列）和資料集的最后一列（第 3 列）。每行資料對應一個excel檔案
之后，代碼使用rename子例程重命名檔案——想法是將存盤在names陣列中的舊名稱與陣列第一列中的字串值交換newDetails。

但是當我運行代碼時，會出現以下錯誤訊息：The process cannot access the file because it is being used by another process。我打開了任務管理器，手動關閉了所有 excel 行程，甚至重新啟動了計算機 - 即使這樣還是出現了這個錯誤。附上錯誤截圖。請求幫助。無法重命名 excel 檔案

奇怪的是，當我不止一次運行代碼時，有時我會在行中收到錯誤，owb = CType(oxl.Workbooks.Open(names(i)), Excel.Workbook) 并且該錯誤警告我檢查檔案是否已損壞。這些檔案沒有損壞，因為當我手動打開它們時沒有問題。無法重命名 excel 檔案

uj5u.com熱心網友回復：

當檔案名以開頭時~$，通常表示該檔案已打開（在 Excel 中）。但是，有時此檔案不會被洗掉。如果您確定 Excel 不再運行（例如在重新啟動后）并且存在這樣的檔案，則可以將其洗掉。當然，也可以在獲取檔案串列時忽略它。

您沒有提到您使用的是.NET哪個.NET Framework版本。VS 2019 支持.NETCore 3.1、.NET 5（不再支持）和.NET Framework 版本。

可以考慮改用 NuGet 包DocumentFormat.OpenXml或ClosedXml。但是，如果想使用Excel Interop，請嘗試以下操作：

添加參考：（Microsoft Excel xx.x Object Library例如：Microsoft Excel 16.0 物件庫）

專案
添加專案參考...
串口
Microsoft Excel xx.x Object Library（例如：Microsoft Excel 16.0 物件庫）
好的

創建一個類（名稱：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

創建模塊（名稱：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

注意：上面的代碼是用 VS 2022 (.NET 6) 測驗的，因為 .NET 5 不再受支持。有關更多資訊，請參見此處。如果使用 .NET Framework，可以洗掉#Disable Warning CA1416和#Enable Warning CA1416.

用法：

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

資源：

Excel互操作
什么是 .NET 框架
微軟.NET框架
微軟 .NET 和 .NET 核心
系統.IO.檔案
系統.IO.路徑
內插字串（Visual Basic 參考）
集合 (Visual Basic)
Visual Basic 中的物件和類
期權嚴格宣告

其他資源

使用 C# 以編程方式獲取最后填充的 excel 行

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/536765.html

標籤：网络办公室互操作excel互操作办公自动化excel自动化

上一篇：電腦優化與清理

下一篇：圖文并茂解釋開源許可證GPL、BSD、MIT、Mozilla、Apache和LGPL的區別