Automating Excel from Word - How to Sort a Range of Cells

[Doug Robbins - Word MVP on news.microsoft.com]

I have an application in which I am automating Excel from Word where the
application opens a series of Word documents that are protected for filling
in forms and takes information from the formfields in each document and then
populates cells in an Excel Worksheet that the application creates from an
Excel Template (as well as inserting some of the information into a Word
document). After the Excel spreadsheet has been populated with all of the
required information, it is desired that the range of cells containing that
information be sorted on the data in one of the columns.

The only way that I have been able to do the sort is to use

Set xlrange = tSheet.Range("A11:T" & j - 1)
xlrange.Select
oXL.SendKeys "%a%a"

As the use of SendKeys seems to suffer the same reliability problems in
Excel as it does in Word (though sometimes, it appears that it is necessary
to resort to it), I would like to try and avoid using it.

If I run the following code from Excel itself, on a Worksheet that contains
three rows of data (rows 11, 12 and 13 - Hence j -1 = 13), the sort is
performed:

Dim tsheet As Worksheet
Set tsheet = ActiveWorkbook.ActiveSheet
Dim j As Long
j = 14
tsheet.Sort.SortFields. _
Clear
tsheet.Sort.SortFields. _
Add Key:=Range("A11"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With tsheet.Sort
.SetRange Range("A11:T" & j - 1)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

It does not however work when used in the following code. Does anyone know
how what I should use in the following code to do the sorting.

Dim fname As String
Dim PathToUse As String
Dim oXL As Excel.Application
Dim ETarget As Excel.Workbook
Dim WTarget As Document
Dim Source As Document
Dim fd As FileDialog
Dim drange As Range
Dim strText As String
Dim i As Long, j As Long
Dim tSheet As Excel.Worksheet
Dim ResidentName As String
Dim xlrange As Excel.Range

'If Excel is running, get a handle on it; otherwise start a new instance of
Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
Set oXL = CreateObject("Excel.Application")
End If
'Allow the user to select the folder containing the Word files to be
processed
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.Title = "Select the folder containing the files."
If .Show = -1 Then
PathToUse = .SelectedItems(1) & "\"
Else
End If
End With
Set fd = Nothing
oXL.Visible = True
'Create a new workbook from the LongSheet template
Set ETarget = oXL.Workbooks.Add(ThisDocument.Path & "\LongSheet.xlt")
Set tSheet = ETarget.Sheets(1)
tSheet.Activate
'Create a new Word document from the Daily Report template
Set WTarget = Documents.Add("Daily Report.dot")
If Len(PathToUse) = 0 Then
Exit Sub
End If
fname = Dir$(PathToUse & "*.doc*")
'Set the first row of the spreadsheet into which data is to be inserted
j = 11
'Open each document and extract the data from the formfields to populate the
spreadsheet and the Word document
While fname <> ""
Set Source = Documents.Open(PathToUse & fname)
With Source
ResidentName = .FormFields("ResidentName").Result
ResidentName = Mid(ResidentName, InStr(ResidentName, ",") + 1) & " "
& Left(ResidentName, InStr(ResidentName, ",") - 1)
Set drange = WTarget.Tables(2).Cell(3, 3).Range
drange.End = drange.End - 1
drange.Collapse wdCollapseEnd
drange.InsertAfter .FormFields("MapNumber").Result _
& " " & ResidentName & vbCr
tSheet.Range("A" & j) = .FormFields("MapNumber").Result
tSheet.Range("C" & j) = .FormFields("Location").Result
tSheet.Range("D" & j) = ResidentName
tSheet.Range("E" & j) = .FormFields("Contact").Result
tSheet.Range("F" & j) = ResidentName & vbLf & _
.FormFields("Address1").Result & vbLf & _
.FormFields("Address2").Result
tSheet.Range("G" & j) = .FormFields("Phone").Result
tSheet.Range("H" & j) = .FormFields("ContactDate").Result
tSheet.Range("I" & j) = .FormFields("ContactDate").Result
tSheet.Range("J" & j) = .FormFields("DEPC").Result
j = j + 1
End With
Source.Close wdDoNotSaveChanges
fname = Dir$()
Wend
'Sort the data in the spreadsheet
'This does not sort the worksheet
' tSheet.Sort.SortFields. _
' Clear
' tSheet.Sort.SortFields. _
' Add Key:=Range("A11"), SortOn:=xlSortOnValues, Order:=xlAscending,
_
' DataOption:=xlSortNormal
' With tSheet.Sort
' .SetRange Range("A11:T" & j - 1)
' .Header = xlNo
' .MatchCase = False
' .Orientation = xlTopToBottom
' .SortMethod = xlPinYin
' .Apply
' End With
'This does sort the worksheet
Set xlrange = tSheet.Range("A11:T" & j - 1)
xlrange.Select
oXL.SendKeys "%a%a"
'Sort the information in the Word document
Set drange = WTarget.Tables(2).Cell(3, 3).Range
drange.End = drange.End - 1
drange.Sort ExcludeHeader:=False, FieldNumber:="Paragraphs", _
SortFieldType:=wdSortFieldNumeric, SortOrder:=wdSortOrderAscending

Set drange = Nothing
Set tSheet = Nothing
Set ETarget = Nothing
Set WTarget = Nothing
Set Target = Nothing
Set oXL = Nothing

 

[Joel]

I think from looking at your code that SortFields is a defined
Range?Therefore you need to use Range("SortFields") as the object. Alos why
are you clearring the range before you sort?


tsheet.Sort.Range("SortFields"). _
Clear
tsheet.Sort.Range("SortFields"). _
Add Key:=Range("A11"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With tsheet.Sort
.SetRange Range("A11:T" & j - 1)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


If not then you can use something like this

set SortRange = tsheet.Range("A11:T" & j - 1)
SortRange.sort _
Key1:=Range("A11"), _
Order1:=xlAscending, _
Header:=xlno


You also need to refernce the Excel object in the Word VBA for the code to
work

Word VBA menu - Tools - References - Microsoft Excel 11.0 object Library

 

[Peter T]

' tSheet.Sort.SortFields. _
' Add Key:=Range("A11"), SortOn:=xlSortOnValues, Order:=xlAscending,
_
' DataOption:=xlSortNormal
' With tSheet.Sort
' .SetRange Range("A11:T" & j - 1)

When automating Excel you need to fully qualify objects. In the above those
Range objects are not.
Probably you want tSheet.Range(etc

Don't forget "Range" is both Excel and Word, also need to qualify in
declarations.

Looks like you are using early binding but if not don't use named constants
like xlSortNormal, or declare them yourself. This is in passing, I don't
think an issue with yours.

There may well be other errors but that's what I spotted with a quick skim
through.

There's rarely a need to Select or Activate in Excel, and rarer still to
need SendKeys.

Regards,
Peter T

 

[Doug Robbins - Word MVP on news.microsoft.com]

Thanks, Joel, but neither of those pieces of code cause the data to be
sorted when they are incorporated into my application.

The clearing of the SortFields from the range is only there because the
macro recorder puts it there. I do realise that in my case at least, it
would be redundant because there would never have been a previous sort
operation performed on the spreadsheet.

at various times, I had stripped out all of the redundant bits of the
recorded code and while the resulting code worked when run from within
Excel, I could never get it to work when run from Word with Excel being
automated.

I do of course have a reference to the Excel Object Library.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Joel]

First, I like makeing the object visible so I can see what is happening as
the code is running

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

Second, the clear is in the wrong place because it will remove all the data
the was placed in the worksheet just before the sort is performed.


Third, I debug my code by placing break points and stepping through the code

F9 - Set break point
F8 - Step
F5 - Run until next breakpoint


Fourth, put break point on sort line and with the object visible see if yo
have data. Add a message boxd to make sure the range is defined properly

msgbox Sortfields.Address

 

[Doug Robbins - Word MVP on news.microsoft.com]

Thanks, Peter

Using tsheet.Range() is what was required.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Doug Robbins - Word MVP on news.microsoft.com]

Thanks, Joel.

Peter T has put me on the right track with the need to use tsheet.Range(etc

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com