从Word到Excel的VBA [英] VBA from Word to Excel

问题描述

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

I'm having issues with some VBA code that lives in a Word Template. The intent is to have the code open an Excel book, reference to Sheet named "Log", and find a row based on the Word document's name. Once the name is matched, I want to change a cells value to "completed". Then save and close the Excel workbook. I have tried the code below which will open the correct work book, but wont update the cell to "Completed", I get an error:

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 进行简化.

Then you should test if the Find was successful. And you can use Offset here to simplify.

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

注意，您实际上并没有使用 With ... End With .

Note, you are not actually using the 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

这篇关于从Word到Excel的VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！