Export specific Data from Access to specific cells in Excel VBA

[keusch]

The issue: would like to populate specific data in Excel directly from Access.
Currently, the process is data is transfered from Access to create a new worksheet "B" in the existing spreadsheet containing worksheet "A", run VB code that loops through all the data to match Name in worksheet "A" with Name in worksheet "B", then populate the new data in Field3 in the existing worksheet "A" from worksheet "B" Field26, then deletes new worksheet "B" and exits the excel spreadheet. This works fine but is there a way to do this directly from Access without copying the worksheet to Excel 1st?
I'm a novice coder so any hints would be greatly appreciated.
Thanks
---

 

[kjv1611]

What version of Office are you using? 2003/2007/other?

What you need to look at using is MS Query within Excel. You can use it to query records from one spreadsheet to the other. So, you could still export the one full table/query from Access to one worksheet in Excel, and then use MS Query in Excel to grab the necessary data (pretty much like a query in Access) and feed it to where it needs to go in Worksheet B.

Or actually.... this would be even better, don't know why I didn't think about it when first answering..

You could use MS Query from within Excel to return just the data you need from the Access database. That way, you'll only send what data is necessary between the applications. So doing it this way should give you the best overall performance as well.

Use Microsoft Query to retrieve external data

MS Query Wizard Uses for Excel and Access

 

[jebry]

Hi!

Here is some code to get you started:

Dim ExcelBook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet

Set ExcelBook = Excel.Workbooks.Open(Path to your Excel file)

Set ExcelSheet = ExcelBook.Worksheets("Worksheet Name")

ExcelSheet.Range(YourCell such as "A1").FormulaR1C1 = Data from Access
etc.

Then, when you are done:

Set ExcelSheet = Nothing
ExcelBook.Close
Set ExcelBook = Nothing

The code will be more concise and readable if you can manage to use loops to advance your columns and rows and find your Access data.

One more hint: If you want to do something to an Excel book from Access, record it as a macro in Excel and look at the code it writes. Very often the same code, or very nearly the same, will be used in Access.

hth

Jeff Bridgham
Purdue University
Graduate School
Data Analyst