[AccessD] Excel Object - Can You Tell What Row Or Column Is Clicked

Rocky Smolin rockysmolin at bchacc.com
Fri Jun 24 10:17:50 CDT 2016


Paul:

I did an application years ago which required extensive manipulation of
Excel spreadsheets.  I discovered, as you probably know, that anything you
can do in Excel you can do to Excel from Access.  The automation is
terrific.  And I did it both ways - pushing data from the back end into a
spreadsheet template so the accountant could massage the numbers, then using
VBA behind Excel to push the data back into the Access back end.

I think what you might need in this case is some VBA behind your Excel to
tell your Access app when the user selects a row.  And pushes that answer
back into your app and also triggers an event so you wknow when the user did
that.

OTOH, an easier solution, though more work, would be to import the
spreadsheet into a temp front end table and present that to the user in a
form that maybe mimics a spreadsheet.  Easier to trap their actions that
way.

BTW, I never did really learn how to automate Excel from Access - I just
used the macro recorder in Excel, cribbed out the code the recorder created
and used that in my Access app. 

HTH

Rocky


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Paul Hartland
Sent: Friday, June 24, 2016 6:45 AM
To: Access List
Subject: [AccessD] Excel Object - Can You Tell What Row Or Column Is Clicked

To all,

A little background...I was given a task where an Excel sheet has upto 300
different branches on it and about 6 sheets....The branch name is in each
sheet and can be in there multiple times.  So I wrote a little VBA module
that looked at the first sheet, gets a unique list of branch names, then one
by one filters the six excel sheets by the branch name....ends up making a
workbook with six sheets for each branch with only that branch name on.....

But now I want to try and make a little front-end application, where the
user is given an open file dialog to select the Excel sheet, then the user
is presented with each of the six sheets in turn and asked to click a row
and/or a column and the row number and/or column letter are stored in
Access.....Is this possible, I have never done anything like that before, I
have played with Excel objects etc, but nothing where the user selects a row
from an Excel sheet and the value is passed back into Access, and I can't
really seem to find anything on the internet...

Thanks in advance for any help on this.



--
Paul Hartland
paul.hartland at googlemail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list