Connecting to Excel From Access

[Mirage]

I've spent most of the day trying to find some help for this but to no avail.

I have a spreadsheet that has user input for cells B1, B2, B3, B4 and B5.

Using formulas that looks up other spreadsheets required values are given in B10, B11 and B12.

I want to set up an access form that inputs form fields into B1-B5 and then takes the values from B10-B12. Prefably without actually opening the spreadsheet.

There's no option to bring everything into the database from the spreadsheet unfortunately.

I'm stuck at:

      Dim RS As ADODB.Recordset
      Set RS = New ADODB.Recordset
      Dim sconn As String

      RS.CursorLocation = adUseClient
      RS.CursorType = adOpenKeyset
      RS.LockType = adLockBatchOptimistic
      

      sconn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & DBPath & "\" & FileName
      RS.Open "SELECT B2 FROM [DATA$]", sconn

 

[KevinCB]

Was going to post about how good the TransferSpreadsheet method is and that you don't need to create connection strings, but then realised you wanted to take it from Access to Excel.

Anyway, found this, hopefully it will help.
This code creates a new instance of a spreadsheet, but would have thought with a bit of tweaking it will use an existing one.

Export Data To Excel