Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1643
  • Last Modified:

visual basic date/time Format() type mismatch error!

I'm trying to grab today's date and append it to a string (to be used as my final output file name), and I keep getting "type mismatch 'Format' " error...

here's a snippet of what I'm doing:

// Approach #1

Dim outputFile
Dim somePath

somePath = "C:\\ myDir\\outFileName-"
outputFile = somePath & Format(Now, "yyyy-mm-dd")

Get the "mismatch error" at the 'Format' line when I run it

// Approach #2
Dim outputFile
Dim thisYear
Dim thisMonth
Dim thisDay
Dim somePath

somePath = "C:\\ myDir\\outFileName-"
thisYear = Format(Now, "yyyy")
thisMonth = Format(Now, "mmm")
thisDay = Format(Now, "dd")

outputFile = somePath & thisYear & HYPHEN & thisMonth & thisDay

Still get that "mismatch" error at the call to 'Format'

This seems to be an easy thing...but why am I having so much trouble...??

any help?

  • 5
  • 2
  • 2
  • +4
1 Solution
This ahould work:

Dim outputFile As String
Dim somePath As String

somePath = "C:\\myDir\\outFileName-"
outputFile = somePath & Format(Now, "yyyy-mm-dd")

MsgBox outputFile
try using Date instead of Now, Now returns time too
have you tried using format$ instead of format ?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

jade03Author Commented:
Nothing seems to work...I tried all 3 suggestions...

adding "As String" gives me an error that says "Expected end of statement"

changing "Now" to "Date" gives me the same "mismatch type" error

changing "Format" to "Format$" gives me an error that says "Invalid Character"
Whats wrong with Vinnys code

Copy and paste this in

Private Sub Command1_Click()
Dim outputFile As String
Dim somePath As String

somePath = "C:\myDir\outFileName-"
outputFile = somePath & Format(Now, "yyyy-mm-dd")

MsgBox outputFile

End Sub
Do you have any missing references?  Sometimes missing references can cause format and some other commands to work incorrectly:
Select References from Project menu and make sure none of the references have (missing) beside them.

Also - make sure you fully declare all your variables:
Your type mismatch could be because when the format occurs it returns a variable of a certain type, if you have declared something without specifying it, it is a Variant by default (which is more memory hungry), but variants are often cast as a type.
dim intMe
intme = 30000

intMe will be cast as a integer - if you then
intMe = 35000
you will often get an error

jade03Author Commented:
vinny's code looks great..but for some reason, even if I were to just copy and paste that chunk into a brand new .vbs file with just that little chunk of code, it still complains of "missing end statement"

One thing to note, I'm writing my code using notepad, and running it in MS dos.

That date format is one problem. Another problem is I can't seem to write it out to an excel file. It keeps complaining of not having access to the excel file that I want to create...

I'm upping the point values if someone can help me solve BOTH problems...here's my entire code, written in notepad, and run using MS dos:




Dim olApp
Dim oNameSpace
Dim oFolders
Dim oFolder
Dim bFound
Dim n
Dim oFolderItems
Dim oAttachments

Dim strFileName
Dim mailFrom
Dim mailTo
Dim mailSubj
Dim mailRecDateTime
Dim aRowNum
Dim bRowNum
Dim cRowNum
Dim dRowNum
Dim finalOutputFile
Dim destPath
Dim thisYear
Dim thisMonth
Dim thisDay

destPath = "C:\\myDir\\TestMail\\output"

'Dim oExcel As Object
'Dim oBook As Object
'Dim oSheet As Object

Dim oExcel
Dim oBook
Dim oSheet


Function getFolderByName(ByVal oFolders, ByVal name)
    Dim oF
    Dim oFound

    For Each oF In oFolders
        If (oF.Name = name) Then
            Set oFolder = oF
            getFolderByName = TRUE
            Exit Function
        End If
        If (oF.Folders.Count > 0) Then
            oFound = getFolderByName(oF.Folders, name)
            If (oFound) Then
                getFolderByName = TRUE
                Exit Function
            End If
        End If
    getFolderByName = FALSE
    Exit Function
End Function

'Function WriteFile(ByVal FileName, ByVal Contents)
 '   Dim FS: Set FS = CreateObject("Scripting.FileSystemObject")
 '   On Error Resume Next

'  ForReading = 1, ForWriting = 2, ForAppending = 8
'   Dim OutStream: Set OutStream = FS.OpenTextFile(FileName, 8, True)
  ' OutStream.WriteLine Contents
' End Function


' Get Outlook COM Object
Set olApp = CreateObject( "Outlook.Application" )

'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

'thisYear = Format(Now, "yyyy")

'thisMonth = Format(Now, "mmm")
'thisDay = Format(Now, "dd")

'finalOutputFile = destPath & thisYear & HYPHEN & thisMonth & HYPTHEN & thisDay
'finalOutputFile = destPath & Format$(Now, "yyyy-mm-dd")
finalOutputFile = destPath & ".xls"
wscript.echo finalOutputFile

' Look for Inbox folder
Set oNameSpace = olApp.GetNameSpace("MAPI")
Set oFolders = oNameSpace.Folders
bFound = getFolderByName(oFolders, MESSAGE_FOLDER_NAME)
If (Not bFound) Then
    wscript.echo MESSAGE_FOLDER_NAME & " Outlook Folder not found!"
End If
'wscript.echo "Found Folder Named: " & oFolder.Name

n = 1 ' Message Filename Number

wscript.echo "will create workbook"
'Create column titles in the new excel workbook
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1").Value = "FROM"
 '  oSheet.Range("B1").Value = "TO"
   oSheet.Range("C1").Value = "SUBJECT"
   oSheet.Range("D1").Value = "DATE/TIME RECEIVED"
   oSheet.Range("A1:B1:C1:D1").Font.Bold = True

wscript.echo "done create workbook"

' Periodically Scan Through Messages
Do While (TRUE)
    ' Get Folder Items
    Set oFolderItems = oFolder.Items
    ' Scan Through Folder Items
    For Each oFolderItem in oFolderItems
'        wscript.echo oFolderItem.SenderName
        If (oFolderItem.UnRead) Then
            mailFrom = oFolderItem.SenderName
          wscript.echo "mailFrom: " & mailFrom
            mailSubj = oFolderItem.Subject
          '  mailTo = oFolderItem.Recipients
          mailRecDateTime = oFolderItem.ReceivedTime

            n = n + 1
            aRowNum = "A" & CLng(n)            
         '   bRowNum = "B" & CLng(n)
            cRowNum = "C" & CLng(n)
            dRowNum = "D" & CLng(n)

            'Insert data to cells in the new workbook
            oSheet.Range(aRowNum).Value = mailFrom
        '    oSheet.Range(bRowNum).Value = mailTo
            oSheet.Range(cRowNum).Value = mailSubj
            oSheet.Range(dRowNum).Value = mailRecDateTime

            ' Mark Item Read
            oFolderItem.UnRead = FALSE

            'Save the Workbook and Quit Excel

        End If
    wscript.Sleep SCAN_DELAY ' sleep X msecs
end it with a ;
Try prefixing Format$ and Now with VBA. and see if that helps.
If not look at references and make sure you have the following references checked:
1. Visual Basic for Applications
2. Visual Basic runtime objects and procedures
3. Visual Basic objects and procedures
4. OLE Automation

Option Explicit

Private Sub Form_Load()
   Dim outputFile       As String
   Dim somePath         As String

   somePath = "C:\\myDir\\outFileName-"
   outputFile = somePath & VBA.Format$(VBA.Now, "yyyy-mm-dd")

   MsgBox outputFile
End Sub
You can't use Format() in a VBS -- there's no command for it.  Closest equivalent is FormatDateTime() but it will not do what you want.  Adapt this code into your VBS for the desired result:

      Const HYPHEN = "-"

      Dim y, m, d

      y = DatePart("yyyy", Date())
      m = DatePart("m", Date())
      d = DatePart("d", Date())

      If Len(m) = 1 then m = "0" & m
      If Len(d) = 1 then d = "0" & d

      msgbox y & HYPHEN & m & HYPHEN & d

See http://www.drdev.net/article05.asp for more details.

I hate VBS.  I really do.


jade03Author Commented:
Thank you, jimbobmcgee !! That DatePart() function works great!

But I still can't write to an excel file...from the code that I have above, where I call oBook.SaveAs fileName it keeps telling me it can't access my folder...I checked and the folder is NOT readonly...so it should be ok...

anyhow, I guess an easier way would be to forget about writing to excel, and just create a text file...but I want to have it tab-delimited...but I don't know how to put tabs into vbs...any ideas?
jade03Author Commented:
Guys, I got it to work!! Thank you sooo much for all your help! :)
Tabs, for anyone who is wondering, can be added by using Chr(9).  Idon't think the constant, vbTab, will work in a VBS but I may be wrong...

jade03Author Commented:
Thanx, jimbobmcgee! That's very helpful of you. :)

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now