saving to word with vba

ok let me try to explain what I've got. I am trying to save to a word document through vba. I have 2 text boxes on my form and I would like to save the 2 text boxes in the word doc. just like there are on the form, right next to each other. I am using this code to save.

Code:

---

Private Sub mnuSaveAs_Click()

Dim filenum As Integer
Dim Ingredients As String
Dim lab As String
Dim recname As String
Dim proc As String
  Dim strMsg As String
  Dim W As New  _
        Word.Application
Ingredients = TxtIngredients.Text
                  lab = Label1.Caption
                  recname = LblName.Caption
                  proc = TxtProcessTest
  mnuSaveAs.Enabled = False

  If TxtProcessTest.Text = ""  _
        Then
  MsgBox "You cannot use a blank TextBox", vbCritical, _
"Entry Error"

 mnuSaveAs.Enabled = True
 Exit Sub
 Else

 strMsg = lab & vbTab & recname & vbCrLf & vbNewLine & Ingredients & vbTab &  _
        vbTab & proc
 End If

 W.Documents.Add
 W.Selection.TypeText (strMsg)
 W.ChangeFileOpenDirectory (App.Path)
 W.ActiveDocument.SaveAs Filename:=recname & ".doc", _
FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", _
ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
        _
SaveNativePictureFormat:=False, SaveFormsData:=False, _
        _
SaveAsAOCELetter:=False

 W.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
 W.Application.Quit
 Set W = Nothing

 strMsg = "The document, " & recname & _
".doc" & vbCrLf
 strMsg = strMsg & "has be saved in the directory, " & _
App.Path & "."
 MsgBox strMsg
 mnuSaveAs.Enabled = True
               
End Sub

'Code improved by vBulletin Tool (Save as...)

---

the lab and recname are fine. the ingredients and proc are the ones I need to align. right now it saves the ingredients fine but it saves proc right underneath the ingredients. is it possible to keep the text box size, the proc is in, the same in the .doc file. right now it is saving it as one long line. like this

ingredient 1
ingredient 2
ingredient 3
proc ----------------------------------
----------------------------------------------------
---------------------------
something like that. I want it to save it like this

ingredient 1 proc--------------------------
ingredient 2 --------------------------------
ingredient 3 ------------------------------

does that make since? also what about keeping the same font size when saving as it is on the form?

Scoutt

Use the split function to break the text from each box
into two arrays, then loop through the arrays, joining the text together:

Code:

---


    Dim a1() As String
    Dim a2() As String
    Dim i As Integer
   
   
    a1 = Split(TxtIngredients.Text, vbCrLf)
   
    a2 = Split(TxtProcessTest.Text, vbCrLf)
   
    For i = LBound(a1) To UBound(a1) - 1
      strMsg = lab & vbTab & a1(i) & vbCrLf & vbNewLine & a2(i) & vbTab & vbTab & proc
 
    Next

---

You can get the font of the text box with
dim myfont as string
myfont = Text1.Font

but I'm not sure how to set it in the word document.

Thanks Bubba for giving it a shot but I got a subscript out of range error on this line

Code:

---

strMsg = lab & vbTab & a1(i) & vbCrLf & vbNewLine & a2(i) & vbTab & vbTab & proc

---

also what happened to recname in the strMsg.

Scoutt

Post your new code if you aren't able to figure it out.

ok this is my save As code. After the changes

Code:

---

Private Sub mnuSaveAs_Click()
Dim a1() As String
Dim a2() As String
Dim I As Integer
Dim filenum As Integer
Dim Ingredients As String
Dim lab As String
Dim recname As String
Dim proc As String
Dim strMsg As String
Dim W As New  _
        Word.Application
        Ingredients = TxtIngredients.Text
        lab = Label1.Caption
        recname = LblName.Caption
        proc = TxtProcessTest
  mnuSaveAs.Enabled = False

  If TxtProcessTest.Text = ""  _
        Then
  MsgBox "Your Process Box is empty.", vbCritical, _
"Entry Error"

 mnuSaveAs.Enabled = True
 Exit Sub
 Else
a1 = Split(TxtIngredients.Text, _
        vbCrLf)
a2 = Split(TxtProcessTest.Text, _
        vbCrLf)

For I = LBound(a1) To UBound( _
        a1) - 1
 'strMsg = lab & vbTab & recname & vbCrLf & vbNewLine & Ingredients
        '& vbTab & vbTab & proc
 strMsg = lab & vbTab & a1(I) & vbCrLf & vbNewLine & a2(I) _
& vbTab & vbTab & proc
 Next
 End If

 W.Documents.Add
 W.Selection.TypeText (strMsg)
 W.ChangeFileOpenDirectory (App.Path)
 W.ActiveDocument.SaveAs Filename:=recname & ".doc", _
FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", _
ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
        _
SaveNativePictureFormat:=False, SaveFormsData:=False, _
        _
SaveAsAOCELetter:=False

 W.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
 W.Application.Quit
 Set W = Nothing

 strMsg = "The document, " & recname & _
".doc" & vbCrLf
 strMsg = strMsg & "has been saved in the directory, " & _
App.Path & "."
 MsgBox strMsg
 mnuSaveAs.Enabled = True
                        FrmResult.SetFocus
End Sub

'Code improved by vBulletin Tool (Save as...)

---

Scoutt

My guess would be that a1 has more elements than a2.

I would handle parsing the data from the arrays in a little different way, then adding it to the string later:

Something LIKE

Code:

---

dim strBuild as String

a1 = Split(TxtIngredients.Text, _
        vbCrLf)
a2 = Split(TxtProcessTest.Text, _
        vbCrLf)

For I = LBound(a1) To UBound(a1) - 1
  If not UBound(a2) Then
    strBuild = strBuild & a1(i) & vbtab & vbtab & a2(i) & vbcrlf
  Else
    strBuild = strBuild & a1(i) & vbcrlf
  End If

Next
'See if there are any elements in a2 that don't correspond to a1
If not Ubound(a2) Then
  For I = lbound(a2) to ubound(a2) - 1
    strBuild = strBuild & vbtab & vbtab & a1(2) & vbcrlf
  Next
End If

---

Then just stick strBuild somewhere in strmsg (it's your data, you know where it should go better than I do)

I still get the same error, subscript out of range.
my txtIngredients is a text box comprised of 3 different elements. it has elements form combo boxes.
TxtIngredients.txt has

combo1 combo2 combo3
combo1 combo2 combo3
etc etc etc

up to 14 different lines.
the TxtProcessText is a regular text box comprized of text written in frmMain, it can be up to about 100 lines.
Does that help in anyway?

Scoutt

Ok I was playing around with it and if I put one line of text in both boxes it saves it without an error, but there is nothing in the word file, Blank. but if i put 2 lines of TxtIngredients in and one line of TxtProcess in gives me the error.

Scoutt

My bad I forgot to change my strMsg to strBuild. it saves just how I want it but when I put more than one line in TxtProcessTest the next line goes right underneath the Ingredients. If I do 2 lines in each box it does give the error again.

Scoutt

ok i am very close. I found out that in my TxtProcessTest box I have to hit enter on every line. if I have the same amount of lines in both boxes it works fine but if I have more lines in the TxtProcessTest box than TxtIngredients box it looks like this

1/2 tsp.vanilla*********mix well
2/3 oz. milk************drink it all yourself
2/3 cup sugar**********wipe off mustache
************************wipe off mustache
************************wipe off mustache
************************wipe off mustache

I had four lines and the fourth line is getting wrote over by the third line. does that make since. if I had 4 lines in each box then this happens

1/2 tsp.vanilla*********mix well
2/3 oz. milk************drink it all yourself
2/3 cup sugar**********wipe off mustache
1/2 cup orange juice*************go puke in toilet
************************wipe off mustache
************************wipe off mustache
************************wipe off mustache

this is my code so far

Code:

---

Dim strBuild As String
Dim a1() As String
Dim a2() As String
Dim I As Integer
Dim lab As String
Dim recname As String
Dim W As New  _
        Word.Application
        lab = Label1.Caption
        recname = LblName.Caption
        mnuSaveAs.Enabled = False

  If TxtProcessTest.Text = "" Then
  MsgBox "Your Process Box is empty.", vbCritical, "Entry Error"

 mnuSaveAs.Enabled = True
 Exit Sub
 Else
 
a1 = Split(TxtIngredients.Text, vbCrLf)
a2 = Split(TxtProcessTest.Text, vbCrLf)

For I = LBound(a1) To UBound(a1) - 1
  If Not UBound(a1) Then
        strBuild = strBuild & a1(I) & vbTab & vbTab & a2(I) & vbCrLf
  Else
        strBuild = strBuild & a1(I) & vbCrLf
  End If
Next
'See if there are any elements
in a2 that don't correspond to a1
If Not UBound(a2) Then
  For I = LBound(a2) To UBound(a2) - 1
        strBuild = strBuild & vbTab & vbTab & vbTab & a2(2) & vbCrLf
 Next
End If

strMsg = lab & vbTab & recname & vbCrLf & vbNewLine & strBuild

W.Documents.Add
 W.Selection.TypeText (strMsg)

'Code improved by vBulletin Tool (Save as...)

---

the "*" are to show how it is aligned, can ignore them. think of them as tabs. How do I stop it from repeating the 3rd line and make it print all of the lines after that? It doesn't matter how many lines I have in the boxes it always repeats the 3rd line. (if there was more than 3 lines)

Scoutt

anybody????????????????

Scoutt

ok I narrowed it down to this line

Code:

---

'See if there are any elements
in a2 that don't correspond to a1
If Not UBound(a2) Then
  For I = LBound(a2) To UBound(a2) - 1
        strBuild = strBuild & vbTab & vbTab & vbTab & a2(2) & vbCrLf
 Next
End If

---

I have played around it so much I think I lost my original specs. does anybody know how I can change thi sline to only print teh next lines instead of printing what ever line is in this array- a2(2)
I have changed th avalues over and over but no luck.

Scoutt