Copy Access Form Control Values to Excel File Cells 1

[FabHead]

I have a form called "MaintAction" and on the form is a command button called "Create FSR" (Field Service Report).
With the form opened to the desired record, I want to be able to click "Create FSR" then an Excel file called FRS.xls would open. Then the values of a select group of controls on the form would be copied to certain cells in the excel file.
Any help in the coding of this is greatly appreciated.

Rick

 

[AccessAce]

Here's your start:

Dim xl As Excel.Workbook
Dim sheet As Excel.Worksheet
Dim cell As Excel.Range
Dim N,L As Integer  'Number and Letter cell reference

'Set xl = GetObject("path:\FRS.xls")
Set sheet = xl.Worksheets("Sheet1")

L = A 
N = 2 
Set cell = sheet.Cells(L, N) 'This is the first cell
cell.Value = Form![control_name].Value

This will work as long as you know which field goes to which location on the form. This will also mean that you cannot make changes to your form without altering the code as well. There's a lot you can do with this, just play with it a bit. Test it out and let me know how it goes.

 

[FabHead]

Ace,
That was a very good start....in the right direction. I was able to save the info I wanted to the excel file (cell) I wanted. Just had to figure out how to open excel and make it visible so the user can view and print, which I believe I can get working very soon.
I'll post my final code here or yell if I need more help.

Thanks for the help!

 

[FabHead]

Public Sub ExcelFSR()
Dim FSENameXL as string 'Example data transfer
Dim MyXL As Object ' Variable to hold reference to Microsoft Excel.
Dim sheet As Excel.Worksheet
Dim cell As Excel.Range

'Set the object variable to reference the file you want to see.
Set MyXL = GetObject("C:\Ebara CMMS Files\FSR.xls&quot

'Show Microsoft Excel through its Application property. Then
'show the actual window containing the file using the Windows
'collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
Set sheet = MyXL.Worksheets("FSR&quot

'Insert code here to get your form data, one example shown.
FSENameXL = Me.FSE1.Column(1) 'FSE1 is a combobox, I want the value not the ID.

'As this is a form where a certain controls values are always sent to the same cell, just
'use the cell designation in place of a variable. Example of one tranfer of data.
Set cell = sheet.Cells(39, 5) 'This is the Service Rep cell on FSR.xls
cell.Value = FSENameXL

MyXL.PrintOut ' Print FSR
MyXL.Application.Quit
Set MyXL = Nothing ' Release reference to the application and spreadsheet.

End Sub

 

[AccessAce]

Congradulations. Glad I could help.