[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

[ANN] November Phoenix Ruby Group Meeting

James Britt

11/13/2006 12:57:00 AM

The Phoenix Ruby Users Group will have its November meeting this
Monday, November 13, at 6:15 PM.

The meeting will be at the Axway offices (formerly known as Cyclone Commerce)

See

http://www.rubyaz.org/gr...

for directions

We'll be getting a recap of the recent RubyConf 2006 from
Actual Live Attendees, plus the usual fun discussions on whatever.


---
James Britt

** Get ready for Phoenix BarCamp, December 9 **
** http://barcamp.org/BarC... **
3 Answers

PumaMan

3/13/2009 7:44:00 PM

0

Thank you so much!!!! I got it to do what my goal was for today! I'll be
adding more from what I'm familiar with. I would like to add a dialogbox
that will display the values the user has selected before it gives the option
to save, so I'll work on that too. Here is my code incase you have any other
suggestions:

Private Sub SigCardGenerator_Click()
Call WriteToWordDoc
End Sub
Sub WriteToWordDoc()

Dim WordObject As Object 'Word Object
Dim SigCardTemplateString As String 'Template name
Dim SigCardPathString As String 'Path & Template
Dim AcctNumberString As String '1st Dropdown
Dim AcctTypeString As String '2nd Dropdown
Dim AcctStateString As String '3rd Dropdown

With Sheets("Sheet1")
AcctNumberString = .Range("E1")
AcctTypeString = .Range("E2")
AcctStateString = .Range("E3")
End With

If AcctStateString = "Florida" Then
SigCardTemplateString = "FL W9 Sig.dot"
SigCardPathString = "C:\Documents and Settings\nbk337h\Desktop\Excel
Process Docs\FL W9 Sig.dot"
End If

Set WordObject = CreateObject("Word.Application")

With WordObject
'Create a new Word document based on the template
..Documents.Add Template:=SigCardPathString
..Visible = True 'Can be False
..ActiveDocument.Bookmarks("AccountNumber").Range = AcctNumberString
..ActiveDocument.Bookmarks("AccountType").Range = AcctTypeString
..ActiveDocument.Bookmarks("AccountState").Range = AcctStateString

'.ActiveDocument.PrintOut 'Uncomment this line if required

'Close document without saving
'.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

'Alternative to previous line of code.
..ActiveDocument.Close ' Opens Save Dialog box
..Quit 'Close Word
End With

'Cleanup
Set objWord = Nothing

End Sub


"OssieMac" wrote:

> Hi Elbio,
>
> I did some work on this and thought that I should post what I have done
> because I will not be able to reply again until sometime tomorrow.
>
> It still needs the code to establish which Word Template to use based on the
> selections. Thought that you might be able to do that yourself or maybe
> another post with some explicit instructions on the criteria. (Feel free to
> reply to this post if you can wait for the code.)
>
> Anyway the following Excel VBA code opens a word document with a specified
> template and populates the document at bookmarks.
>
> When you open the Excel VBA editor you need to select Tools - > References.
> Scroll down until you find Microsoft Word nn.0 Object Library.
> Where nn.0 is the version of Office
> 10.0 is Office XP (or 2002)
> 11.0 is Office 2003
> 12.0 is Office 2007
> Check the box and click OK. (Ensure you check the box; don't just select the
> line)
>
> You will see some lines of code commented out that give you options re
> printing and closing the document without saving.
>
> Sub WriteToWordDoc()
>
> Dim objWord As Object 'Word Object
> Dim strTemplateName As String 'Template name
> Dim strPathTemplateName As String 'Path & Template
> Dim strFirstOption As String '1st Dropdown
> Dim strSecondOption As String '2nd Dropdown
> Dim strThirdOption As String '3rd Dropdown
>
> With Sheets("Sheet1")
> strFirstOption = .Range("E1")
> strSecondOption = .Range("F1")
> strThirdOption = .Range("G1")
> End With
>
> 'Need code to replace the following line to select the _
> correct template based on the 3 dropdown selections.
> strTemplateName = "TestTemplate.dot"
>
> 'Assign path and template file name to variable
> strPathTemplateName = CurDir & "\" & strTemplateName
>
> 'Open Word and assign Word Application to a variable
> Set objWord = CreateObject("Word.Application")
>
> With objWord
> 'Create a new Word document based on the template
> .documents.Add template:=strPathTemplateName
> .Visible = True 'Can be False
> .ActiveDocument.Bookmarks("BM_Name").Range = strFirstOption
> .ActiveDocument.Bookmarks("BM_Address").Range = strSecondOption
> .ActiveDocument.Bookmarks("BM_Locality").Range = strThirdOption
>
> '.ActiveDocument.PrintOut 'Uncomment this line if required
>
> 'Close document without saving
> '.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
>
> 'Alternative to previous line of code.
> .ActiveDocument.Close ' Opens Save Dialog box
> .Quit 'Close Word
> End With
>
> 'Cleanup
> Set objWord = Nothing
>
> End Sub
>
>
> --
> Regards,
>
> OssieMac
>
>

OssieMac

3/13/2009 8:52:00 PM

0

I've got a few minutes before I go out today so just a couple of comments.

You haven't changed the object variable from objWord to WordObject in the
clean up. I understand that memory problems can result if these object
variables are not set to nothing when no longer required. As the very first
line in the declarations section of the modules enter Option Explicit and
then when you are finished editing code click Debug -> Compile VBA Project
and it picks up any code errors inclucing undeclared variables.

You can force Excel to automatically insert Option Explicit at the top of
modules in NEW projects /modules if you select Tools - > Options and on the
Editor tab check Require Variable Declaration.

You have included the template name with the path string
SigCardPathString = "C:\Documents and Settings\nbk337h\Desktop\Excel Process
Docs\FL W9 Sig.dot"

I would do it this way by concatenating the path and the variable
SigCardTemplateString :-

SigCardTemplateString = "FL W9 Sig.dot"

SigCardPathString = "C:\Documents and Settings\nbk337h\" & _
"Desktop\Excel Process Docs\" & SigCardTemplateString

I pointed out the above for two reasons. One for concatenating the path with
the filename variable.
The other shows the method of inserting a line break in an otherwise single
line of code when the break is required in the middle of a string enclosed in
double quotes.
Close off the double quotes and use an ampersand and then the space and
underscore for the line break and then open the double quotes on the next
line for the remainder of the string.

You may have this done by the time you get this but the following is a code
sample for a message to user re the selections:-

Dim Response

Response = MsgBox("Account Number = " & AcctNumberString & vbCrLf & _
"Account Type = " & AcctTypeString & vbCrLf & _
"Account State = " & AcctStateString & vbCrLf & _
vbCrLf & "Click OK to confirm or Cancel to exit", _
vbOKCancel, "Selections for word document ")

If Response = vbCancel Then
MsgBox "Processing will terminate"
Exit Sub
End If

--
Regards,

OssieMac

PumaMan

3/15/2009 12:36:00 PM

0

Thanks Ossie, I've made all of the adjustments, but haven't done the dialog
box yet. I've saved the code for when I get there. I'm learning VB on VS
2008, so this is slightly different, but good practice.

I sincerely appreciate your help!

"OssieMac" wrote:

> I've got a few minutes before I go out today so just a couple of comments.
>
> You haven't changed the object variable from objWord to WordObject in the
> clean up. I understand that memory problems can result if these object
> variables are not set to nothing when no longer required. As the very first
> line in the declarations section of the modules enter Option Explicit and
> then when you are finished editing code click Debug -> Compile VBA Project
> and it picks up any code errors inclucing undeclared variables.
>
> You can force Excel to automatically insert Option Explicit at the top of
> modules in NEW projects /modules if you select Tools - > Options and on the
> Editor tab check Require Variable Declaration.
>
> You have included the template name with the path string
> SigCardPathString = "C:\Documents and Settings\nbk337h\Desktop\Excel Process
> Docs\FL W9 Sig.dot"
>
> I would do it this way by concatenating the path and the variable
> SigCardTemplateString :-
>
> SigCardTemplateString = "FL W9 Sig.dot"
>
> SigCardPathString = "C:\Documents and Settings\nbk337h\" & _
> "Desktop\Excel Process Docs\" & SigCardTemplateString
>
> I pointed out the above for two reasons. One for concatenating the path with
> the filename variable.
> The other shows the method of inserting a line break in an otherwise single
> line of code when the break is required in the middle of a string enclosed in
> double quotes.
> Close off the double quotes and use an ampersand and then the space and
> underscore for the line break and then open the double quotes on the next
> line for the remainder of the string.
>
> You may have this done by the time you get this but the following is a code
> sample for a message to user re the selections:-
>
> Dim Response
>
> Response = MsgBox("Account Number = " & AcctNumberString & vbCrLf & _
> "Account Type = " & AcctTypeString & vbCrLf & _
> "Account State = " & AcctStateString & vbCrLf & _
> vbCrLf & "Click OK to confirm or Cancel to exit", _
> vbOKCancel, "Selections for word document ")
>
> If Response = vbCancel Then
> MsgBox "Processing will terminate"
> Exit Sub
> End If
>
> --
> Regards,
>
> OssieMac
>
>