Timestamp in file name transferspreadsheet

[vfgilber]

Hi,

I am currently using transferspreadsheet to send a table to Excel. Currently, my code appends the current date (using Date) to the filename. I would like to have the current time appended as well, but when I use Now function, it fools up the filename because the time has colons in it.

Is there a way to convert the time to something like 22h32m59s?

Thanks,

Vince

 

[jdraw]

Hi,

I am currently using transferspreadsheet to send a table to Excel. Currently, my code appends the current date (using Date) to the filename. I would like to have the current time appended as well, but when I use Now function, it fools up the filename because the time has colons in it.

Is there a way to convert the time to something like 22h32m59s?

Thanks,

Vince

Here is a function that will output a string of YYMMDDHHMMSS

You could append the output to your file name.

'---------------------------------------------------------------------------------------
' Procedure : fjNumTime
' DateTime  : 2000-10-23 14:44
' Author    : drawbrij
' Purpose   : To get some variable characters based on current time.

'Possible uses: to insert into filenames to make them relatively unique.
'
'Calling example   strMytxt = fjNumTime()
'---------------------------------------------------------------------------------------
'
Function fjNumTime() As String

Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String, strInString As String

    strInString = Format(Now(), "YYMMMDDHHMMSS")
    lngLen = Len(strInString)
    strOut = ""
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        
        If Asc(strTmp) = 58 Or _
            Asc(strTmp) = 32 Then
            GoTo bypassOutput
        Else
            strOut = strOut & strTmp
        End If
bypassOutput:
    Next i
      
    fjNumTime = strOut 
    
End Function

 

[gadjet]

jdraw,
Thanks for the code, I wasn't looking for that function but I can definately use it.

Cheers,
Gadjet