FileDateTime of a external file being imported to a Table on a Report

[jw83]

I need a way to get the FileDateTime of a html file that I am importing to a table. I am basically wanting to show on a report the last date and time the data was updated. I tried putting a text box on the report and putting =FileDateTime("filename.html") but that didn't work. I then get an "Enter Parameter Value" box for FileDateTime. Any suggestions?

 

[sxschech]

Here is some code that will show last modified and created date:

Sub GetDateLastModified()

    Dim oFS As Object
    Dim strFilename As String
    
    'Put your filename here
    strFilename = "filename.html"


    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

    MsgBox strFilename & " was last modified on " & oFS.GetFile(strFilename).DateLastModified & " created on " & oFS.GetFile(strFilename).DateCreated
    
    Set oFS = Nothing

End Sub

 

[jw83]

How do I get the date listed as in a text box on the report?

 

[jw83]

I put the code in a module and on the textbox I put =GetDateLastModified and I still get the same error. What am I doing wrong?

 

[sxschech]

Private Sub Command2_Click()


Dim oFS As Object
Dim strFilename As String
strFilename = "filename.html"
Set oFS = CreateObject("Scripting.FileSystemObject")

Me.TextBox = oFS.getfile(strFilename).datelastmodified

Set oFS = Nothing
End Sub

 

[jw83]

I already have this code.

On Error GoTo Err_cmdRunReport_Click

Dim stDocName As String
Dim strWhere As String

If Not IsNull(Me.List37) Then
Select Case Me.List37
Case Else
' strWhere = strWhere & BuildWhere()
stDocName = Me.List37
DoCmd.OpenReport stDocName, acPreview, , strWhere
DoCmd.Maximize
Me.Visible = False
End Select
Else
MsgBox "You must select a report", vbOKOnly + vbInformation, "Select Report"
Me.List37.SetFocus
End If
Exit_cmdRunReport_Click:
Exit Sub

Err_cmdRunReport_Click:
Select Case Err.Number
Case 2501 'ignore
Case Else
MsgBox Err.Description
End Select
Resume Exit_cmdRunReport_Click

On Error GoTo 0
Exit Sub

cmdRunReport_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdRunReport_Click of VBA Document Form_frmReportSelection"

End Sub

Where do I add your code?

 

[sxschech]

Like this? You may need to play around with the location of the code as not sure if your intent is to show the date of the html file after the report is run.

On Error GoTo Err_cmdRunReport_Click

[red]
    Dim oFS As Object
    Dim strFilename As String
[/red]
    Dim stDocName As String
    Dim strWhere As String

    
    If Not IsNull(Me.List37) Then
        Select Case Me.List37
            Case Else
             '  strWhere = strWhere & BuildWhere()
                stDocName = Me.List37
                DoCmd.OpenReport stDocName, acPreview, , strWhere
                DoCmd.Maximize
                Me.Visible = False
        
[red]
                strFilename = "filename.html"
                Set oFS = CreateObject("Scripting.FileSystemObject")
                Me.TextBox = oFS.getfile(strFilename).datelastmodified

                Set oFS = Nothing
[/red]
        End Select
     Else
        MsgBox "You must select a report", vbOKOnly + vbInformation, "Select Report"
        Me.List37.SetFocus
    End If
Exit_cmdRunReport_Click:
    Exit Sub

Err_cmdRunReport_Click:
    Select Case Err.Number
        Case 2501 'ignore
        Case Else
            MsgBox Err.Description
    End Select
    Resume Exit_cmdRunReport_Click

   On Error GoTo 0
   Exit Sub

cmdRunReport_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdRunReport_Click of VBA Document Form_frmReportSelection"
    
End Sub