Export data to Excel Template

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

I have a continuous form in Access and I want the user to be able to export
data for the current record to Excel. I need to use an existing Excel
template which contains formulas and column headings, so I just need the data
transferring from Access to specific cells in Excel. There will only be one
records transferred and I want the Excel filename to include the name of the
dealer.

Do I need to use VBA automation to achieve this?

Thanks in advance for any help.
Sue
 
hi Sue,
I have a continuous form in Access and I want the user to be able to export
data for the current record to Excel. I need to use an existing Excel
template which contains formulas and column headings, so I just need the data
transferring from Access to specific cells in Excel.
Click to expand...
Also in Excel it is a good practice to use data sheets and calculation
sheets. This would make your task easy.
There will only be one
records transferred and I want the Excel filename to include the name of the
dealer.
Do I need to use VBA automation to achieve this?
Click to expand...
Yes, because it is fastest way to put your values in your specific cells.


mfG
--> stefan <--
 
hi Sue,
Can you give me any help to achieve this?
Click to expand...
You need a reference to the Microsoft Excel library.

Public Sub ExcelExport()

On Local Error GoTo LocalError

Dim objExcel As Excel.Application
Dim rs As ADODB.Recordset

Dim ExportFile As String

ExportFile = Environ("USERPROFILE") & "\MyExcel.xls"

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM TableOrView", _
CurrentProject.Connection, _
adOpenForwardOnly, adLockOptimistic

Set objExcel = New Excel.Application

objExcel.Workbooks.Open ExportFile

objExcel.Range("C2").FormulaR1C1 = rs![Field]


objExcel.Visible = True
objExcel.ActiveWorkbook.Save

rs.Close

Set rs = Nothing
Set objExcel = Nothing

End Sub


mfG
--> stefan <--
 
Thanks a bunch, will try this. Am I correct in thinking this opens an
existing excel file and saves the data to that file? In which case I will
need to perform Save As command as this blank template will be used time and
time again.

Thanks in advance for any help.
Sue


Stefan Hoffmann said:
hi Sue,
Can you give me any help to achieve this?
Click to expand...
You need a reference to the Microsoft Excel library.

Public Sub ExcelExport()

On Local Error GoTo LocalError

Dim objExcel As Excel.Application
Dim rs As ADODB.Recordset

Dim ExportFile As String

ExportFile = Environ("USERPROFILE") & "\MyExcel.xls"

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM TableOrView", _
CurrentProject.Connection, _
adOpenForwardOnly, adLockOptimistic

Set objExcel = New Excel.Application

objExcel.Workbooks.Open ExportFile

objExcel.Range("C2").FormulaR1C1 = rs![Field]


objExcel.Visible = True
objExcel.ActiveWorkbook.Save

rs.Close

Set rs = Nothing
Set objExcel = Nothing

End Sub


mfG
--> stefan <--
Click to expand...
 
hi Sue,
Thanks a bunch, will try this. Am I correct in thinking this opens an
existing excel file and saves the data to that file? In which case I will
need to perform Save As command as this blank template will be used time and
time again.
Click to expand...
That is correct.

mfG
--> stefan <--
 
Back
Top