saving to word with vba

[scoutt]

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.

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

[bubba]

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

    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.

[scoutt]

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

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

also what happened to recname in the strMsg.

Scoutt

[bubba]

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

[scoutt]

ok this is my save As code. After the changes

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

[bubba]

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

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)

[scoutt]

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

[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

[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

[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

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

[scoutt]

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

Scoutt

[scoutt]

ok I narrowed it down to this line

'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