从Word到Excel的VBA

Question

我在一个Word模板中使用一些VBA代码遇到了问题。旨在通过代码打开一个Excel工作簿，引用名为“Log”的工作表，并基于Word文档的名称查找一行。一旦匹配成功，我希望将单元格的值更改为“completed”。然后保存并关闭Excel工作簿。我已尝试下面的代码，它可以打开正确的工作簿，但无法将单元格更新为“Completed”，我收到了一个错误：

Private Sub CommandButton1_Click()
'***********************************************************************************************
'Message box asking if you are sure you are ready to submit report for completion
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you are ready to submitt this Smart Learning Report?"        ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
Title = "Submit SLR"    ' Define title.
Help = "DEMO.HLP"    ' Define Help file.
Ctxt = 1000    ' Define topic context.
    ' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbNo Then    ' User chose Yes.
    MyString = "No"    ' Perform some action.
    Exit Sub
End If

'***********************************************************************************************
'File name to be used to update the status in the Log
Dim CONum As String

'File name to be used to save report as PDF
Dim PDF As String

CONum = ActiveDocument.FullName
PDF = Replace(CONum, ".docm", ".pdf")

'***********************************************************************************************
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'***********************************************************************************************
'Open excel, find cell with matching Word document name (CONum) in column K, 
_and change the cell value in column J (-1) to COMPLETED, save and close excel when completed.
Dim excelApp As Excel.Application
Dim openExcel As Excel.Workbook

  Set excelApp = Excel.Application
  Set openExcel = excelApp.Workbooks.Open("C:\Users\ggonzales\Desktop\SLR's\GPT SLR Submission.xlsm")
  excelApp.Visible = True

With openExcel
    Dim CRow As Excel.Range
    Set CRow = Sheets("Log").Range("K:K").Find(What:=CONum,     LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False)
    If Not CRow Is Nothing Then
    CRow.Offset(, -1).Value = "COMPLETED"
    End If
ActiveWorkbook.Save 'Filename:=COFile, FileFormat:=52
ActiveWorkbook.Close
End With

'***********************************************************************************************
Application.ScreenUpdating = True
Application.DisplayAlerts = True

'***********************************************************************************************
'Delete Command Button so it does not show on the final report, _
and so no one can submit the same report twice.
For Each o In ActiveDocument.InlineShapes
   If o.OLEFormat.Object.Caption = "Complete & Submit Report" Then
        o.Delete
    End If
Next

'***********************************************************************************************
'Save a copy of the report as a PDF
ActiveDocument.ExportAsFixedFormat OutputFileName:= _
        PDF, ExportFormat:= _
        wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
        wdExportOptimizeForPrint, Range:=wdExportAllDocument

'***********************************************************************************************
'Close and save report (Word Document)
ActiveDocument.Close SaveChanges:=True

End Sub

- GabeG726

---

哪一行代码出错了，错误信息是什么？ - BigBen

@BigBen，这是一个运行时错误'91'：对象变量或With块变量未设置。我在尝试定义“CRow”是的那一行遇到了这个问题。我认为这可能与“Find”函数有关。 - GabeG726

Answer 1

您需要使用Set来处理对象变量：

Set CRow = Sheets("Log").Range("K:K").Find(What:=CONum, LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False)

接下来，您需要测试Find是否成功。您可以在此处使用Offset来简化操作。

If Not CRow Is Nothing Then
    CRow.Offset(,1).Value = "COMPLETED"
End If

请注意，实际上你并没有使用With...End With。

修改后的代码：

With openExcel
    Dim CRow As Excel.Range
    Set CRow = .Sheets("Log").Range("K:K").Find(What:=CONum, LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False)

    If Not CRow Is Nothing Then
        CRow.Offset(,1).Value = "COMPLETED"
    End If

    .Save 'Filename:=COFile, FileFormat:=52
    .Close
End With

- BigBen