Macro to open source workbook then close

[drw7162]

I looked but I couldn't find it. I am new to writing macros so if this has been answered I apologize. I have a workbook that has data that can only be updated by opening another source workbook so what I need to do is just that. When the excel file is opened, it automatically opens the source workbook which would let the original excel file be updated and then close out the source workbook and leave the original file open. I was able to find code that opens the source workbook immediately upon opening the workbook but it stays open and doesn't close. I'm guessing there is s simple line of code I am missing? Thanks for any help.

 

[EFANYoutube]

Hi drw
Just put the below in the "ThisWorkbook" macro

Private Sub Workbook_Open()
    Dim wb As Workbook
    Set wb = Application.Workbooks.Add("E:\OneDrive\Documents\Source.xlsx")
    wb.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
    wb.Close True
    
End Sub

 

[drw7162]

Hi,
Thanks for the response. I put in the macro, and I am now getting this error every time i open the workbook

oddly enough it is actually updating the workbook when i make changes to the source but the error still comes up, do you know what the issue is?

 

[EFANYoutube]

If you comment out the below line does it still work

Private Sub Workbook_Open()
    Dim wb As Workbook
    Set wb = Application.Workbooks.Add("E:\OneDrive\Documents\Source.xlsx")
    'wb.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
    wb.Close True
    
End Sub

The error you are getting is usually when you have broken links to other workbooks or if a linked workbook wasn't calculated before closing
But if omitting the line of code for updating links works use that
If your data relys on those links to also be updated then you'l need to click edit links and fix them
there is a way of turning off these dialog boxes but I don;t like to as it leads to incorrect data if your worksheet relys on ot being correct

 

[drw7162]

If you comment out the below line does it still work

Private Sub Workbook_Open()
    Dim wb As Workbook
    Set wb = Application.Workbooks.Add("E:\OneDrive\Documents\Source.xlsx")
    'wb.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
    wb.Close True
   
End Sub

The error you are getting is usually when you have broken links to other workbooks or if a linked workbook wasn't calculated before closing
But if omitting the line of code for updating links works use that
If your data relys on those links to also be updated then you'l need to click edit links and fix them
there is a way of turning off these dialog boxes but I don;t like to as it leads to incorrect data if your worksheet relys on ot being correct

I get it to work once without errors then the next time it's opened same error... don't know if it's got something to do with network drive...

 

[EFANYoutube]

You need to fix your links

 

[EFANYoutube]

you can try this but be careful to make sure its back on if something errors out

=vbaPrivate Sub Workbook_Open()
    Dim wb As Workbook
    Application.DisplayAlerts = False
    Set wb = Application.Workbooks.Add("E:\OneDrive\Documents\Source.xlsx")
    'wb.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
    wb.Close True
    Application.DisplayAlerts = True
End Sub