Hi m (what is your name?)
yes, you can put data into an Excel template.
here is some "shell" code I use when I am going to write a program with
Excel automation...
'~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Excel_Conversation()
On Error GoTo Proc_Err
Dim xlApp As Excel.Application, _
booLeaveOpen As Boolean
'if Excel is already open, use that instance
booLeaveOpen = True
'attempting to use something that is not available
'will generate an error
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo Proc_Err
'If xlApp is defined, then we
'already have a conversation
If TypeName(xlApp) = "Nothing" Then
booLeaveOpen = False
'Excel was not open -- create a new instance
Set xlApp = CreateObject("Excel.Application")
End If
'Do whatever you want
Proc_Exit:
On Error Resume Next
If TypeName(xlApp) <> "Nothing" Then
xlApp.ActiveWorkbook.Close False
If Not booLeaveOpen Then xlApp.Quit
Set xlApp = Nothing
End If
Exit Function
Proc_Err:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " Excel_Conversation"
'comment next line after debugged
Stop: Resume
Resume Proc_Exit
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~
I often use a template to make new workbooks with one sheet ("master"),
that I make copies of, fill and rename. I have found the the formatting
doesn't always stick in Excel, so this syntax has been very handy for
formatting a range...
xlApp.Range(xlSht.Cells(8, 5), xlSht.Cells(mRow + 1, 10)).NumberFormat =
"#,##0"
'~~~~~~~~~~~~~~~~~~~~~~~~
and for putting formulas into Excel instead of calculation results...
xlSht.Cells(mRow, 7).Formula = "=IF(E" & mRow & "=0,0,F" & mRow & "/E" &
mRow & ")"
pSht.Cells(pRow2, 5).Formula = "=SUM(E" & pRow1 & ":E" & pRow2 - 1 & ")"
"p" is my passed parameter notation -- to modularize the code, I often
send a recordset, an Excel object reference, possibly row numbers,
etc... to another routine to do the writing to Excel. That way, it is
easier to add a loop too
'~~~~~~~~~~~~~~~~~~~~~~~~
here's another handy tip...
to launch Excel code from Access
'this is the workbook with the code if it is somewhere else
xlApp.Workbooks.Open mPath & "PROGRAMS.XLS"
'this is the workbook to run code on, or just to open
xlApp.Workbooks.Open mExcelFile
'run Sub in Programs Workbook if applicable
xlApp.Run "PROGRAMS.XLS!ModuleName.SubName"
'~~~~~~~~~~~~~~~~~~~~~~~~
to make a new workbook based on a template...
xlApp.Workbooks.Add _
Template:= _
CurrentProject.Path _
& "\Templates\Filename.xlt"
'~~~~~~~~~~~~~~~~~~~~~~~~
I have some nifty code using ADO and CopyFromRecordset that I could send
if you are interested... don't know if you are on UA much, but there are
a couple guys who are geniuses with Excel and I have picked up some good
stuff...
~~~
I send out the first 3 chapters of a book I am writing on Programming
with VBA to all who request it -- all you have to do is ask
Be sure
to put "VBA chapters" in your subject line so I see your message
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
