Macro opening referenced Excel table

[Fostertrident]

Hi



I'm using the following Macro to import a table from an excel file maintained by another team.

Option Compare Database


Private Sub Form_Load()


End Sub


Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE A.* FROM A;"
DoCmd.TransferSpreadsheet acImport, , "A", "B", True, "TABLE1!"
DoCmd.SetWarnings True
End Sub

This works brilliantly when I have no other excel files open. However, if I have any Excel file open (not referenced in the above Macro at all) then when I open access it attempts to open the referenced Excel file (called "B" in the above Macro)

This anomaly also happened to me when trying to use a Linked table in a previous iteration of my database.

Do you think this is a Access issue? An Excel issue? or a Windows 10 issue? If anyone has any pointers how to prevent this it would be greatly appreciated as :-

1) it slows the macro down considerably (the referenced excel file is huge)
2) it means if someone else tries to use the excel file or database the whole thing crashes
3) I would like things to work the way they are intended.


I look forward to hearing from you.

[June7]

What you posted is VBA, not a macro. Macros in Access are very different. Macros in Excel are really VBA code.

Don't need to reference fields in DELETE action because entire record is deleted.

I tested TransferSpreadsheet with an Excel workbook open. The workbook being imported from does not open. Cannot replicate issue.