Data Taken from Access to Excel

[danian]

I have a database that works as a sales system. From a table in the database I run a query that calculates the totals for that day.
i.e.

Date……….Dept 1…..Dep2
07/11/05…...£10………£10

What I need is:
1. You click a button
2. It copies the date and finds it in the excel spreadsheet as the sheet will already have a field called date.
3. It will then copy the Dept 1 figure and Dept 2 figure into the spreadsheet where the date matches (in a certain column)

Can this be done?

Kindest Regards

Danian

 

[DJN]

My suggestion would be to do it the other way round. Pull the data in from Access from Excel. You can do this with a Microsoft Query. Have a look at the help files for the use of Microsoft Query.

 

[danian]

This is ok but i need the data to go into certain cells. Microsfy query does not allow this.

 

[Samanalysis]

Why not use VBA?

Why not set up a function within VBA in Excel that opens the database and queries for a particular value based on input parameters?


Sub SetAndReturnDataSourceName()
Dim appOffice As OfficeDataSourceObject

Set appOffice = Application.OfficeDataSourceObject
With appOffice
.Open bstrConnect:="DRIVER=SQL Server;SERVER=ServerName;" & _
"UID=user;PWD=;DATABASE=Northwind", bstrTable:="Employees"

If .DataSource = "" Then
.DataSource = "Northwind"
MsgBox .DataSource
End If
End With
End Sub

Is a VBA example setting up a data source -- after that, it's just a matter of figuring out your query and moving the data into the right cell.