Access To Excel

[bdavis96]

I am trying to take information from an Access Database, put it into an array, open up Excel and put the data into Excel. I have no problem putting the Access data into the array. I would like to use the Workbook_Open procedure to then look at the array made by Access and step through the array and put the data into cells (i.e. Cells(1,1).Value = Array(1,1).Value...using short code to get the point across).

The problem that I am having is allowing Excel access to the array. Should I try making it a global array in the form or module in Access or will I have to pass it as a parameter? Is this the best way to go about passing data from Access to Excel...note: I have an existing Access table that I am pulling data from and an existing Excel file that I am trying to open because it is already formatted with labels that I want used, though doing the labels in runtime wouldn't be a problem if I had to make a new file each time I tried passing the data. Any help in this matter would be much appreciated...sorry for the book long explanation.

Brian

 

[SkipVought]

Hi,
What you need to do is create an Excel object in Access VBA, open the workbook you want and then write the array to a range on a worksheet. Skip,
Skip@theofficeexperts.com

 

[bdavis96]

How do I get Excel to recognize that I created an array in Access? Also, how do I create the Excel object in Access? I have tried a couple of suggestions before but they don't seem to work.

Brian

 

[SkipVought]

Use the CreateObject Function - check Help

You will take the values you put into the array and do with them what you need to in Excel via one or more For...Next loops. Skip,
Skip@theofficeexperts.com

 

[Zathras]

Since this is the Office forum (and not a VBA forum) perhaps you will want to take a look at the non-VBA way:

1. From the Excel menu: Data/Get External Data/Create New Query...

2. Select <New Data Source> and click OK. Then follow the prompts:
Select Microsoft Access Driver (*.mdb) for the driver
Click Connect,
Click Select...
Browse for the database file and click OK.
Click OK.
Select a table to query

3. When you are back to the Choose Data Source, select your new source and click OK. Set up your query. Be sure to click the Save Query... button on the Finish screen. Click the Finish button and enjoy.

You do not need to have Access running to do this.

(This is for Excel 97. Excel 2K may have some differences, but the functionality should be there.)

If you didn't install Microsoft Query, you will be prompted for the CD's to do a quick installation.