尝试将 VBA 复制/粘贴循环与 VBA 'Open File'对话框组合在一起

Question

我有一个Excel宏，将行项从源文件复制并粘贴到基于Excel的表单。它打开表单模板并将每个行项保存为自己的文件，然后循环遍历其余行。现在，我有一个文件路径内置于代码中，它指向所需的表单模板，但我需要用户能够选择他们想要使用哪个文件作为模板。我有这两个过程的代码，但我还不能把它们结合起来。下面的示例导致编译错误:变量未定义。

到目前为止我写的是:

Option Explicit
Sub CopyToForm()
Dim wbSource As Workbook, wbForm As Workbook
Dim wsSource As Worksheet, wsForm As Worksheet
Dim formpath As String, foldertosavepath As String
Dim lrow As Long, i As Integer
Set wbSource = ThisWorkbook '~~> Write your code in Indication Tool.xls
Set wsSource = wbSource.Sheets("Indication Tool") '~~> Put the source sheet name
'~~> This opens the Processing Form template.
formpath = "C:File path.xls"

'~~> Prompts user with Open File Dialog Box
strCancel = "N"
strWorkbookNameAndPath = Application.GetOpenFilename _
    (FileFilter:=strFilt, _
     FilterIndex:=intFilterIndex, _
     Title:=strDialogueFileTitle)
'~~> Exits If No File Selected
If strWorkbookNameAndPath = "" Then
    MsgBox ("No Filename Selected")
    strCancel = "Y"
    Exit Sub
ElseIf strWorkbookNameAndPath = "False" Then
    MsgBox ("You Clicked The Cancel Button")
    strCancel = "Y"
    Exit Sub
End If
Workbooks.Open strWorkbookNameAndPath

'~~> This declares path where the Individual forms will be saved.
foldertosavepath = "C:File pathForms"
With wsSource
    '~~> Counts how many rows are in the Indication Tool
    lrow = .Range("B" & .Rows.Count).End(xlUp).Row
    If lrow < 18 Then MsgBox "No data for transfer": Exit Sub
    For i = 18 To lrow
        Set wbForm = Workbooks.Open(formpath) '~~> open the form
        Set wsForm = wbForm.Sheets("Processing Form") '~~> Declare which worksheet to activate
        '~~> Proceed with the copying
        .Range("B" & i).Copy wsForm.Range("F7:K7")
        .Range("C" & i).Copy wsForm.Range("D8")
        .Range("C" & i).Copy wsForm.Range("D30")
        .Range("D" & i).Copy wsForm.Range("H29")
        .Range("E" & i).Copy wsForm.Range("E29")
        .Range("F" & i).Copy wsForm.Range("D33")
        .Range("G" & i).Copy wsForm.Range("J30:K30")
        .Range("H" & i).Copy wsForm.Range("P33")
        .Range("I" & i).Copy wsForm.Range("L33:N33")
        .Range("L" & i).Copy wsForm.Range("H32")
        .Range("R" & i).Copy wsForm.Range("D87")
        .Range("C2:F2").Copy wsForm.Range("J101:M101")
        .Range("C3:M3").Copy wsForm.Range("E102:O102")
        '~~> Save the form using the client name
        wbForm.SaveAs foldertosavepath & .Range("B" & i).Value & ".xls"
        '~~> These steps are for formatting, as I haven't figured out how paste values only
        Set wbForm = Workbooks.Open(formpath)
        Cells.Select
        Selection.Copy
        wsForm.Activate
        '~~> This allows the format to be pasted into the updated Form
        wsForm.Unprotect
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteFormats
        wsForm.Protect
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        wbForm.Close ([SaveChanges:=False])
        Set wbForm = Nothing
        Set wsForm = Nothing
   Next
End With
End Sub

当我调试错误时，Sub CopyToForm()以黄色突出显示，strCancel =被选中。是否有办法将用户选择的文件设置为格式路径?提前感谢你的帮助，这件事一直是我的眼中钉。

Answer 1

Option Explicit被声明，strCancel没有被声明为一个变量。

添加Dim strCancel As String到您的代码