vba format invalid property assignment

• Search forums
• Board Rules

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature may not be available in some browsers.

• If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
• Question Forums
• Excel Questions

VBA Error wrong number of arguments or invalid property assignment

• Thread starter Access Beginner
• Start date Nov 4, 2018
• Tags dim end error set vba

Access Beginner

Active member.

• Nov 4, 2018

Hello, I've found this bit of code during my searches on the web and this does work for me in other spreadsheets however, when I try and run this in another workbook the following error is coming up "wrong number of arguments or invalid property assignment" The following is highlighted and the word "Format" is highlighted in yellow Code: TempFile = Environ$("temp") & "/" & Format(Today, "dd-mm-yy h-mm-ss") & ".htm" Code: Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook TempFile = Environ$("temp") & "/" & Format(Today, "dd-mm-yy h-mm-ss") & ".htm" 'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With 'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.ReadAll ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=") 'Close TempWB TempWB.Close savechanges:=False 'Delete the htm file we used in this function Kill TempFile Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function Code: Sub MailReports() Dim rng As Range Dim OutApp As Object Dim OutMail As Object Dim rngAttach As Range Dim hlink As String 'Set range for file attachment, cell should contain the path and file name With ActiveSheet Set rngAttach = .Range("b5") End With Set rng = Nothing ' Only send the visible cells in the selection. Set rng = Sheets("Email_Reports").Range("b7:c70").SpecialCells(xlCellTypeVisible) If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected. " & _ vbNewLine & "Please correct and try again.", vbOKOnly Exit Sub End If With Application .EnableEvents = False .ScreenUpdating = False End With Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .SentOnBehalfOfName = ThisWorkbook.Sheets("Email_Reports").Range("d6").Value .To = ThisWorkbook.Sheets("Email_Reports").Range("d2").Value .CC = ThisWorkbook.Sheets("Email_Reports").Range("D3").Value .BCC = ThisWorkbook.Sheets("Email_Reports").Range("D4").Value .Subject = ThisWorkbook.Sheets("Email_Reports").Range("D5").Value .Display .HTMLBody = RangetoHTML(rng) ' In place of the following statement, you can use ".Display" to ' display the e-mail message. strFileName = Dir(rngAttach.Value) .Attachments.Add Replace(rngAttach.Value, "*.*", "") .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub  

Excel Facts

Rick Rothstein

Mrexcel mvp.

Today is an Excel function name, it is not a VBA function (it evaluates to Null if you don't assign a value to it). I think you would want to use Now in place of it... TempFile = Environ$("temp") & "/" & Format( Now , "dd-mm-yy h-mm-ss") & ".htm"  

• Nov 5, 2018

Hi Rick, Thanks for your reply. I changed it to.... TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" And still get the same error  

Access Beginner said: Hi Rick, Thanks for your reply. I changed it to.... TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" And still get the same error Click to expand...

MrExcel MVP, Moderator

I suspect that in that problem workbook you have used Format as the name of something, probably a procedure. If you amend the code to use VBA.Format instead of just Format, does it work?  

Thanks Rory, that was precisely the problem. Thanks for the help.  

Similar threads

• Dec 7, 2023
• Jan 26, 2024
• Nov 7, 2023
• jeffreyCarlino
• Nov 3, 2023
• Oct 5, 2023

Forum statistics

Share this page.

We've detected that you are using an adblocker.

Which adblocker are you using.

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

Contribute to the Microsoft 365 and Office forum! Click  HERE  to learn more  💡

March 14, 2024

Contribute to the Microsoft 365 and Office forum!

Click  HERE  to learn more  💡

Top Contributors in Excel: HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Rory Archibald   ✅

March 11, 2024

Top Contributors in Excel:

HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Rory Archibald   ✅

• Search the community and support articles
• Microsoft 365 and Office
• Search Community member

Ask a new question

I am getting a compile error: Wrong number of arguments or invalid property assignment

I cannot get the "format" to show up as "Format" in this particular workbook.  

TempFilePath = Environ$("temp") & "\"     TempFileName = "Part of " & Sourcewb.Name & " " _                  & f ormat (Now, "dd-mmm-yy h-mm-ss")

this path works in all my other workbooks and they are all saved as XLSM.

Any suggestions?

Report abuse

Replies (14) .

* Please try a lower page number.

* Please enter only numbers.

• Independent Advisor

Was this reply helpful? Yes No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

Thanks for your feedback.

It has all of that already... here is what it looks like.  The email, subject, and body are not up to date.  The are in bold needs to be in caps but keeps moving back to lowercase after I change it.

Sub Mail_ActiveSheet() ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010     Dim FileExtStr As String     Dim FileFormatNum As Long     Dim Sourcewb As Workbook     Dim Destwb As Workbook     Dim TempFilePath As String     Dim TempFileName As String     Dim OutApp As Object     Dim OutMail As Object     With Application         .ScreenUpdating = False         .EnableEvents = False     End With     Set Sourcewb = ActiveWorkbook     ' Next, copy the sheet to a new workbook.     ' You can also use the following line, instead of using the ActiveSheet object,    ' if you know the name of the sheet you want to mail :     ' Sheets("Sheet5").Copy     ActiveSheet.Copy     Set Destwb = ActiveWorkbook     ' Determine the Excel version, and file extension and format.     With Destwb         If Val(Application.Version) < 12 Then             ' For Excel 2000-2003             FileExtStr = ".xls": FileFormatNum = -4143         Else             ' For Excel 2007-2010, exit the subroutine if you answer             ' NO in the security dialog that is displayed when you copy             ' a sheet from an .xlsm file with macros disabled.             If Sourcewb.Name = .Name Then                 With Application                     .ScreenUpdating = True                     .EnableEvents = True                 End With                 MsgBox "You answered NO in the security dialog."                 Exit Sub             Else                 Select Case Sourcewb.FileFormat                 Case 51: FileExtStr = ".xlsx": FileFormatNum = 51                 Case 52:                     If .HasVBProject Then                         FileExtStr = ".xlsm": FileFormatNum = 52                     Else                         FileExtStr = ".xlsx": FileFormatNum = 51                     End If                 Case 56: FileExtStr = ".xls": FileFormatNum = 56                 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50                 End Select             End If         End If     End With     ' You can use the following statements to change all cells in the    ' worksheet to values.     '    With Destwb.Sheets(1).UsedRange     '        .Cells.Copy     '        .Cells.PasteSpecial xlPasteValues     '        .Cells(1).Select     '    End With     '    Application.CutCopyMode = False     ' Save the new workbook, mail, and then delete it.     TempFilePath = Environ$("temp") & "\"     TempFileName = "Part of " & Sourcewb.Name & " " _                  & format( Now, "dd-mmm-yy h-mm-ss")     Set OutApp = CreateObject("Outlook.Application")          Set OutMail = OutApp.CreateItem(0)     With Destwb         .SaveAs TempFilePath & TempFileName & FileExtStr, _                 FileFormat:=FileFormatNum         On Error Resume Next        ' Change the mail address and subject in the macro before        ' running the procedure.         With OutMail             .To = "blank for now"             .CC = ""             .BCC = ""             .Subject = "This is the Subject line"             .Body = "Hello World!"             .Attachments.Add Destwb.FullName             ' You can add other files by uncommenting the following statement.             '.Attachments.Add ("C:\test.txt")             ' In place of the following statement, you can use ".Display" to             ' display the mail.             .Send         End With         On Error GoTo 0         .Close SaveChanges:=False     End With     ' Delete the file after sending.     Kill TempFilePath & TempFileName & FileExtStr     Set OutMail = Nothing     Set OutApp = Nothing     With Application         .ScreenUpdating = True         .EnableEvents = True     End With End Sub

Question Info

• Norsk Bokmål
• Ελληνικά
• Русский
• עברית
• العربية
• ไทย
• 한국어
• 中文(简体)
• 中文(繁體)
• 日本語