populating excel file from custom form

[Joel Allen]

Hello,

I'm have a word.dot file that I automatically populate using text form
fields from my custom form. I want to do the same with an excel file. I
modified my existing code and created a new excel.xlt file and it doesn't
work.

The very first error I get is on this line:
Set objDoc = GetExcelDoc6(\\tgps8\drawing$\Jobs\Task_Templates\Credit
ApplicationII.xlt)

It says "Object required: 'GetExcelDoc6(...)'"

'***************************************************************************************************************************************************

Option Explicit
Dim m_blnWeOpenedWord
Dim m_blnWordPrintBackground
Dim m_blnWeOpenedExcel
Dim m_blnExcelPrintBackground
Const wdDoNotSaveChanges = 0
Dim ins
Dim pgs
Dim pg
Dim ctls
Dim ctl

Sub CommandButton6_Click()
Dim objDoc
Set objDoc = GetExcelDoc6("\\tgps8\drawing$\Jobs\Task_Templates\Credit
ApplicationII.xlt")
Call FillFields6(objDoc)
objDoc.Application.Options.PrintBackground = True
'objDoc.PrintOut
'objDoc.Close wdDoNotSaveChanges
Call RestoreExcel6
Set objDoc = Nothing
End Sub

Sub FillFields6(objDoc)

On Error Resume Next

Dim colFields
Set colFields = objDoc.FormFields

Worksheets("Sheet1").Cells(6, 1).Value = 10

Set colFields = Nothing
End Sub


Private Function GetExcelDoc6(strTemplatePath)
Dim objExcel
On Error Resume Next
m_blnWeOpenedExcel = False
Set objExcel = GetObject(, "Excel.Application")
If objExcel Is Nothing Then
Set objExcel = CreateObject("Excel.Application")
m_blnWeOpenedExcel = True
End If
m_blnExcelPrintBackground = _
objExcel.Options.PrintBackground
If strTemplatePath = "" Then
strTemplatePath = "\\tgps8\drawing$\Jobs\Task_Templates\Normal.dot"
End If
Set GetExcelDoc6 = objExcel.Documents.Add(strTemplatePath)
Set objExcel = Nothing
End Function

Sub RestoreExcel6()
Dim objExcel
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
objExcel.Options.PrintBackground = _
m_blnExcelPrintBackground
If m_blnWeOpenedExcel Then
objExcel.Quit
Else
objExcel.Visible = True
End If
Set objExcel = Nothing
End Sub

'***************************************************************************************************************************************************

Thank you,
-Joel

 

[Sue Mosher [MVP-Outlook]]

That error indicates that the GetExcelDoc6 function is not returning a workbook object. If you stepped through the code and looked in the object browser, I suspect you'd find the real problem is with this statement:

objExcel.Documents.Add(strTemplatePath)

The Excel Application object has no Documents collection. Instead, it has a Workbooks collection. You must use objects, properties, and methods appropriate to the application. You can't just assume that what will work in Word will work in Excel. Let the object browser be your guide.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers