Passing data from access to excel

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

Is there a way to open an excel file from disk from within access via code
and then pass some data from access into excel cells? The excel disk file is
a template so I only want to open a copy of it every time.

Thanks

Regards
 
here's some code i use to open an Excel file that i use as a template, put
data into specific cells, print the worksheet, and then close Excel without
saving:

Private Sub isTestFormSetup()

On Error GoTo OpenErr

Dim MyXL As Object

Set MyXL = GetObject("put filepath of xls file here")
' recommend you read up on GetObject in VBA Help.

MyXL.Application.Visible = True
MyXL.Application.WindowState = 3
MyXL.Parent.Windows(1).Visible = True
MyXL.Parent.ActiveWindow.WindowState = 2

MyXL.WorkSheets("put worksheet index here")).Range("put cell address
here").Value = "some value"
' the above goes all on one line

MyXL.WorkSheets("put worksheet index here")).Activate
MyXL.ActiveSheet.PrintOut
MyXL.Application.DisplayAlerts = False
MyXL.Application.Quit

Me.Repaint
DoCmd.SelectObject acForm, Me.Name

OpenEnd:
Set MyXL = Nothing
Exit Sub

OpenErr:
Select Case err.Number
Case 1004 ' can't write to protected cell
Resume Next
case else
Msgbox err.Number & " " & err.Description
Resume OpenEnd
End Select

End Sub

hth
 
dear friend,
on an click event of a command button on a form
i would like to know about some code to export some selected data from many
querys to one excel worksheet and do additional calculations on that
worksheet .
(a) after transfering the data how to access the said excel file from inside
the database please advise
 
Back
Top