Hi all,
I can go through the wizard, and I can link a worksheet from an existing Excel workbook, but when I try and automate it in VBA, it won't link the worksheet as a Linked Excel table - it imports the table!
As I need to add records to this Excel sheet, and then disconnect the link, I find this very frustrating! Do I need to add some more code to then save the Excel workbook before any of this will work properly?
Everything but the LINK (as opposed to imported) seems to be working...
Thanks in anticipation everyone!
Code:
DoCmd.TransferSpreadsheet acLink, , "TblTmpBilling", "Z:\HealthIntelligenceUnit\Data\ICPDATA.xlsx", True, "TblTmpBilling"
DoCmd.RunSQL "DELETE * FROM TblTmpBilling;"
DoCmd.RunSQL "SELECT TblBilling.RegisterID, TblBilling.ContactDate, TblBilling.BillingCode, TblBilling.ExportFlag, *" & _
"INTO TblTmpBilling FROM TblBilling WHERE (((TblBilling.ExportFlag)=0 Or (TblBilling.ExportFlag) Is Null));"
cdb.Execute "DROP TABLE TblTmpBilling", dbFailOnError