Share via

Is it possible to populate specific cells in a spreadsheet with specific data from Acces, but NOT as an organized table?

Anonymous
2025-05-17T10:11:28+00:00

My company uses a spreadsheet as a sort of organization chart. Boxes with positions and then the next cell having the person's name who fills that position. But it is not in a simple column row list. Would it be possible to have values in my employee database for their position (section one foreman, section one mechanic, section one chief widget maker, etc...) and then specify on a spreadsheet that this cell is filled by whomever is in the database in that position (like D4 = SectionOneForeman, D5 = SectionOneMechanic, etc...)?

I'm going to be trying to figure out if I can do this on my own, but also figured I'd put the question out on the chance it's flat out impossible and someone can save me a headache. Or, tell me "Sure Rock, that's easy peasy!" and give me a general direction to tunnel :)

Microsoft 365 and Office | Access | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Accepted answer
  1. HansV 460.6K Reputation points MVP Volunteer Moderator
    2025-05-17T10:45:08+00:00

    I'd use Power Query (Data > Get Data > From Database > From Access Database) to import the Access table to a separate sheet,

    You can then use XLOOKUP formulas to retrieve individual data from the table.

    If you wish, you can hide the sheet with the linked table.

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,755 Reputation points Volunteer Moderator
    2025-05-17T11:38:19+00:00

    Using Office automation, you can write data from Access to specific cells in an Excel sheet.

    0 comments
  2. Anonymous
    2025-05-17T12:06:37+00:00

    I'd use Power Query (Data > Get Data > From Database > From Access Database) to import the Access table to a separate sheet,

    You can then use XLOOKUP formulas to retrieve individual data from the table.

    If you wish, you can hide the sheet with the linked table.

    I think that'll work very well! Yeah, much simpler than anything I was trying to spin up in my head. Just import the positions and employees into a properly organized sheet/table and then XLOOKUP all the cells for the positions in the org sheet. I appreciate the help!

    0 comments
  3. Anonymous
    2025-05-17T12:07:44+00:00

    Using Office automation, you can write data from Access to specific cells in an Excel sheet.

    Thank you for the reply. I hadn't heard of Office automation before, and I'll probably look into it at some time. But for now, I think Hans' XLOOKUP suggestion is the way to go.

    0 comments
  4. ScottGem 68,755 Reputation points Volunteer Moderator
    2025-05-17T13:02:49+00:00

    Office Automation is a very powerful VBA tool. it allows you execute VBA code for another VBA compliant application from within a different application. For example: You can execute Word VBA commands from within an Excel VBA module. Excel VBA allows you to assign a value to a specific cell. Therefore you can write VBA code in Access to do the same thing.

    Dim strTitle As String

strTitle = Me.txtTitle 

Set objApp = CreateObject("Excel.Application")

objApp.Visible = True

Set wb = objApp.Workbooks.Open("fileHere.xls", True, False)

wb.Sheets(1).Range("A1").Value = strTitle

    This code snippet will take the value stored in the txtTitle control on the current record of a form and store it cell A1 of the Excel file represented by filehere.xls

    0 comments