Linking or Exporting Excel Cells Automaticly From A Table

[Gunnar Bozeman]

Populating Excel Cells Automaticly From A Table

Hello. I am new. I hope to contribute here as much as I ask questions, but I am on the learning curve. I started an access database from scratch about two years ago, and it has now grown to the point that I am dependent on it to do business efficiently. I am now trying to add bells and whistles.

Part of the database keeps up with changing contract amounts. I want to be able to extract this data (in a table) to mapped cells in an Excel spreadsheet.

How do I go about this? I know enough about VBA to be dangerous. I usually take snip its of code with similar functions and modify them to my needs. I cannot write code from scratch.

I am thinking I need some type module that runs from a button or a macro key and will export these values to mapped cells in Excel. This seems like a one time "static" type of filling in cells.

Is there a way to create a dynamic link so if the excel cell location changes, the mapping can follow. This way, the cells are always up to date with the values in the table.

If anyone can provide code and how to execute it, I would be forever grateful.

I want to do what these people are doing.

Thanks,

Gunnar

 

[llkhoutx]

Litwin's Access 97 Developer's Handbook gives an example of "slamming" (as he calls it) data from Access into Excel. I think that it's in the automation section.

Slamming data into specific cells is a problem. There is no way to send Access data to spreadsheet cells A1, E3, and G24 in one operation. I have slammed data from Access to Excel and then moved it by Excel VBA to specific cells.

 

[jenvandiver]

This should help...

In a blank Excel worksheet, go to Data, then Get External Data, then choose New Database Query. From the database list, choose MS Access Database. Click OK, then select your specific database and click OK. Make sure your database name does not contain any periods (.) other than in the file extension name. For example, if your db is named 123.mdb, that will work fine. But if it's named 1.2.3.mdb, it won't work and you'll need to rename it without the periods.

Choose the columns you want included in Excel from the tables. Click Next and enter any conditions for filtering and sorting data. In the Finish box, make sure to save your query before you return the data to Excel if you want to use it again.

Excel will ask you for the data range, just click on the cell, columns or rows where you want the data to start and it will enter everything in. If you want the data to refresh every time the workbook is opened, click a cell inside the range and go to Data, Get External Data, Data Range Properties. Under Refresh Control, click Refresh Data on File Open.

I had the same problem importing data exactly where I wanted it, so what I did was create one worksheet for the imported data above, then a second worksheet linked to the first, so I could lay the information out where I needed it. For example, Sheet1 would contain data from your Access table. In Sheet2, you would enter formulas in your cells to capture it (='Sheet1'!B2). This formula will automatically enter whatever is in Sheet1, Cell B2 to the new cell in Sheet 2.

 

[Gunnar Bozeman]

Thanks for the replies. This is what I did.

I created a button on a form that started a macro, which uses the TransferSpreadsheet action to export the tables and select queries to the required excel spreadsheet, and then open that spreadsheet.

The cells in my spreadsheet that need to be updated use a VLOOKUP statement to find their value (from the imported access data) based on other criteria in that workbook.

It seems a little cumbersome, but it works, and does not slow anything down.

Gunnar