从Word到Excel的VBA

debugcn 发表于 Dev

加贝

我的Word模板中的某些VBA代码存在问题。目的是使代码打开Excel书籍，引用名为“ Log”的工作表，并根据Word文档的名称查找行。名称匹配后，我想将单元格值更改为“ completed”。然后保存并关闭Excel工作簿。我尝试了下面的代码，它将打开正确的工作簿，但不会将单元格更新为“已完成”，但出现错误：

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

大本钟

您需要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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。