Exporting from Access into an Excel template

[fetler]

Hi,

I'm fairly new to Access and I'm currently tasked with building a database. One of the functions I'd like it to have is the ability to export data from a table, query or report (whichever is best) into an Excel spreadsheet. Thing is, the Excel spreadsheet is a template and I want to export the data from Access into a specific part of the spreadsheet. I've no idea how to do this.

For example, I want the following:
ID Number
Last Name
First Name

to export into a certain part of a spreadsheet template so that ID Number exports into cell A7, A8, A9, etc; Last Name into B7, B8, B9, etc; and First Name into C7, C8, C9, etc.

Does that make sense? Is it possible to do such a thing? I'm using Access 2013.

I don't want it to create a new worksheet in Excel. I want it to export into a specific part of an already-created worksheet. I've got a feeling it's not possible, but I'm hopeful one of you knows if it is possible.

Thanks,
Matt

[jonh]

Excel > Data > From Access

[HiTechCoach]

Thing is, the Excel spreadsheet is a template and I want to export the data from Access into a specific part of the spreadsheet

That does not sounds like a template. A template is used to create a new file each time.


I don't want it to create a new worksheet in Excel. I want it to export into a specific part of an already-created worksheet. I've got a feeling it's not possible, but I'm hopeful one of you knows if it is possible.

From that you are definitely not using a template or at least not what Excel terminology defines as template.

The good news is that it is possible to do what you want to do. If I were dong it I would use VBA code and Excel Automation to enter the data into the Workbook.

To get you started check out: Export Data To Excel (http://hitechcoach.com/index.php?option=com_weblinks&view=weblink&id=301:export-data-to-excel&catid=84:access-vba-office-automation&Itemid=20)

[Johann]

I do this sort of thing all the time. My suggestion is to do it like this:

1) Store your Excel templates in a specific folder. These can be read by Access as it opens and populate a combobox. When you select the right template from the combobox it opens and gets populated.

The code below should be inside your Form_Load Sub. It is the code that populates your combobox. Mine is called cStandardReports.


Set f = fs.GetFolder("C:\Program Data\SVDM-2011\Templates\Standard Reports")
Set fc = f.Files
Teller = 0
RySource = ""
For Each f1 In fc
Teller = Teller + 1
If Right(f1.Name, 4) = "xlsm" Then 'only include valid files
'If Right(f1.Name, 4) = "docm" Then 'only include valid files
If Teller > 1 Then
RySource = RySource & "; " & Left(f1.Name, InStr(f1.Name, ".") - 1)
Else
RySource = Left(f1.Name, InStr(f1.Name, ".") - 1)
End If
End If
Next
Me.cStandardReports.RowSource = RySource



2) The rest of your code is best stored within your Excel template. It's quite complicated to set it out here. To enable any sort of manipulation you first need this:


Private Sub Workbook_Activate()
Set objExcel = Excel.Application
Set db = CurrentDb
End Sub

3) Then you can go wild with code like this.............

Set objSheet = objExcel.Sheets("Summary")
Set rs = db.OpenRecordset("SELECT * FROM StArea")
x = 1
y=1
Do
objSheet.Cells(y,x).Value = rs![municipality].Value
objSheet.Cells(y,x+1).Value = rs![suburb].Value
objSheet.Cells(y,x+2).Value = rs![population].Value
y = y + 1
rs.MoveNext
Loop Until rs.EOF

This is just a very simple example.