PDA

View Full Version : Word frm to Excel



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

Jan Karel Pieterse
03-10-2009, 10:34 AM
Well, makes sense your changes disappear, you tell Excel to wipe them:

xlWB.Close False ' close the workbook without saving

jbn
03-11-2009, 12:14 AM
Ok so i missed that one :eek: sometimes hard to see the easy stuffups. My problems are that the excel sheet is not accepting input from the form to add a new record, also probably the reason why the the wb doesn't close either when it should. And then again neither does the form close either, even after manually closing the wb.

Any ideas ? Please!

Jan Karel Pieterse
03-11-2009, 01:02 AM
I'd modify this:

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

to:

Dim rNewRow As Object
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")
Set rNewRow = .Range("A" & .rows.count).End(xlUp).Offset(1)
rNewRow.Value = txtDate.Value
rNewRow.Offset(,1).Value = txtClientName.Value
rNewRow.Offset(,2).Value = txtOperatorName.Value
End With
xlWB.Close True ' close the workbook saving changes
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing

jbn
03-11-2009, 06:26 AM
Dim NewRow As Integer
'to
Dim rNewRow As Object


Just changed that today, you must be psychic. Course it took me hours to get that far :banghead:


Dim rNewRow As Object
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")
Set rNewRow = .Range("A" & .rows.count).End(xlUp).Offset(1)
rNewRow.Value = txtDate.Value
rNewRow.Offset(,1).Value = txtClientName.Value
rNewRow.Offset(,2).Value = txtOperatorName.Value
End With
xlWB.Close True ' close the workbook saving changes
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing


:p Much cleaner and a lot smaller :bow: code than i cooked up :clap: Bravo!!

Is there any real significant difference in use .Cells vs .Range in this manner ?

Anyway thanks a lot, your code looks more professional and works fine.

TY
TY
TY

:cloud9:

Kenneth Hobs
03-11-2009, 06:51 AM
I generally Dim variables like rNewRow as an Excel Range.
Dim rNewRow As Excel.Range
I too prefer Range over Cells generally. Cells can also be used like this which is less cryptic sometimes.
Worksheets("main").Cells(NewRow, "A").Value = txtDate.Value

Jan Karel Pieterse
03-11-2009, 06:51 AM
It is a matter of personal preference I guess, I use both cells and range when I feel like it.

Jan Karel Pieterse
03-11-2009, 08:27 AM
Hi Kenneth,

I declared as Object, because the original code uses CreateObject and I therefore assumed late binding.

Kenneth Hobs
03-11-2009, 08:45 AM
Hi Jan. Dim as Object is the best route for late binding only of course.

I am a sort of an eat cake kind of guy. I like to mix both early and late binding sometimes to take advantage of intellisense and the pre-defined variable constants. For those that feel the same, see this example. http://www.vbaexpress.com/forum/showthread.php?p=171977

Here is a funny. In VBE, look for Intellisense in the Help.