Need help putting single Access VBA value into Excel cell

[NateofMT]

Ok, so here is what I have going on: I have a linked excel workbook with several sheets. I read and read last night and figured out how to reference the excel cells for use with VBA in Access.

Now what I need to do is take a calculated value (a variable) from Access VBA and put it back into an existing Excel worksheet. I read through this site (601 topics) and my thick head just didn't grasp what I needed to do. I read how to import entire tables into Excel, but I only need one cell and want to use the link.

The name of the workbook is BobDataSheet, the name of the worksheet is Transfers and cell I want to write to is B2 (in the picture below in red color). The name of my linked table in Access is also called Transfers.

BobDateSheet.xls
	A	B	C	D	E
1	Title	Value
2	DaysTotal
3
Transfers

The reason I need to copy it back to Excel is that this number gets ran with the Excel Workday function to include holidays and etc that are already functioning under another worksheet. Oh, the write needs to be part of a function I have written in Access (snippet below):

If Me.Need_Repairs.Value = True Then
increase = Me.Repair_Time_Tbox.Value
totaldays = totaldays + increase
End If
increase = 0
'Me.Transfer_Tbox.Value = totaldays
'can set a hidden control the value of total days
' Need to send totaldays to Excel Sheet right here

End Function

Will someone point me in the right direction? This is a whole new area for me. Thanks.

 

[Cbrine]

Re: Need help putting single Access VBA value into Excel cel

This should get you on the right path. Fill the correct FileName value and update the range value for the correct cell and VBA variable.

Dim XL As Excel.Application

'Build reference to Excel Objects and open sheet
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open (FullPath)
XL.Visible = False

FileName ="C:\test\test.xls"

XL.DisplayAlerts = False

Set WB = XL.Workbooks(FileName)
Set WS = WB.Worksheets(XL.ActiveSheet.Name)

ws.range("A2").value = YourValue

wb.close true
xl.quit

 

[mdmilner]

Re: Need help putting single Access VBA value into Excel cel

I caught that you'd linked the spreadsheet to Access with the linked table name of 'Transfers'

One of the direct benefits of this is you can write values to the "Access Table" and have it show up on the spreadsheet.

Questions I have are how you are organizing this sheet.
It appears you're using it as a template to store default values.

Here's how you might access the linked table field.

Public Function SetDefaults(MyDefaults As String, strEntry As String, strMod As Integer)
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Select * from Transfers", dbOpenDynaset)

With rs
  .FindFirst "Title='" & MyDefaults & "'"
  If !Title = MyDefaults Then
      .Edit
      .Fields(strMod).Value = strEntry
      .Update
    Else         'If can not find it, create a new one
      .AddNew
      .Fields(0).Value = MyDefaults
      .Fields(strMod).Value = strEntry
      .Update
  End If
End With

Set rs = Nothing
Set dbs = Nothing
End Function

Now, this is actually an example of how to make a function call which is probably a little more complex than you'd intended to get, but, really, function calls are easy. What the above does is access three variables which are, in order, 1) What is the name of the field value to look for 2) What is the value to add to the table 3) What is the column number to add it in.

What you'll end up adding to your code is this line (right where you put your 'write to excel' comment.

Call SetDefaults("DaysTotal", totaldays, 1)

What the routine does is:
Looks in the first column for a matching value of "DaysTotal" and adds the value of 50 to column 1 (columns start at 0 so the 2nd column is column 1)

Now, what are the pitfalls of this method.
First, the above routine is designed to do a bit more than merely adding a value to the 2nd column. It's setup to Add the row value if it's missing. Also, it's not designed to add things to the 2nd row (record) it's just designed to look for a row with the right name.

So - if you can count on there being only one row with that name, and it being in the right place every time, you're set. If not, you have to use slightly more complex logic to make sure it adds things in the right place.

Mike

 

[NateofMT]

Re: Need help putting single Access VBA value into Excel cel

Thank you for your help in this - both solutions gave me insight and solved my problem. Again, thanks!