run time error 424

[Phone_Guy]

I'm trying to save this file to "today's" date with a macro. I'm now getting a 424 run time error. I'm very new to VBA , so I was wondering if someone can tell me what I'm doing wrong in this code? It's pieced together from code I've found in forums and changed a little. Also can you recommend any good books to learn more about VBA? Thanks for any help.


Sub RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail()
Dim FileName As String
Dim Today As Date


If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "There is more then one sheet selected," & vbNewLine & _
"be aware that every selected sheet will be published"
End If

'Call the function with the correct arguments
'Tip: You can also use Sheets("Sheet3") instead of ActiveSheet in the code(sheet not have to be active then)
FileName = RDB_Create_PDF(ActiveSheet, "C:\Documents and Settings\safety2\Desktop\" & Format(Today, "MM_DD_YYYY").pdf, True, False)

'For a fixed file name and overwrite it each time you run the macro use
'RDB_Create_PDF(ActiveSheet, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)

If FileName <> "" Then


RDB_Mail_PDF_Outlook FileName, Range("g11"), "Time Sheet", _
"See the attached PDF file for daily time sheet" _
& vbNewLine & vbNewLine & "", True
Else
MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
"Microsoft Add-in is not installed" & vbNewLine & _
"You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
"The path to Save the file in arg 2 is not correct" & vbNewLine & _
"You didn't want to overwrite the existing PDF if it exist"
End If
End Sub

 

[Norie]

Is the error on the line beginning FileName = ...?

If it is I think it's because you appear to be trying to use some property called pdf.

That definitely doesn't exist, especially not for a string which is what Format returns.

Try this.

Filename = RDB_Create_PDF(ActiveSheet, "C:\Documents and Settings\safety2\Desktop\" & Format(Today, "MM_DD_YYYY") & ".pdf", True, False)

 

[Phone_Guy]

Thank you!! Someday I'll figure this stuff out. With the "today" command what clock does it reference? It just saved as 1230 1899. The computer clock is correct. Any ideas?

 

[Phone_Guy]

Sorry, I figured it out... I had to add "Today = Date" below Dim Today as Date. Thanks again for your help.

 

[Norie]

The reason you were getting the date as 1230 1899 was because you hadn't given Today a value so it would be regarded as 0 or empty.

You could just use Date - you don't need to have a variable for the date, though it might be a good idea if you are using it for other things.