Word Textboxes - Values from Excel wb - Return dates as 0

[Nintendo]

Hi,

(I have been wondering whether this post should be in the Excel or Word section. I ended here)

I have inserted several textboxes in my Word template, and I am now trying to populate them with values from an Excel sheet. It is really working brilliantly, except for when I try to fetch the highest and lowest dates from two date columns. lblStartDate and lblEndDate only show 0-values in the given text-boxes.

(For simplicity, I have removed all the other (working) textbox lines, but one.)

Suggestions will be appreciated. :yes



Private Sub cmdUpdate_Click()


Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook


Set exWb = objExcel.Workbooks.Open("R:\Export.xlsx")


ThisDocument.lblPrice.Value = WorksheetFunction.Average(exWb.Sheets("Data Dump").Range("P5:P525"))


ThisDocument.lblStartDate.Value = WorksheetFunction.Max(exWb.Sheets("Data Dump").Range("AI5:AI525"))
ThisDocument.lblEndDate.Value = WorksheetFunction.Max(exWb.Sheets("Data Dump").Range("AJ5:AJ525"))


exWb.Close


Set exWb = Nothing


End Sub

[Paul_Hossler]

ThisDocument.lblPrice.Value = objExcel.WorksheetFunction.Average(exWb.Sheets("Data Dump").Range("P5:P525"))


might be easy to try this and see

Paul

[Nintendo]

ThisDocument.lblPrice.Value = objExcel.WorksheetFunction.Average(exWb.Sheets("Data Dump").Range("P5:P525"))


might be easy to try this and see

Paul

Paul,

I am not sure if I get you right, or if I was unclear in my inital post, but the lblPrice is working correctly. I kept it there to show how a working link was looking like. It is lblStartDate and lblEndDate that return "0", instead of lowest and highest date.

Nintendo

[fumei]

I believe Paul added the objExcel because Excel functions should not work without them being properly qualified. Are you saying the Average function IS working? I do not know enough about Excel function to have idea why Max is not working.

You may be right in that it may be better to post in the Excel forum.

[macropod]

Hi Nintendo,

Since the code is being run from Word and the call to WorksheetFunction is unqualified (i.e. you don't specify the application), I'm surprised even the Average function is working. You really should be using something like:

With objExcel.WorksheetFunction
ThisDocument.lblPrice.Value = .Average(exWb.Sheets("Data Dump").Range("P5:P525"))
ThisDocument.lblStartDate.Value = .Max(exWb.Sheets("Data Dump").Range("AI5:AI525"))
ThisDocument.lblEndDate.Value = .Max(exWb.Sheets("Data Dump").Range("AJ5:AJ525"))
End with
Indeed, I'd probably go even further and use something like:

Private Sub cmdUpdate_Click()
Dim wdDoc as Document
Set wdDoc = ThisDocument
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Set exWb = objExcel.Workbooks.Open("R:\Export.xlsx")
Dim xlWkSht as Excel.Worksheet
Set xlWkSht = exWb.Sheets("Data Dump")
With objExcel.WorksheetFunction
wdDoc.lblPrice.Value .Average(xlWkSht.Range("P5:P525"))
wdDoc.lblStartDate.Value = .Max(xlWkSht.Range("AI5:AI525"))
wdDoc.lblEndDate.Value = .Max(xlWkSht.Range("AJ5:AJ525"))
End With
exWb.Close
objExcel.Quit
Set xlWkSht = Nothing: Set exWb = Nothing: Set objExcel = Nothing: Set wdDoc = Nothing
End Sub

[Nintendo]

Wow! I am astonished by the degree of help provided in here.

Paul_Hossler,

I read your post on my cell phone, and did not notice that you had actually handed me the solution in your post. You held the key in front of me and I did not spot it.:doh:. Inserting objexcel in front of the WorksheetFunction.Max and .Min did it. Thank you!

fumei,

Thanks to you for pointing back at the correct solution. And actually, without me understanding why, the WorksheetFunction.Average does work correct, without the objExcel.

macropod,

Now this is where I get embarrassed. Your code looks so tidy and easy to work with. I have learned VBA by primarily by trial and error, and google when the going gets tough. I wish that I someday do write such a structure on my own. Thanks for your elaborate effort in helping me!