To help make this post a little less confusing (hopefully), please refer the following as:
Workbook 1 = A
Workbook 2 = B
Workbook 3 = C

I have a A saved in Sharepoint that uses a lot of VBA code. I want A to open a B from Sharepoint, manipulate cells within B, copy a value from B and paste it into A, then close B.

B, also relies on lookups in C, and requires C being open for B to update its values.

I couldn't work out any code in A to have both B and C open in order for B's values to update.

I have managed to use the following code however by adding additional code to the WorkbookOpen sub of B to open C, with the bonus of not being seen by the user:
Private Sub Button_Click()

Application.ScreenUpdating = False

Dim Filenamepath As String
Dim x As New Excel.Application
Dim w As Workbook

Filename = Sheets("Info").Range("FileNameLink").Value
Set w = x.Workbooks.Open(Filename:=Filenamepath)

' Set variables from initial workbook
' Manipulate sheet in second workbook using w.Sheets("Inputs").value = ....
' Assign value from second workbook to a variable in VBA
'Set value in initial workbook to equal the variable

w.Close savechanges:=false
x.Quit

Set w = Nothing
Set x = Nothing

Application.ScreenUpdating = True

End Sub
However, the issue I'm having is that C leaves a prompt if the user wants the workbook to be saved when closing which I can't get rid of.

Does anyone have a better solution to have both B and C open from A and allow C to update B, whilst being closed without saving?
Or, to allow me to close C without saving also as it is?

Thanks