kiran said:
I have placed ocx in my project i want to
display all records of my access table in
Excel, is it possible to display all records
in Excel if so pls let me know with an
example in Visual Basic
Access allows you to link an Excel spreadsheet as though it were a Table, so
you can easily view your Excel spreadsheet rows in Access. Excel does not
have a corresponding feature, but you can use VBA code in Excel to execute a
Query and do whatever you wish with the results (like populate a cells in
your spreadsheet).
Here's a topic from Excel 2003 Help:
Copy an entire set of data to Microsoft Excel
1.. In the Database window in Microsoft Access, click the table, query,
form, or report you want to use in Excel.
2.. On the Tools menu, point to Office Links, and then click Analyze It
With MS Excel.
Access saves the contents of the table, query, form, or report as a Excel
workbook (.xls) file and opens the file in Excel. The file is saved with the
name of the table, query, or other database object in the current working
folder (working folder: The folder in which your documents appear when you
open or save them. If you select another folder when opening or saving, that
folder becomes the working folder. You can set the startup location of this
folder in most Office applications.).
Note If you have a main form with one or more subforms or a main report
with one or more subreports, Access saves only the data in the main form or
report to the workbook.
And, here's another
Copy selected records to Excel
1.. In Microsoft Access, open the table, query, or form that contains the
records you want to copy.
2.. On the View menu, click Datasheet View.
3.. Select the records you want to copy.
If you want to select specific columns, drag across adjacent column
headings.
4.. Click Copy .
5.. Switch to Excel.
6.. Click in the upper-left corner of the worksheet area where you want
the first field name to appear.
To ensure that the copied records do not replace existing records, make
sure that the worksheet has no date below or to the right of the cell you
click.
7.. Click Paste .
8.. You may need to adjust the row height of data you paste onto your
worksheet. Do one of the following:
a.. Select the rows, click Cells on the Format menu, click the Alignment
tab, and then clear the Wrap text check box.
b.. On the Format menu point to Row, and click AutoFit.
I found these on the first try at searching for "import data from Access" in
Microsoft Excel 2003 Help... fourth item in the returned list, entitled
"Copy Access data into Excel".
FYI, there are thousands, perhaps tens or hundreds of thousands, of OCX
files (aka ActiveX Controls) so knowing that you placed some arbitrary one
_somewhere_ (Access project, Excel project, ???) is not very much help to us
in helping you.
If, indeed, you want to display the contents of Access table(s) in Excel, do
you suppose it might be more appropriate to ask in a newsgroup devoted to
Excel? People there would be likely not only to know how to use VBA, but
also to know the Excel object model, so they might be able to better help
you with an example, how to create it, and how to invoke the code.
Larry Linson
Microsoft Access MVP
