我想在公司SharePoint里创建一个日志文件。

概念： 一个所有其他文件都指向的单一日志文件（称为 logs.xlsx）。现在，我在我的文件中创建一个临时日志工作表，我想在关闭文件之前把该工作表中的所有数据复制到我的 logs.xlsx 里面。

这是我现在用来实现的代码：

' == ROUTINE DI SALVATAGGIO LOG E PULIZIA FILE == '
Sub Workbook_BeforeClose(Cancel as Boolean)

    ' == PARIAMO IL FILE DOVE SALVARE DEFINITIVAMENTE I LOG == '
    Workbooks.Open ("https:/[companySP]/logs.xlsx")
    Set logWB = Workbooks("logs.xlsx")
    Set logWS = logWB.Worksheets("log list")

    ' == SALVIAMO I DATI DEI LOG REGISTRATI == '
    Dim r As Integer
    Dim c As Integer

    r = 2
    c = 1

    Do Until Cells(r, 1).Value = ""
        Do Until Cells(r, c).Value = ""
            logWB.logWS.cell(r, c).Value = ActiveLogWB.ActiveLogWS.cell(r, c).Value
            c = c + 1
        Loop
        c = 1
        r = r + 1
    Loop

    logWB.Save
    logWB.Close

    ActiveLogWB.Save
    ActiveLogWB.Close

End Sub

出于某种原因，日志文件会打开，但随后会自动关闭，我在 Set logWB = Workbooks("logs.xlsx") 行遇到了错误。

我应该怎么修复？

解决方案

你需要将Workbook对象 Set 关联到你正在打开的工作簿。

Set wb = Workbooks.Open()

你不需要用循环即可把数据从一个工作表传输到另一个。你可以通过一次操作传输所有数据，这将是最佳实践的方法。

下面有三个示例：

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

'CURRENT BOOK:Determine range with logs to copy starting at A2.
  Dim ws As Worksheet, lr As Long, lc As Long, copy_logs As Range
        Set ws = ThisWorkbook.Sheets("!!!!")        '<--- Update sheet name
            lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                Set copy_logs = ws.Range(ws.Cells(1, 2), ws.Cells(lr, lc))



'LOG BOOK: Find paste destination to append log file instead of overwriting
        Dim fp As String, logWB As Workbook, log As Worksheet
        Dim pr As Long, paste_logs As Range   'pr = Paste Row (first empty row)
            fp = ("https:/[companySP]/logs.xlsx")       'File path
            Set logWB = Workbooks.Open(fp)
            Set log = logWB.Sheets("log list")


                pr = log.Range("A" & log.Rows.Count).End(xlUp).Offset(1).Row

                '------- 3 METHODS TO WRITE LOGS WITHOUT LOOPS ---------

                '1) Range transfer is most effecient if you you only need values
                Set write_logs = log.Range("A" & pr).Resize(lr - 1, lc - 1)
                write_logs.Value = new_logs.Value

                '2) Standard copy/paste which retains cell formatting, formulas,etc
                copy_logs.copy log.Range("A" & pr)

                '3) Copy and choose attributes to paste
                copy_logs.copy
                log.Range("A" & pr).PasteSpecial (xlPasteValues)

        'Close & save log book
        'Other closing processes

End Sub

注意：在会话过程中，如果你的主工作簿可能不会生成任何日志，请相应地修改代码（例如 If 0 new_logs then End'）。