print from visual basic 6.0 to word or excel

[mixalis21]

hi,

i really need to know in this month a way to take data from a vb form and put then in particular places in a word/excel document which i have already created.

for example, i have a text box named n1, a command button called c1 and my report is called "myrep.doc" or "mydoc.xls". what would be the script in the command button action, so that when i press the button the program will take the value from the text box "n1" and put it in a particular place in one of the two reports.

i really need an answer till the end of this month.

thanks a lot in advance.

[Dököll]

hi,

i really need to know in this month a way to take data from a vb form and put then in particular places in a word/excel document which i have already created.

for example, i have a text box named n1, a command button called c1 and my report is called "myrep.doc" or "mydoc.xls". what would be the script in the command button action, so that when i press the button the program will take the value from the text box "n1" and put it in a particular place in one of the two reports.

i really need an answer till the end of this month.

thanks a lot in advance.

Hey there, mixalis21!

Here is info gathered through link you see here, you can modify to create word doc:

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add


'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit



......


Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

'Create an array with 3 columns and 100 rows
Dim DataArray(1 To 100, 1 To 3) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 1) = "ORD" & Format(r, "0000")
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
Next

'Add headers to the worksheet on row 1
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")

'Transfer the array to the worksheet starting at cell A2
oSheet.Range("A2").Resize(100, 3).Value = DataArray

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit




Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset rs

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit

'Close the connection
rs.Close
conn.Close


......



'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Create the QueryTable
Dim sNWind As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim oQryTable As Object
Set oQryTable = oSheet.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";", oSheet.Range("A1"), "Select * from Orders")
oQryTable.RefreshStyle = xlInsertEntireRows
oQryTable.Refresh False

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
Use the clipboard
The Windows Clipboard can also be used as a mechanism for transferring data to a worksheet. To paste data into multiple cells on a worksheet, you can copy a string where columns are delimited by tab characters and rows are delimited by carriage returns. The following code illustrates how Visual Basic can use its Clipboard object to transfer data to Excel: 'Copy a string to the clipboard
Dim sData As String
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
& "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
& "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
Clipboard.Clear

Clipboard.SetText sData

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add


'Paste the data
oBook.Worksheets(1).Range("A1").Select
oBook.Worksheets(1).Paste

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit





.........



Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sData As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)

'Save the recordset as a tab-delimited file
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
Open "C:\Test.txt" For Output As #1
Print #1, sData
Close #1

'Close the connection
rs.Close
conn.Close

'Open the new text file in Excel
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus



'Create a new instance of Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")

'Open the text file
Set oBook = oExcel.Workbooks.Open("C:\Test.txt")

'Save as Excel workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
oExcel.Quit












'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Joe', 'Thomas')"
conn.Close
Excel 2007
1. In Excel 2007, start a new workbook.
2. Add the following headers to cells A1:B1 of Sheet1:

A1: FirstName B1: LastName
3. Format cell B1 as right-aligned.
4. Select A1:B1.
5. On the Ribbon, click the Formulas tab, and then click Define Name. Type the name MyTable, and then click OK.
6. Save the new workbook as C:\Book1.xlsx, and then quit Excel.
To add records to the MyTable table by using ADO, use code that resembles the following code example. 'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Scott', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Jane', 'Dow')"
conn.Close

Note In this example, Text1 represents a Text Box control on a Visual Basic form: 'Initiate a DDE communication with Excel
Text1.LinkMode = 0
Text1.LinkTopic = "Excel|MyBook.xls"
Text1.LinkItem = "R1C1:R2C3"
Text1.LinkMode = 1

'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls
Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _
"four" & vbTab & "five" & vbTab & "six"
Text1.LinkPoke

'Execute commands to select cell A1 (same as R1C1) and change the font
'format
Text1.LinkExecute "[SELECT(""R1C1"")]"
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"

'Terminate the DDE communication
Text1.LinkMode = 0

Just in case you need to read up about it. I needed to clean it up so you can see some of the code:
http://support.microsoft.com/kb/247412

[bonkbc]

hi!

the code snippets you added were very close to what I'm trying to do...but not quite close enough.

I was wondering if you could pick up where you left off by addressing this issue:

how can we take textbox data from a visual basic 6.0 form and write it to an excel worksheet once a command button is clicked?

I've gotten very close to completing this on my own, here is my code:

Private Sub cmdwrite_Click()

Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet

Set oExcel = New Excel.Application
oExcel.Visible = True

Dim oRng1 As Excel.Range
Dim oRng2 As Excel.Range


Set oWB = oExcel.Workbooks.Add
Set oWS = oWB.Worksheets("Sheet1")

Set oRng1 = oWS.Range("A1")

oRng1.Value = Val(txtwrite.Text)

oWB.SaveAs ("writeit.xls")

Cleanup:
Set oWS = Nothing
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub


Private Sub cmdquit_click()

End

End Sub

the problem I am having, is that the value entered in the textbox is lost somewhere along the way, and the value written to excel is just "0" (the number zero).

what is happening there?? I'm kind of lost, and would appreciate any help at all!!! thank you very much in advance.

-bri