hyperlink excel cel to access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a running sum total in excel in one particular cell. I want to
hyperlink that cell to a form in access to show the total paid out on one
contract. I have multiple contracts that I want to do this on. Is it possible
to do this? The object is that when the excel total line changes, it will
automatically update and change in the Access form.
 
Do you want

A) to have an unbound textbox on a form that looks up the value of a
particular cell in a particular workbook, or

B) to set things up so that as the form moves from one record to
another, the cell value is looked up using information stored in your
table (e.g. the name and location of the workbook and the cell
reference)?



If (A), one way is:

1) Set up a query that to get the value from the workboook. Create a new
query, don't add any tables to it, and switch to SQL view. Enter a SQL
statement like this, changing the workbook and cell reference as needed:

SELECT F1 FROM
[Excel 8.0;HDR=No;database=D:\Folder\XXX.xls;].[Sheet1$C3:C3]
;

Save the query under a convenient name, let's say qryXLCell.

Then put an expression in the textbox's value property:

=DLOOKUP("F1", "qryXLCell")

and there you are. If you define the cell as a named range, the syntax
is
database=D:\Folder\XXX.xls;].[TheName]


If (B), you need a bit of VBA code.
 
How would I set up the VBA code?

John Nurick said:
Do you want

A) to have an unbound textbox on a form that looks up the value of a
particular cell in a particular workbook, or

B) to set things up so that as the form moves from one record to
another, the cell value is looked up using information stored in your
table (e.g. the name and location of the workbook and the cell
reference)?



If (A), one way is:

1) Set up a query that to get the value from the workboook. Create a new
query, don't add any tables to it, and switch to SQL view. Enter a SQL
statement like this, changing the workbook and cell reference as needed:

SELECT F1 FROM
[Excel 8.0;HDR=No;database=D:\Folder\XXX.xls;].[Sheet1$C3:C3]
;

Save the query under a convenient name, let's say qryXLCell.

Then put an expression in the textbox's value property:

=DLOOKUP("F1", "qryXLCell")

and there you are. If you define the cell as a named range, the syntax
is
database=D:\Folder\XXX.xls;].[TheName]


If (B), you need a bit of VBA code.


I have a running sum total in excel in one particular cell. I want to
hyperlink that cell to a form in access to show the total paid out on one
contract. I have multiple contracts that I want to do this on. Is it possible
to do this? The object is that when the excel total line changes, it will
automatically update and change in the Access form.
Click to expand...
Click to expand...
 
How much experience do you have of programming in general and Access VBA
in particular?

How would I set up the VBA code?

John Nurick said:
Do you want

A) to have an unbound textbox on a form that looks up the value of a
particular cell in a particular workbook, or

B) to set things up so that as the form moves from one record to
another, the cell value is looked up using information stored in your
table (e.g. the name and location of the workbook and the cell
reference)?



If (A), one way is:

1) Set up a query that to get the value from the workboook. Create a new
query, don't add any tables to it, and switch to SQL view. Enter a SQL
statement like this, changing the workbook and cell reference as needed:

SELECT F1 FROM
[Excel 8.0;HDR=No;database=D:\Folder\XXX.xls;].[Sheet1$C3:C3]
;

Save the query under a convenient name, let's say qryXLCell.

Then put an expression in the textbox's value property:

=DLOOKUP("F1", "qryXLCell")

and there you are. If you define the cell as a named range, the syntax
is
database=D:\Folder\XXX.xls;].[TheName]


If (B), you need a bit of VBA code.


I have a running sum total in excel in one particular cell. I want to
hyperlink that cell to a form in access to show the total paid out on one
contract. I have multiple contracts that I want to do this on. Is it possible
to do this? The object is that when the excel total line changes, it will
automatically update and change in the Access form.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top