G
Guest
I'm using Basic to plug data into a linked Excel spreadsheet, then opening
the spreadsheet so Excel will provide calculated results, relinking my table
while Excel is open, then closing Excel.
For some reason Excel is not being properly released. If I repeat the
process a couple of times Access will hang. If I try to open my Excel file
from Windows, I'm told that it is still being edited from Access.
' link SpreadSheet as table
aa = DeleteATable (TheTableName) ' remove previous version of table
DoCmd.TransferSpreadsheet acLink, , TheTableName, FileName, True, SheetName
DoCmd.OpenQuery "PlugValuesFromAccessIntoLinkedExcelTable" ‘ run update query
'open Excel to take out calculated values from linked table
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FileName)
xlApp.Application.Visible = False
‘ re-link SpreadSheet table, so linked table will have Excel’s calculated
values
aa = DeleteATable (TheTableName) ' remove previous version of table
DoCmd.TransferSpreadsheet acLink, , TheTableName, FileName, True, SheetName
'close Excel
xlBook.Close (False) 'changed from false
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
the spreadsheet so Excel will provide calculated results, relinking my table
while Excel is open, then closing Excel.
For some reason Excel is not being properly released. If I repeat the
process a couple of times Access will hang. If I try to open my Excel file
from Windows, I'm told that it is still being edited from Access.
' link SpreadSheet as table
aa = DeleteATable (TheTableName) ' remove previous version of table
DoCmd.TransferSpreadsheet acLink, , TheTableName, FileName, True, SheetName
DoCmd.OpenQuery "PlugValuesFromAccessIntoLinkedExcelTable" ‘ run update query
'open Excel to take out calculated values from linked table
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FileName)
xlApp.Application.Visible = False
‘ re-link SpreadSheet table, so linked table will have Excel’s calculated
values
aa = DeleteATable (TheTableName) ' remove previous version of table
DoCmd.TransferSpreadsheet acLink, , TheTableName, FileName, True, SheetName
'close Excel
xlBook.Close (False) 'changed from false
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing