I'm trying to send value from a form to an excel template. Here's my code but I'm getting an error on line 7 saying "Run-time error '9'". Any suggestions would be appreciated. I'm a novice coder.
Code:
Private Sub Generate_Report__Excel__Click()
Dim MyXL As Object
FileCopy "D:\Testing\AB Trans 1-12_5 Template.xlsx", "D:\Testing\Sieve Report " & Me![Sample Number] & ".xlsx"
Set MyXL = GetObject("D:\Testing\Sieve Report " & Me![Sample Number] & ".xlsx")
MyXL.Application.Visible = True
MyXL.Application.WindowState = 3
MyXL.Parent.Windows(1).Visible = True
MyXL.Parent.ActiveWindow.WindowState = 2
With MyXL.worksheets(1)
.Range("H7").Value = Me![Client]
.Range("H8").Value = Me![Project]
.Range("A23").Value = Me![Source]
.Range("B23").Value = Me![Sample Description (spec)]
.Range("C23").Value = Me![Sample Number]
End With
MyXL.worksheets(1).Activate
OpenEnd:
Set MyXL = Nothing
Exit Sub
OpenErr:
Select Case Err.Number
Case 1004, 3265 ' can't write to protected cell; OR Item not found in this collection.
Resume Next
Case Else
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Critical error: isExcelSetup()"
Resume OpenEnd
End Select
End Sub