Export Form And Subform Data To Excel Form Same Worksheet

[tomtheappraiser]

Hello,

I have a problem I was hoping you might help with. I have a form and subform in Access. This is for information regarding the sale of Apartment buildings. The main form has the information on the sale and buildings type, square footage, etc. The subform has the unit mix. So for example The main form (frmSalesInfo) will have:

SaleID: 100
Address : 1021 Main St.
Size: 1,023
Units: 100

Then the sub form (sbfrmUnitMix) could have multiple records for the different type of units:

Type	Num Units
1 Bedroom	40
2 Bedroom	60

After I've chosen the comparable sales I want to use, I have a select box on frmSalesInfo that I check. Now I export this data to an excel template using a button on the form which executes an export query. I have been just exporting the frmSalesInfo to an import worksheet to which I link the following worksheets: Adjustment Grid. I use this to make adjustments to the sales and come up with a value. Then I have a seperate worksheet for each sale. This has all of the information of the sale in a report format. I then have been manually adding in the unit mix to each sale worksheet. Then I copy and paste each of these worksheet reports into my appraisal report, which is in word format. However, I am starting to deal with sales which have 15 or even 20 different types of units. So, I've been trying to figure out how to add both sets of data as they relate to each sale. Everyone says do: DoCmd.TransferSpreadsheet, but I have limited knowledge of VBA and have no idea how to implement this.
Also since I'm going to be taking to time to learn how to do this, I was wondering if I could also automatically insert rows for the number different types of units. So far example, right now on my current template for each report, in the middle of the page I have a section that says Unit Mix and then I leave three rows to allow for me to enter each type of unit. I'm wondering if I could automatically insert those rows for any number of units I have.

I am attaching an example of the template I'm using. The 1st worksheet is where I import the data (actually it's exported from access to excel). The second has my adjustment grid. The remaining three worksheets are each sale. I just put 3 in there for now, but I can have as many as 10. I had just copied and pasted Sales 2 & 3 from Sale #1 so the links aren't correct (I didn't want to take the time to go through each cell and put the correct reference column in just for a sample). I highlighted in yellow the areas where I need the subform data be added.

Any help you could give me would be much appreciated.

[June7]

You use Excel as opposed to Access report because of the adjustments?

Yes, code can insert rows. TransferSpreadsheet won't be helpful if you need to do post-export manipulation of worksheet from the Access end. Must open the worksheet as an object in VBA.

Otherwise, use code behind the workbook to do the row insertion. The trick will be in telling the code how many rows and then triggering the procedure.

Will the insertion always be at the same position (row)?

[tomtheappraiser]

Thanks for your quick response.
Yes, I use it so that I can make adjustments on the grid. I also use it so I can paste the sales sheets into my appraisal report in Word. I know it seems like a lot of steps, but I'm just kind of learning on the fly how to do this.
Yes, the insertion will always be in the same position.

[June7]

What you want is fairly complicated. What method do you currently use for the export - what is the code behind that button? Post code or provide db. Follow instructions at bottom of my post.

BTW, some of the formula references in the sales columns (sales 2 and up) on AdjustmentGrid are wrong.

[tomtheappraiser]

OK I have attached a very scaled down copy of my database. I removed a lot of tables and forms that weren't necessary to this example in order to shrink it down below 2mb. Hopefully I didn't remove anything that was necessary. I export from the frmSalesInfo. The button that is under the heading "Export To Grid" named "Improved". It exports to a blank excel spread sheet. I then copy that data then open up the template I will use for that specific report and paste it into the Sample spreadsheet I posted above. It seems like a long way to go, but I do that because I never know what my new spreadsheet file will be named. So I just always export it to a generic excel file then copy and paste.

[June7]

The macro you use is basically the equivalent of VBA executing Docmd.OutputTo. Why have embedded macro call MacCompsImp? The MacCompsImp code could be right in the embedded macro. Is MacCompsImp called by other embedded macros? I tested the export and the result doesn't look like the spreadsheet you posted. How do you get the exported spreadsheet to look like that? You said you export to an Excel template but I don't see a template referenced in the macro.

Two links in the last post of this thread you might find interesting http://forums.aspfree.com/microsoft-...el-414974.html

Here is example code to insert blank rows:

Sub InsertRows()
Dim eapp As Excel.Application
Dim ewb As Excel.Workbook
Set eapp = New Excel.Application
Set ewb = eapp.Workbooks.Open("C:\Users\June\Forums\SCMFUtte rAccessSample.xltm")
ewb.Worksheets("Sale #1").Range("32:" & 32 + 4).EntireRow.Insert
eapp.Visible = True
End Sub

For that code to work, need to set a reference to Microsoft Excel 14.0 Object Library in the VBA editor.

[tomtheappraiser]

The way I do it is export the query to a spreadsheet that is named after the query that is always in the same place. Then I copy the data that was exported to the generic spreadsheet and paste it into the specific spreadsheet I use for that report to the import worksheet.

I have a couple of questions about the code, but I can't ask right now. Because I'm at work. I'll post tonight. Thanks again for your help and patience