create excel file based on Template

[JoeriMJ]

Hi,
I have a lot of data that needs to be displayed, then edited and finally printed. Because it needs to be edited but not saved in the database, i can't use either form or report. I thought the best solution was to create an excel template with static cells for logo, field names, etc... and then using VBA to send a query to a new .xls file based on the template.
I'm a rookie when it comes to anything with Excel, so forgive me my unknowingness
I'm using the following code to create a new .xls file.

Dim AppXls As Excel.Application
Dim ObjWb As Excel.Workbook
Dim ObjWs As Excel.Worksheet

Set AppXls = CreateObject("Excel.Application")
Set ObjWb = AppXls.Workbooks.Add

Set ObjWs = ObjWb.Worksheets.Add
ObjWs.Range("A1").Value = "1"

ObjWb.SaveAs (CurrentProject.path & "\saved PBR\TestCreate.xls")

ObjWb.Close (SaveChanges = False)

This ofcourse is just a test to see if creating a file and writing to it would work. I think I need to set a property to objWb or objWs in order to bind a template to the document.
Any help using excel and VBA this way is appreciated, links to good sites are all welcome ofcourse.

Thanks in advance
Joeri

 

[PWise]

have a look at Docmd.transferSpreadsheet

 

[JoeriMJ]

I think this will only transport the query in its most basic layout. I'm gonna need to write code to put query.field[z] into cell(x,y). That's why there needs to be a template for the layout. I don't think a template can be used with transferSpreadsheet.

Greetz,
Joeri

 

[SkipVought]

Hi,

Probably because I'm more familiar with Excel, but, it seems to me that you cald do this easily from Excel via a query, using Data > Import External Data....

Once the QueryTable is added, it only needs to be refreshed. This can be done automatically, when the workbook opens, to get the latest queried data. (see Data > Import Data > Data Range Parameters)

You can format the sheet any way you like.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[JoeriMJ]

Hi, thanks for the option you provided.
But I need a predefined format and every query needs to be stored in a different .xls file. Adding automatisation to this would require VBA, correct me if i'm wrong. The query contains information about purchase orders so it's necessairy to stock them into seperate files.


Greetz,
Joeri

 

[SkipVought]

Running the query would require NO VBA.

Is there a table of the PO Nbrs you need to run? Running that loop could be done in VBA and a separate workbook saved for each.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[JoeriMJ]

There's a table of PO's, a table of Order items with a reference to the PO, a table of work requests and a table of products. The query consists of fields from these tables. The problem is that the people that will work with this program will want to do everything using Access, meaning they create the purchase order and add the order items. A work requests is automatically generated. This concludes the data part of the problem. Now they want to have a printout. Ofcourse they want this function on a form of Access.

Greetz,
Joeri

 

[PWise]

Have A look At ExcellObject.Range("A1").CopyFromRecordSet(RecordsetName)

 

[SkipVought]

"The problem is that the people that will work with this program will want to do everything using Access..."

So why are we still talking about Excel???

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[JoeriMJ]

we're talking about excel because I can't find a solution in Access. Maybe you guys know a solution for what they want.
This is a format that includes information about the purchase order, about the order product. This isn't a problem for me. The problem is that the form has a breakdownlist of the product. This breakdownlist is stored in another table ofcourse. To show this breakdownlist on the form I used a spreadsheet on my form. This didn't work when I had multiple records having different breakdownlists. The problem here was that information on records where overwritten by the last record.
You might wonder why use a spreadsheet and not a subform for displaying this data. The answer is that they want to do small adjustments to the breakdownlist before they print it. But they don't want to save the adjustments in the breakdownlist itself.
For this reason, I thought it would be simpeler to transport everything on this form to excel.

I hope this is somewhat clear to you.

Greetz,
Joeri

 

[JoeriMJ]

I found a solution. I made a template .xlt. Then opened it in VBA, added data to the worksheet and finally saved it as another file .xls. Anyways thanks for you help all.

Greetz,
Joeri