Populating an Excel document with Access fields

[BajanPOET]

I have created an access database of requisitions and would like to be able to populate a purchase order with information from the tables.

How do I get the lines of the requisition (qty, description, est. price, supplier name) into the Excel document in the correct possitions so that the correct totals are automatically evaluated by the Excel fucntions?

This is the main thing I need. However, I would also like to be able to automate the emailing of the completed purchase order to a person on my intranet.

GOD is in charge, though men may say, "Not so!

 

[PHV]

In Excel:
menu Data -> External data -> Create a query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

I did that; it works fine except that I have a preformatted Excel sheet with predetermined cell lengths. When I do it this way, the name of the field comes up in bold with the entered info underneath it, and the cell length is shortened to the length of the string containing the field name (Description, for example.) I just want to pull the information out of the Access table and display it in the specified places on the Excel worksheet, with no formatting to cell lengths, etc.

Any suggestions on what I could be doing wrong?

GOD is in charge, though men may say, "Not so!

 

[PHV]

Change the relevant properties of the QueryTable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Check out Data/Data Range Parameters

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

AdjustColumnWidth = False
FieldNames = False
FillAdjacentFormulas = True
PreserveFormatting = True
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

Thanks... it worked...

Is there a way that I can open this worksheet by a buttonclick? I get Excel to open to a blank worksheet, but I want it to be able to press a button after I enter a requisition and generate the relevant Purchase Order.

GOD is in charge, though men may say, "Not so!

 

[SkipVought]

BTW,

Once you ADD the querytable, it only has to be REFRESHED (Data/Refresh) to retrieve a fresh resultset.

Also, it can be configured to refresh when you OPEN the workbook.

So I don't understand yor question.

Skip,
[sub]
[red][/red]
[/sub]

 

[BajanPOET]

I realized that I could refresh and set it to show the refreshed result automatically after I asked the question and fooled around a bit. I was trying to open the workbook automatically by pressing a button in Access which would bring up the Excel document with the refreshed info - but I've found a snippet that helps with that.

Thanks.

PS - is there a way to delete a external query from the workbook so that I can do it from scratch? I moved my Excel file and my Access file to a shared location on my network, but now the references to the database don't work in the query. I wanted to delete the queries and do them from scratch, but nothing I do seems to work without deleting rows from my document.

GOD is in charge, though men may say, "Not so!

 

[PHV]

Change in the debug window the ActiveSheet.QueryTables(1).Connection property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KenPuls]

If you want an alternate way, you can retrieve the data using SQL via an ADO connection. An example here:

http://www.excelguru.ca/node/23

The size of the recordset totally depends on your SQL query. I've modified the method there to create functions to return a value, return entire arrays of data and things in between.

HTH,

Ken Puls, CMA

http://www.excelguru.ca

 

[BajanPOET]

The data entered into the Access table shows up on the Excel document but it keeps adding rows and throwing off my Excel cell references. How do I add the information without it adding extra rows and/or columns to my Excel spreadsheet?


GOD is in charge, though men may say, "Not so!

 

[SkipVought]

That's often what happens from a query -- you get more or less rows of data than the time before, unless the query criteria is designed to return a fixed number of rows.

Skip,
[sub]
[red][/red]
[/sub]

 

[BajanPOET]

So can't I fix it? It's throwing off my calculations. All I want is the information from the query to be copied into a specific cell range without adding new cells to the document.

GOD is in charge, though men may say, "Not so!

 

[SkipVought]

What do you mean by "new Cells"?

For instance, I have a querytable in Sheet1!A1 that returns 10 rows.

Each time I refresh, I get 10 rows of data.

Is your query returning an ever growing number of rows?

Please describe the problem in complete detail. REMEMBER, I can't see what you can, cuz my crystal ball is cloudy today.

Skip,
[sub]
[red][/red]
[/sub]

 

[BajanPOET]

Ok let me be as detailed as possible.

This is what I'm attempting to do:

1 Create an Access database of requisitions and have those requisitions approved by my boss.

- I created the Access database, created some forms, and used code to use Outlook to email a note to my boss telling her that new requisitions need her attention.

2 When a new requisition is approved, my boss generates a Purchase Order by using the external query to pull the data from Access into Excel and then send the completed Purchase Order to the Accounts Department.

- The Excel document is a preformatted document with spaces for the description of the product ordered, the quantity and the estimated cost of each (these are pulled from the query.) The formulas within the Excel spreadsheet then calculate the total cost (qty * unit price) and display the sum of these totals at the bottom.

The purchase order is supposed to be completed in triplicate, so I have the exact format of the form duplicated twice and pull the information to the duplicated forms via cell references: '=A21'etc)

The problem is that somehow when I run the query the information whenever I run the query or refresh, I get the same amount of rows of data, as I should. The problem comes because somehow when it is displaying the data, it adds an extra row so that the cell references are messed up. I get a lot of #REF! errors below the added rows because the query doesn't just add the values to the existing rows, it somehow adds that extra row and my totals can't add up because my formulae are refrencing the wrong cells.

GOD is in charge, though men may say, "Not so!

 

[PHV]

Put your QueryTable in another hidden sheet.
You may then reference the first row's cells in your well formatted Purchase Order.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

That sounds great... I'll try that.

GOD is in charge, though men may say, "Not so!