Text Form Fields

  • Thread starter Thread starter Joel Allen
  • Start date Start date
J

Joel Allen

Hello,

I have a word.dot file that I auto populate from my custom task form. Works
great.

Now I need to do the same with excel. Can someone point me in the right
direction as how to do it with code and how it should be done in excel. I
wasn't able to find
Text Form Fields in excel for some reason?

Thanks for your help,
Joel
 
It really depends on what you are trying to do with Excel, and even then your
question is better suited to an Excel programming newsgroup. In the
meantime, this link should get you started:

How to Reference Cells and Ranges [Excel 2003 VBA Language Reference] --
Excel:
http://msdn.microsoft.com/library/e...encecellsandranges1_HV05201926.asp?frame=true

If you have specific questions about using the Outlook Object Model, feel
free to post your issue in this forum.
 
Thanks Eric. I tried modifying my existing code to work with excel and it
is not working. Can you take a quick look to make sure you don't see any
major problems?:

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


Eric Legault said:
It really depends on what you are trying to do with Excel, and even then
your
question is better suited to an Excel programming newsgroup. In the
meantime, this link should get you started:

How to Reference Cells and Ranges [Excel 2003 VBA Language Reference] --
Excel:
http://msdn.microsoft.com/library/e...encecellsandranges1_HV05201926.asp?frame=true

If you have specific questions about using the Outlook Object Model, feel
free to post your issue in this forum.

--
Eric Legault (Outlook MVP, MCDBA, old school WOSA MCSD, B.A.)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/


Joel Allen said:
Hello,

I have a word.dot file that I auto populate from my custom task form.
Works
great.

Now I need to do the same with excel. Can someone point me in the right
direction as how to do it with code and how it should be done in excel.
I
wasn't able to find
Text Form Fields in excel for some reason?

Thanks for your help,
Joel
Click to expand...
Click to expand...
 
I believe this line should refer to a Workbooks collection:

Set GetExcelDoc6 = objExcel.Documents.Add(strTemplatePath)

Should be:

Set GetExcelDoc6 = objExcel.Workbooks.Add(strTemplatePath)

Some of your other code appears to be referencing Word objects, but you're
working with Excel!

--
Eric Legault (Outlook MVP, MCDBA, old school WOSA MCSD, B.A.)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/


Joel Allen said:
Thanks Eric. I tried modifying my existing code to work with excel and it
is not working. Can you take a quick look to make sure you don't see any
major problems?:

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


Eric Legault said:
It really depends on what you are trying to do with Excel, and even then
your
question is better suited to an Excel programming newsgroup. In the
meantime, this link should get you started:

How to Reference Cells and Ranges [Excel 2003 VBA Language Reference] --
Excel:
http://msdn.microsoft.com/library/e...encecellsandranges1_HV05201926.asp?frame=true

If you have specific questions about using the Outlook Object Model, feel
free to post your issue in this forum.

--
Eric Legault (Outlook MVP, MCDBA, old school WOSA MCSD, B.A.)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/


Joel Allen said:
Hello,

I have a word.dot file that I auto populate from my custom task form.
Works
great.

Now I need to do the same with excel. Can someone point me in the right
direction as how to do it with code and how it should be done in excel.
I
wasn't able to find
Text Form Fields in excel for some reason?

Thanks for your help,
Joel
Click to expand...
Click to expand...
Click to expand...
 
Thanks Eric.

Eric Legault said:
I believe this line should refer to a Workbooks collection:

Set GetExcelDoc6 = objExcel.Documents.Add(strTemplatePath)

Should be:

Set GetExcelDoc6 = objExcel.Workbooks.Add(strTemplatePath)

Some of your other code appears to be referencing Word objects, but you're
working with Excel!

--
Eric Legault (Outlook MVP, MCDBA, old school WOSA MCSD, B.A.)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/


Joel Allen said:
Thanks Eric. I tried modifying my existing code to work with excel and
it
is not working. Can you take a quick look to make sure you don't see any
major problems?:

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


Eric Legault said:
It really depends on what you are trying to do with Excel, and even
then
your
question is better suited to an Excel programming newsgroup. In the
meantime, this link should get you started:

How to Reference Cells and Ranges [Excel 2003 VBA Language
Reference] --
Excel:
http://msdn.microsoft.com/library/e...encecellsandranges1_HV05201926.asp?frame=true

If you have specific questions about using the Outlook Object Model,
feel
free to post your issue in this forum.

--
Eric Legault (Outlook MVP, MCDBA, old school WOSA MCSD, B.A.)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/


:

Hello,

I have a word.dot file that I auto populate from my custom task form.
Works
great.

Now I need to do the same with excel. Can someone point me in the
right
direction as how to do it with code and how it should be done in
excel.
I
wasn't able to find
Text Form Fields in excel for some reason?

Thanks for your help,
Joel
Click to expand...
Click to expand...
Click to expand...
 
Back
Top