PDA

View Full Version : [SOLVED] Overflow Error when using Format()



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.

Paul_Hossler
07-30-2019, 01:51 PM
IF that is an accurate copy/paste, then in



Format(Now, “MM / dd / yyyy”)


you have 'curly quotes', not the required regular-old VBA quote (ASCII 34)

Your Format might have been pasted from a web site or something

Otherwise it works


24711

BTW, you had at least 3 Format statements in there. It'd be helpful to let us know which one threw the error

mongoose
07-31-2019, 08:21 AM
It must have been when I copy/pasted as you suggested. It's solved now. Thank you!