Linking Excel to Access

[Justin01]

I have no idea on how to link the two applications together. I want to write a program in which the user opens Excel and asks certain questions on a specific subject.
My information will be contained in tables in access. Now how would i go about grabbing the required info from Access and depositing it in excel.

 

[Parra]

We'll you can create a report. When the report is being displayed there is an icon that lets you export the report to Word or Excel.

But first you have to create a query, and link the report to the query.

Does this help you?

 

[harvi]

can i create a template in Excel for the data in Access to go into, so it can then be calculated,etc.? If so, how?

 

[DonkeyOte]

you can write SQL in Excel VB quite easily - ie. you can query your Access tables direct from Excel and create a SQL Query to return the data

eg

Set dbs = OpenDatabase("R:currentinfoPODatabaseFINANCE_DATA.mdb")

Set qry = dbs.CreateQueryDef(A, "SELECT TRAN_NUMBER, DATE, NOMINAL_CODE, DETAILS, DEPT_NUMBER, " & CURR & ", TYPE, YYMM FROM DATA WHERE NOMINAL_CODE IN ('0030', '0040', '6201', '6202', '6203', '6204', '6205', '6206', '6207', '6900', '7005', '7006', '7007', '7008', '7009', '7100','7103','7104', '7200','7203','7300','7304','7400','7401','7402','7403','7404','7406','7500','7501','7502','7504','7505','7506','7600','7601','7602','7603','7604','7605','7606','7700','7701','7800','7801','7802','7803','7900','7901','7902','8200','8201','8202','8203','8204','8205','8206','8207') AND DEPT_NUMBER in (" & "'" & DEPT1 & "'" & "," & "'" & DEPT2 & "'" & ")" & " AND YYMM in (" & "'" & Q_MTH1 & "'" & "," & "'" & Q_MTH2 & "'" & "," & "'" & Q_MTH3 & "'" & ")" & "")

Set rst = qry.OpenRecordset()

[a2].CopyFromRecordset rst

dbs.Close


The above example includes some variables set up earlier in the Macro - say DEPT1 = 10 then the syntax for the SQL would be

"'" & DEPT1 & "'"

so its read as '10'

etc...

The resulting data is then returned to the active sheet into range A2. Needless to say the actual Query may be far simpler than the above example - i.e. "SELECT DATA1 FROM TABLE1 WHERE MREXCEL = 'ME'"

NOTE: You will need to reference the DAO 3.6 Library and the Microsoft Access 9.0 (or 10.0 for XP) Library in your VB References.

Hope that helps.