access linking

  • Thread starter Thread starter ian
  • Start date Start date
I

ian

Hi guys, i was wondering is it possible to link access to excel somehow?
i have an excel file which is 23mb big and i want to reduce it so it doesnt
take so long to calculate values, and the only thing i can think of is to
put all the information i don't need into an access database, but i want to
link to it so i can use it when i need to.
 
You need to maintain your data in Access and perform any calculations on
the data therein. Export the data to excel for reports etc.

To be able to manipulate the excel object model from access you need to
create a reference to it (Tools References in the IDE)

Once you have a reference you can get at Excel objects easily - ie


Sub OpenXLWorkbook(byVal strFileName as string)

Dim objXLApp as excel .application
Dim objXLWb as excel.workbook
 
You need to maintain your data in Access and perform any calculations on
the data therein. Export the data to excel for reports etc.

To be able to manipulate the excel object model from access you need to
create a reference to it (Tools References in the IDE)

Once you have a reference you can get at Excel objects easily - ie

**********Code starts***********************

Sub OpenXLWorkbook(byVal strFileName as string)

Dim objXLApp as excel .application
Dim objXLWb as excel.workbook

set objXLWb = objXLApp.workbooks.open (strFileName)

With objXLW

'manipulate your workbook object in the noemal way

.close true
End With

set objXLApp = nothing
set objXLWb = nothing


*****Code Ends*****************************

Once you get the hang of this you can write some cool custom objects to
create and manipupate excel lists and pivots
 
This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Back
Top