mongoose
07-30-2019, 11:33 AM
I'm getting an Overflow error when trying to insert the date into the new file I have VBA creating when a user clicks a button.
Public Sub GenerateAuditFile()
Application.ScreenUpdating = False
Dim ActiveRow As Integer: ActiveRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
Dim SOPID As Variant
With ActiveSheet
'Set SOPID to variable
SOPID = .Cells(ActiveRow, 1)
End With
'Built new audit filename
Dim NewAuditFileName As String: NewAuditFileName = "SOP_Audit-JV-" & Format(SOPID, "000") & "-" & Format(Now, "MMddyyyy") & ".xlsx"
'If it doesn't already exist, copy and rename the SOP audit file
If Dir("\\JACKSONVILLE-DC\Common\SOP's for JV\SOP Audits\" & NewAuditFileName) <> "" Then
MsgBox ("File: " & NewAuditFileName & " exists")
Else
'Create new audit file
FileCopy "\\JACKSONVILLE-DC\Common\SOP's for JV\SOP Audits\SOP Audit Checklist-Template.xlsx", _
"\\JACKSONVILLE-DC\Common\SOP's for JV\SOP Audits\2019\" & NewAuditFileName
With ActiveSheet
Dim wbNewAudit As Workbook: Set wbNewAudit = Workbooks.Open("\\JACKSONVILLE-DC\Common\SOP's for JV\SOP Audits\2019\" & NewAuditFileName)
'wbNewAudit.Windows(1).Visible = False
'MsgBox (ActiveSheet.Cells(ActiveRow, 3))
wbNewAudit.Worksheets(1).Range("A1") = "SOP Title: " & .Cells(ActiveRow, 3)
wbNewAudit.Worksheets(1).Range("F1") = "Date: " & Format(Now, “MM / dd / yyyy”)
End With
End If
Application.ScreenUpdating = True
'TEST - Output SOPID
'MsgBox (Format(SOPID, "000"))
End Sub
The field it is being sent to is a "General" field type.
Public Sub GenerateAuditFile()
Application.ScreenUpdating = False
Dim ActiveRow As Integer: ActiveRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
Dim SOPID As Variant
With ActiveSheet
'Set SOPID to variable
SOPID = .Cells(ActiveRow, 1)
End With
'Built new audit filename
Dim NewAuditFileName As String: NewAuditFileName = "SOP_Audit-JV-" & Format(SOPID, "000") & "-" & Format(Now, "MMddyyyy") & ".xlsx"
'If it doesn't already exist, copy and rename the SOP audit file
If Dir("\\JACKSONVILLE-DC\Common\SOP's for JV\SOP Audits\" & NewAuditFileName) <> "" Then
MsgBox ("File: " & NewAuditFileName & " exists")
Else
'Create new audit file
FileCopy "\\JACKSONVILLE-DC\Common\SOP's for JV\SOP Audits\SOP Audit Checklist-Template.xlsx", _
"\\JACKSONVILLE-DC\Common\SOP's for JV\SOP Audits\2019\" & NewAuditFileName
With ActiveSheet
Dim wbNewAudit As Workbook: Set wbNewAudit = Workbooks.Open("\\JACKSONVILLE-DC\Common\SOP's for JV\SOP Audits\2019\" & NewAuditFileName)
'wbNewAudit.Windows(1).Visible = False
'MsgBox (ActiveSheet.Cells(ActiveRow, 3))
wbNewAudit.Worksheets(1).Range("A1") = "SOP Title: " & .Cells(ActiveRow, 3)
wbNewAudit.Worksheets(1).Range("F1") = "Date: " & Format(Now, “MM / dd / yyyy”)
End With
End If
Application.ScreenUpdating = True
'TEST - Output SOPID
'MsgBox (Format(SOPID, "000"))
End Sub
The field it is being sent to is a "General" field type.