Import tracking (1 Viewer)

[radshar]

Hello,

So I have 2 Tables
-Daily_cumulative
-tbl_Import

I have a button macro in my form which when pressed prompts me for the file I want to upload. The data is uploaded to Daily_cumulative.
tbl_Import should display for me the name (or path) of the file I just uploaded as well as date/time.

Is there a simply VBA to make this happen?
Here is the code I am using to do my import:

Private Sub Upload_Pull_Report_BUTTON_Click()
Dim objXLApp As Object
Dim File_import As Variant
Dim tbl_Import As Variant
                          
        Set objXLApp = CreateObject("excel.application")
        
          ' Ask the user for the file name to open.
        File_import = objXLApp.GetOpenFilename
        
    If File_import = False Then
        MsgBox "No file selected. Upload cancelled"
        Exit Sub
        Else
            DoCmd.TransferSpreadsheet acImport, , "dlyPULL_Cumulative", File_import, True
                     
            MsgBox "Upload completed"
    
    End If
            
End Sub

 

[sneuberg]

I'm not sure what you want but you could try this:

Private Sub Upload_Pull_Report_BUTTON_Click()
Dim objXLApp As Object
Dim File_import As Variant
Dim tbl_Import As Variant
                          
        Set objXLApp = CreateObject("excel.application")
        
          ' Ask the user for the file name to open.
        File_import = objXLApp.GetOpenFilename
        
    If File_import = False Then
        MsgBox "No file selected. Upload cancelled"
        Exit Sub
        Else
            DoCmd.TransferSpreadsheet acImport, , "dlyPULL_Cumulative", File_import, True
                     
         [COLOR="Blue"]   MsgBox "Uploaded " & File_import & vbCrLf & " Last Modified: " & FileDateTime(File_import)
            [/COLOR]
    
    End If
            
End Sub

 

[radshar]

Hi!

It looks like this would give me a prompt displaying what was uploaded. I'm actually wanting to take this a bit further and have that infomraiotn within a table in access, so we can log what was uploaded, incase a day was ever missed...etc..

any ideas?

I'm not sure what you want but you could try this:

Private Sub Upload_Pull_Report_BUTTON_Click()
Dim objXLApp As Object
Dim File_import As Variant
Dim tbl_Import As Variant
                          
        Set objXLApp = CreateObject("excel.application")
        
          ' Ask the user for the file name to open.
        File_import = objXLApp.GetOpenFilename
        
    If File_import = False Then
        MsgBox "No file selected. Upload cancelled"
        Exit Sub
        Else
            DoCmd.TransferSpreadsheet acImport, , "dlyPULL_Cumulative", File_import, True
                     
         [COLOR=blue]  MsgBox "Uploaded " & File_import & vbCrLf & " Last Modified: " & FileDateTime(File_import)
            [/COLOR]
    
    End If
            
End Sub

 

[sneuberg]

Well lets say you had a table named Imports with fields FileName (Text), LastModified (Date/Time) and ImportDateTime (Date/Time) then you could have some like:

Private Sub Upload_Pull_Report_BUTTON_Click()
Dim objXLApp As Object
Dim File_import As Variant
Dim tbl_Import As Variant
                        
        Set objXLApp = CreateObject("excel.application")
        
          ' Ask the user for the file name to open.
        File_import = objXLApp.GetOpenFilename
        
    If File_import = False Then
        MsgBox "No file selected. Upload cancelled"
        Exit Sub
    Else
            DoCmd.TransferSpreadsheet acImport, , "dlyPULL_Cumulative", File_import, True
            MsgBox "Uploaded " & File_import & vbCrLf & " Last Modified: " & FileDateTime(File_import)
           [COLOR="Blue"] CurrentDb.Execute "INSERT INTO [Imports] (FileName, LastModified, ImportDateTime) VALUES ( '" & File_import & "',#" & FileDateTime(File_import) & "#,#" & Now() & "#)"[/COLOR]
    End If
            
End Sub