TransferSpreadsheet acImport and RunApp

[alienscript]

Hi Access experts,

I have this Access problem with the file to be imported having different filenames and one way to know its latest copy is by looking at the file's Modified Date from the Windows Explorer. (I am using network WindowsXP and my Explorer path has its Target at "%SystemRoot%\explorer.exe" and Start in "%HOMEDRIVE%%HOMEPATH%"


1.1) How VB code to add order to let Access using transferSpreadsheet acImport the Excel file from a specified folder with the latest "modified date", as can be seen from the Window Explorer ?
(this is because the Excel data is updated only on every Tuesday with different filenames, therefore I auto fire-up my Access database on every Tuesday 6.00pm to import the spreadsheet with (i)only the latest modified date or (ii)alternatively the excel file with the system now() date).

1.2) How to let Access open up Windows Explorer (i assume using RunApp) to default as the path D:\weekly Service Data\

Then pop up a dialog box MsgBox "the Excelfile you want to process is having
the latest Modified Date ?"
Click YES and the Windows Explorer must close.
Click NO and the Dialog box must close but the Windows Explorer remains
open.


Apprecite very much for any help offered.
Thanks in anticipation.

 

[Surjer]

This is how I did it. This is in VB.NET but you can probably tweek it to work in VBA...

SKILL LEVEL: MEDIUM

In the event that you want to search...

        Dim WithEvents FileLoader As FindDownloads
        Dim letterNUM As Short
        Dim fso2 As New Scripting.FileSystemObject
        Dim fil1, fil2 As Object

       Dim rs as ADODB.Recordset
       Set RS = New ADODB.Recordset

        With rs
            .Fields.Append("fileID", ADODB.DataTypeEnum.adBSTR, 255)
            .Fields.Append("Filename", ADODB.DataTypeEnum.adBSTR, 255)
            .Fields.Append("Modified", ADODB.DataTypeEnum.adDate)
            .CursorType = ADODB.CursorTypeEnum.adOpenStatic
            .LockType = ADODB.LockTypeEnum.adLockOptimistic
            .Open()
        End With

        FileLoader = New FindDownloads
        FileLoader.FindDownloadFiles("C:\FoldertoSearchin\")
'Now you can look at rs to see all the files

rs.movefirst

'Put this somewhere on your form

Private Sub FileLoader_FoundOne(ByVal Filename As String, ByVal modified As String, ByRef Cancel As Boolean) Handles FileLoader.FoundOne
        i = i + 1
		With rs
			.AddNew()
			.Fields.Item("fileID").Value = i
			.Fields.Item("filename").Value = Filename
            .Fields.Item("modified").Value = modified
			.Update()
        End With
    End Sub

'Put this in a module

Public Event FoundOne(ByVal Filename As String, ByVal modified As String, ByRef Cancel As Boolean)
	
	
	Public Function FindDownloadFiles(ByVal RootFolder As String) As Boolean
        Dim System_Renamed As Scripting.FileSystemObject
		Dim Foldr As Scripting.Folder
		Dim SubFolder As Scripting.Folder
		Dim Fyle As Scripting.File
		
		' Return new FileSystemObject.
		System_Renamed = New Scripting.FileSystemObject
		
		' Get folder.
		Foldr = System_Renamed.GetFolder(RootFolder)
		
		' Loop through Files collection, firing an event if the extension matches
		
		Dim Cancel As Boolean
		Cancel = False
		
		Dim length1 As Short
		length1 = Len("Download")
        For Each SubFolder In Foldr.SubFolders
            Foldr = System_Renamed.GetFolder(SubFolder.Path)
            For Each Fyle In Foldr.Files
                System.Diagnostics.Debug.WriteLine(UCase(Left(System_Renamed.GetBaseName(Fyle.Path), 8)) & UCase(Left(System_Renamed.GetExtensionName(Fyle.Path), 3)))
                If UCase(Left(System_Renamed.GetBaseName(Fyle.Path), length1)) = "DOWNLOAD" And UCase(Left(System_Renamed.GetExtensionName(Fyle.Path), 2)) = "JP" Then
                    RaiseEvent FoundOne(Fyle.Path, CStr(Fyle.DateLastModified), Cancel)
                    '            MsgBox "Found a file " & Fyle.path
                    If Cancel Then
                        FindDownloadFiles = False
                        Exit Function
                    End If
                End If
            Next Fyle
        Next SubFolder
    End Function

 

[Steve5]

Wow

I wish I had the knowledge to help more people as I see most people on this sight do. I must say Jerry, that was very impressive answer