Results 1 to 5 of 5
  1. #1
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32

    VBA - Refresh excel file data connections

    Hi all

    I am looking to utilise this code:

    Code:
    Private Sub Form_AfterUpdate()Dim strFile As String
    strFile = "C:\Users\x\OneDrive - x\Approvalapp\Risk Register link.xlsx"
    If FileExists(strFile) Then
    Kill "C:\Users\x\OneDrive - x\Approvalapp\Risk Register link.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Risk Register summary_createtable", _
        "C:\Users\x\OneDrive - x\Approvalapp\Risk Register link.xlsx", True
    Else
    
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Risk Register summary_createtable", _
        "C:\Users\x\OneDrive - x\Approvalapp\Risk Register link.xlsx", True
        End If
        
    Dim appexcel As Object
    Set appexcel = CreateObject("Excel.Application")
    appexcel.Workbooks.Open "C:\Users\x\OneDrive - x\Riskregisterquery.xlsx"
    appexcel.ActiveWorkbook.refreshall
    Set appexcel = Nothing
    End Sub
    The first part of the code exports a query from access. the second part then opens an excel that has a data connection to that exported excel file from access. I wish to refresh this data connection.

    However, it seems to leave excel running in the background causing the file to fail to synch with Onedrive unless I manually close this background excel manually via task manager. I've tried putting a .wait in to see if it needs time to complete but that doesn't seem to work. I've also unticked 'enable background refresh' as I read somewhere this can cause issues. But still, Excel doesn't close and therefore the VBA does not complete correctly as when I try to run the VBA again it states the file is open.

    I look forward to hearing your advice.

    all the best,

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Try adding closing the workbook before closing the application object:

    appexcel.Workbooks.Save
    appexcel.Workbooks.Close
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32
    Quote Originally Posted by Minty View Post
    Try adding closing the workbook before closing the application object:

    appexcel.Workbooks.Save
    appexcel.Workbooks.Close

    Thanks for the reply, I get a runtime error 438 - object doesn't support this property or method (referring to the safe function)

    If I remove this and just keep app.excel.workbooks.Close, it runs fine first time, but when I run it again I get a far more disconcerting error message via Microsoft .NET Framework:

    Click image for larger version. 

Name:	Screenshot 2022-11-09 164334.png 
Views:	10 
Size:	14.2 KB 
ID:	49065



    Not sure why it doesn't like .save or why I'm still getting errors.

    thanks,

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It was air code(Off the top of my head) but I'm sure that's the correct reference.
    I normally set a reference to the workbook as well as the worksheet as it saves typing:

    Code:
    Dim xlApp   As Object
    Dim xlWb    As Object
    Dim xlWS    As Object
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(sFile)
    Set xlWS = xlWb.Worksheets(sSheet)
    
    'Do some clever Excel stuff in here
    '
    
    
    ' Now Close everything and save
    
    xlWB.Save   ' OR xlWb.SaveAs (sNewFileName)
    xlWb.Close
    xlApp.Quit
    Set xlApp = Nothing
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32
    Quote Originally Posted by Minty View Post
    It was air code(Off the top of my head) but I'm sure that's the correct reference.
    I normally set a reference to the workbook as well as the worksheet as it saves typing:

    Code:
    Dim xlApp   As Object
    Dim xlWb    As Object
    Dim xlWS    As Object
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(sFile)
    Set xlWS = xlWb.sheets(1)
    
    'Do some clever Excel stuff in here
    '
    
    
    ' Now Close everything and save
    
    xlWB.Save   ' OR xlWb.SaveAs (sNewFileName)
    xlWb.Close
    xlApp.Quit
    Set xlApp = Nothing

    Thank you! this now works a treat! I really appreciate it.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-07-2019, 02:57 PM
  2. Replies: 1
    Last Post: 02-03-2019, 09:16 AM
  3. Refresh Data in Excel on User's PC
    By DomSza in forum Access
    Replies: 2
    Last Post: 11-15-2017, 10:24 AM
  4. Excel refresh blocks Access data entry
    By simaonobrega in forum Import/Export Data
    Replies: 4
    Last Post: 06-30-2017, 11:01 AM
  5. Query doest not show in Data Connections in Excel 2007
    By adt29 in forum Import/Export Data
    Replies: 4
    Last Post: 03-11-2013, 10:41 AM

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