Results 1 to 3 of 3
  1. #1
    RalphS is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2017
    Posts
    1

    Excel table linked to Access and then Access query back to same Excel file

    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.
    Click image for larger version. 

Name:	FileAvailable.JPG 
Views:	17 
Size:	22.7 KB 
ID:	31290

    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

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Have you tried using something similar to
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCustomers", "C:\Users\Alex\desktop\test1", , "Sheet1"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCustomers", "C:\Users\Alex\desktop\test1", , "Sheet2"
    This will put the same data in sheet 2 as in 1, but works as a good example to show how the same file name is used and exports to append instead of overwriting. Note that I didnt give a file name extension on the file path., This is covered by excel when you specify the spreadsheet type.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCustomers", "C:\Users\Alex\desktop\test1", , "Sheet1"

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might want to use "acSpreadsheetTypeExcel12xml" instead of "acSpreadsheetTypeExcel12".

    "acSpreadsheetTypeExcel12" is Excel 2007 format
    "acSpreadsheetTypeExcel12xml" is Excel 2010 up format

    See https://ss64.com/access/acspreadsheettype.html
    and https://stackoverflow.com/questions/...-to-excel-2010

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-25-2016, 12:43 PM
  2. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  3. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  4. Replies: 7
    Last Post: 04-25-2013, 03:47 PM
  5. Replies: 1
    Last Post: 01-22-2013, 09:51 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums