Can the saving and closing of an Excel spreadsheet trigger an event in access? (1 Viewer)

[crownedzero]

So I'm hacking and slashing my way to learn VBA and I've come across something that no clue to figure out. I have a Msgbox that prompts the user to import a file ("Yes") or opens the file to be edited ("No") and then (hopefully) upon saving and closing the Excel spreadsheet I'd then like it to be imported. Possible?

Be warned I'm sure this is some ugly code and tips/pointers are welcome.

Public Function Import_Inventory()
    
    Dim Msg As String, Button As Variant, Title As String, Response As Variant, fDialog As FileDialog
    Dim strFileName As String, XL As Object
        
        Msg = "Have you removed any duplicates and blank spaces from the file you are importing?"
        Button = vbYesNo + vbDefaultButton2
        Title = "Import File"
        
    Response = MsgBox(Msg, Button, Title)
        If Response = vbNo Then
                Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
                Set XL = CreateObject("Excel.Application")
                
                    With fDialog
                        .InitialFileName = "N:\shared\Lori\BSLR"
                        .AllowMultiSelect = False
                        .Title = "Please select file to edit"
                        .Filters.Clear
                        .Filters.Add "Excel", "*.xls"
                        .Filters.Add "All Files", "*.*"
                            
                            If .Show = True Then
                                strFileName = .SelectedItems(1)
                                XL.Workbooks.Open strFileName
                                XL.Visible = True
                                
                            End If
                    End With
            
        Else
            
                Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
                    With fDialog
                        .InitialFileName = "N:\shared\Lori\BSLR"
                        .AllowMultiSelect = False
                        .Title = "Please select file to import"
                        .Filters.Clear
                        .Filters.Add "Excel", "*.xls"
                        .Filters.Add "All Files", "*.*"
        
                            If .Show = True Then
                                strFileName = .SelectedItems(1)
                                DoCmd.SetWarnings False
                                DoCmd.RunSQL "DELETE * FROM tblInventory"
                                DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblTemp", strFileName, True
                                DoCmd.RunSQL "INSERT INTO tblInventory SELECT * FROM tblTemp"
                                DoCmd.DeleteObject acTable, "tblTemp"
                                DoCmd.RunSQL "UPDATE tblInventory SET [Available]=0 WHERE [Available]<0"
                                DoCmd.SetWarnings True
                            End If
                    End With
        End If
                 
End Function

 

[Uncle Gizmo]

>>> Can the saving and closing of an Excel spreadsheet trigger an event in access?<<<

To be honest I don't know, however my suspicion is you would have to look at manipulating both excel and access from a .net program. It might pay you to search one of the many .net forums.

 

[crownedzero]

Had a thought I'm going to run with, but I figured I'd post it and possibly get some input. The problem with the Excel file is it contains blank fields in the UPC column which just so happens to be the primary key. Rather than brwse -> open said spreadsheet, I'm going to assume I can vba a link to it, open the temp table, allow the user to mod it, and upon closing run the import. Thoughts?

 

[darbid]

Running with your initial question I will take a wild stab at a possible solution

You can declare an object "with events" this would then mean that the events of what you have declared will fire just like for example a lost focus or any of the other ones.

I have done this for example with InternetExplorer so i imagine that if Excel has a on close event then this can also be done.