I have an Access database that uses ODBC to import data from an Oracle server. I have an Excel spreadsheet that acts as a "Master File" to the Access db. There are a number of sheets in the spreadsheet, each with tables of data, each linked into the Access Database. They are linked, and not imported, by design.
The intent is for the keeper of the spreadsheet to not have to work in Access. No forms, etc., just maintain the spreadsheet and run macros. The database is macro driven, and they execute macros, in sequence, via shortcuts, where the database runs minimized. Accessaphobic.
The steps in the routine ends up looping from Excel to Access, back to Excel and back to Access.
Doing a query in Excel to pull the data from Access tends to lock up. I don't know why at this point. A 30 second query in Access never completes in the spreadsheet refresh. So, if Excel can't pull, then Access must push. Here is my current code, everything works fine, right up to the msgbox:
Sub exportQueryADODB()
Dim dbs As Database
Set dbs = CurrentDb
Set rsQuery = dbs.OpenRecordset("qryFirstQueryName")
Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("C:\MasterTables.xlsx")
targetWorkbook.Worksheets("Sheet1").Range("Table1" ).CopyFromRecordset rsQuery
Set rsQuery = dbs.OpenRecordset("qrySecondQueryName")
targetWorkbook.Worksheets("Sheet2").Range("Table2" ).CopyFromRecordset rsQuery
MsgBox "Reply to Excel Prompt"
excelApp.workbooks.Close
excelApp.Quit
End Sub
My dilemma is that when the Excel opens, it opens read only. It will accept the two queries, but there is a lag at the message box, after which an Excel message appears: "File Now Available" and the user must select a "Read-Write" button, which is not the default button.
After replying to the Excel message, the user then closes the Access message, after which the user is then forced through the Excel File Save As sequence to over-write the original file.
I need the Access code to open the Excel file, update the two tables, then save & exit without user input. But, I can't get around Excel's "File Now Available" and the Save As routine. Help!
Thanks