我在使用 Word 模板中的一些 VBA 代码时遇到问题。目的是让代码打开 Excel 书籍,引用名为“Log”的工作表,并根据 Word 文档的名称查找行。一旦名称匹配,我想将单元格值更改为“已完成”。然后保存并关闭 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