That's not the way to use the code I posted.
I provided a function to find the first empty cell in a table which you seem not to know how to use.
I provided an example of how to call the function, which you haven't understood.
You are writing a macro in Word VBA and yet you also seem to be trying to get data from an excel worksheet without properly opening the Excel Workbook
That's what I get from
Code:
'searches for row with "avg" then selects column E(avg of temperature mean) of that row.
i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
This code
Code:
Dim myfile, wdApp As New Word.Application,
Declares myFile as a VARIANT variable and wdApp as a as Word.Applocation object. Is that what you intended.
In the line
Code:
myfile = Application.GetOpenFilename(, , "Browse for Document")
I don't know where 'GetOpenFilename' comes from as its not a Method of the Word.Application object.
Have a look at this
Code:
Sub CopyAndPaste()
Dim my_file As String
Dim my_average As Long
Dim my_cell_index As Long
Dim my_Doc As Word.Document
my_file = get_filename
my_average = get_excel_average(my_file, "A1:A20")
' The excel bit may be aa red herring
' Now open the word document. We can use the DOcuments method as we are already in word.
' Do you really wanto to open an excel file as w Word document?
'makes the file appear
' wdApp.Visible = True
' Set wDoc = wdApp.Documents.Open(myfile)
Set my_Doc = Documents.Open("filename", True)
'Dim myfile, wdApp As New Word.Application, wDoc As Word.Document
my_Cell_index = find_last_empty_cell_in_range(my_Doc.Tables(8).Range)
' put the average into the first empty cell we find in Table 8
my_Doc.Tables(8).Range.Cells(my_Cell_index).Range.Text = CStr(my_average)
End Sub
Public Function get_filename() As String
Dim my_dialog As Office.FileDialog
'select truck report file
' ChDrive "E:\"
' ChDir "E:\WG\TVAL\"
' myfile = Application.GetOpenFilename(, , "Browse for Document")
Set my_dialog = Application.FileDialog(msoFileDialogFilePicker)
my_dialog.Filters.Clear
If my_dialog.Show Then
get_filename = my_dialog.SelectedItems(1)
Else
Exit Function
End If
End Function
Public Function get_excel_average(this_filename As String, this_cell_range As String) As Long
' Its not clear what you want to do here
' so this code is an educated guess
' We are using the Create object approach because its unlikey you understand enough
' to have added the reference to excel to allow ' Dim my_XLApp as Excel.APplication'
Dim my_XLApp As Object
Dim my_XLWorkbook As Object
Dim my_XLWorksheet As Object
Set my_XLApp = CreateObject("Excel.Application")
my_XLApp.Visible = True
Set my_XLWorkbook = my_XLApp.Workbooks.Open(this_filename)
Set my_XLWorksheet = my_XLWorkbook.worksheets(1)
' Dim i As Integer
'searches for row with "avg" then selects column E(avg of temperature mean) of that row.
' i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
' I suspect that what you wqant is more complicated than this
' as it just gets the average of the cells in A1 to A20
get_excel_average = my_XLWorksheet.worksheetfunction.average(this_cell_range)
End Function
Public Function find_last_empty_cell_in_range(this_range As Word.Range) As Long
' Searches through the cells in the table and returns the index of the first empty cell.
' The input parameter is a word range so the function is generic for a table
' or a selection within a table
'
' This function will work with non uniform tables
' Scanning a table using the range.cell(x,y) method will fail with non-uniforma tables
'
' We test if the length of the text in the cell is 2 or less characters to find an empty cell.
' This is because Cells contain hidden characters which mark the end of the cell
' and end of the text (I think)
Dim my_cell As Word.Cell
Dim my_index As Long
For my_index = 1 To this_range.Cells.Count
With this_range
If Len(.Cells(my_index).Range.Text) <= 2 Then
find_last_empty_cell_in_range = my_index
Exit Function
End If
End With
Next
End Function