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

Posted on 2004-11-04
Last Modified: 2013-11-25
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?

Question by:jade03
    LVL 28

    Expert Comment

    This ahould work:

    Dim outputFile As String
    Dim somePath As String

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

    MsgBox outputFile
    LVL 4

    Expert Comment

    try using Date instead of Now, Now returns time too
    LVL 28

    Expert Comment

    have you tried using format$ instead of format ?

    Author Comment

    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"
    LVL 48

    Expert Comment

    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
    LVL 5

    Expert Comment

    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


    Author Comment

    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's my entire code, written in notepad, and run using MS dos:


    CONST SCAN_DELAY  = 15000
    CONST HYPHEN = "-"


    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
    LVL 48

    Expert Comment

    end it with a ;
    LVL 22

    Expert Comment

    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
    LVL 16

    Accepted Solution

    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 for more details.

    I hate VBS.  I really do.



    Author Comment

    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 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?

    Author Comment

    Guys, I got it to work!! Thank you sooo much for all your help! :)
    LVL 16

    Expert Comment

    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...


    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now