jbn
03-10-2009, 10:06 AM
Okey Dokey, I've gone back to school to get certified :bug: now 10 years ago i could do this blindfolded :whistle:, now i'm old and grey. :dunno
Im taking form data, and sending it to a Word template, and also an excel workbook. The workbook opens and for a split second enters the values, but alas the cell contents disappear :( leaving the workbook blank and open, so i close it, and the Word template has been updated correctly.
So any ideas where i went wrong ? Oh yes, have already referenced excel object. No, "ClientAddres" is not a typo!
Private Sub cmdOk_Click()
Application.ScreenUpdating = False
With ActiveDocument
.Bookmarks("ClientAddres").Range.Text = txtClientAddress.Value
.Bookmarks("ClientName").Range.Text = txtClientName.Value
.Bookmarks("Date").Range.Text = txtDate.Value
.Bookmarks("Discount").Range.Text = txtDiscount.Value
.Bookmarks("Operator").Range.Text = txtOperatorName.Value
.Bookmarks("ProductCost").Range.Text = txtProductCost.Value
.Bookmarks("ProductName").Range.Text = txtProductName.Value
End With
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim NewRow As Integer
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\Foldername\Filename.xls")
' open an existing workbook
' example excel operations
With xlWB.Worksheets("main")
NewRow = Worksheets("main").Range("A1").Value + 1
Worksheets("main").Cells(NewRow, 1).Value = txtDate.Value
Worksheets("main").Cells(NewRow, 2).Value = txtClientName.Value
Worksheets("main").Cells(NewRow, 3).Value = txtOperatorName.Value
End With
xlWB.Close False ' close the workbook without saving
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing
End Sub
Im taking form data, and sending it to a Word template, and also an excel workbook. The workbook opens and for a split second enters the values, but alas the cell contents disappear :( leaving the workbook blank and open, so i close it, and the Word template has been updated correctly.
So any ideas where i went wrong ? Oh yes, have already referenced excel object. No, "ClientAddres" is not a typo!
Private Sub cmdOk_Click()
Application.ScreenUpdating = False
With ActiveDocument
.Bookmarks("ClientAddres").Range.Text = txtClientAddress.Value
.Bookmarks("ClientName").Range.Text = txtClientName.Value
.Bookmarks("Date").Range.Text = txtDate.Value
.Bookmarks("Discount").Range.Text = txtDiscount.Value
.Bookmarks("Operator").Range.Text = txtOperatorName.Value
.Bookmarks("ProductCost").Range.Text = txtProductCost.Value
.Bookmarks("ProductName").Range.Text = txtProductName.Value
End With
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim NewRow As Integer
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\Foldername\Filename.xls")
' open an existing workbook
' example excel operations
With xlWB.Worksheets("main")
NewRow = Worksheets("main").Range("A1").Value + 1
Worksheets("main").Cells(NewRow, 1).Value = txtDate.Value
Worksheets("main").Cells(NewRow, 2).Value = txtClientName.Value
Worksheets("main").Cells(NewRow, 3).Value = txtOperatorName.Value
End With
xlWB.Close False ' close the workbook without saving
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing
End Sub