Export one field from access to specific cell in excel

I want to place a name from a table/query to a specific cell from access. I have a check sheet with lots of data in other cells that at the top has the persons name at the top. I would like a button that can be clicked that will open THAT file and insert the desired name into it via VBA. I’m aware on how to do this with VBA to a word doc with bookmarks, but now sure how to define the ‘bookmark’ for a cell in excel.

This is what I have so far.

Private Sub Cmdopentarget1_Click()
Dim xl As Excel.Application
Dim wbTarget1 As Workbook
Dim qdfquerytest As QueryDef
Dim rsquerytest As Recordset
Set qdfquerytest = CurrentDb.QueryDefs("query1")
Set qdfsecondquery = CurrentDb.QueryDefs("query2")
Set rsquerytest = qdfquerytest.OpenRecordset()
Set rssecondquery = qdfsecondquery.OpenRecordset()
Set xl = CreateObject("Excel.application")
xl.Visible = True
Set wbTarget1 = xl.Workbooks.Open("C:UsersOwnerDropboxTarget1.xlsx")
'Set wbTarget1 = xl.Workbooks.Open("C:UsersDennisDropboxTarget1.xlsx")
'wbTarget1.Worksheets("Sheet1").Cells(8, 1).CopyFromRecordset rsquerytest
wbTarget1.Worksheets("Sheet1").Range("A1").CopyFromRecordset rsquerytest
wbTarget1.Worksheets("Sheet1").Range("C5").CopyFromRecordset rssecondquery

It works, but the query I have returns TWO fields, Fullname and the position, I need to only have the FullName in the excel cell.

Answer

You can use

[variable or object] = rsquerytest(0).Value

to pull the value from a record. The recordset starts at position 0. If you wanted the second column, you would use rsquerytest(1).Value.

source

Advertisement

Answer