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
CONST HYPHEN "-"

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?


jade03Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vinnyd79Commented:
This ahould work:

Dim outputFile As String
Dim somePath As String

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

MsgBox outputFile
0
learning_t0_pr0gramCommented:
try using Date instead of Now, Now returns time too
0
vinnyd79Commented:
have you tried using format$ instead of format ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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"
0
Mikal613Commented:
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
0
brianb99999Commented:
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.
ie
dim intMe
intme = 30000

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

Brian.
0
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:

'----------------------------------------------------------------
' CONSTANTS
'----------------------------------------------------------------

CONST MESSAGE_FOLDER_NAME  = "Inbox"
CONST SCAN_DELAY  = 15000
CONST HYPHEN = "-"

'----------------------------------------------------------------
' VARIABLES
'----------------------------------------------------------------

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



'----------------------------------------------------------------
' LOCAL FUNCTIONS
'----------------------------------------------------------------

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


'----------------------------------------------------------------
' MAIN BODY
'----------------------------------------------------------------

' 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!"
    WScript.Quit(1)
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
            oBook.SaveAs(finalOutputFile)

            oExcel.Quit
           
            oFolderItem.Delete()
        End If
    Next
    wscript.Sleep SCAN_DELAY ' sleep X msecs
Loop
0
Mikal613Commented:
end it with a ;
0
danaseamanCommented:
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
0
jimbobmcgeeCommented:
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.

HTH

J.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
0
jade03Author Commented:
Guys, I got it to work!! Thank you sooo much for all your help! :)
0
jimbobmcgeeCommented:
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...

J.
0
jade03Author Commented:
Thanx, jimbobmcgee! That's very helpful of you. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.