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


--
Thanks and Regards

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

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
"Doug Robbins - Word MVP on news.microsof" wrote:

> 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
>
>
> --
> Thanks and Regards
>
> Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
>
>
>

' 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

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" <(E-Mail Removed)> wrote in message
news:8AE0EC0B-CCDC-4864-B9CC-(E-Mail Removed)...
>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
> "Doug Robbins - Word MVP on news.microsof" wrote:
>
>> 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
>>
>>
>> --
>> Thanks and Regards
>>
>> Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
>>
>>
>>

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.microsof" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:8AE0EC0B-CCDC-4864-B9CC-(E-Mail Removed)...
> >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
> > "Doug Robbins - Word MVP on news.microsof" wrote:
> >
> >> 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
> >>
> >>
> >> --
> >> Thanks and Regards
> >>
> >> Doug Robbins - Word MVP, originally posted via msnews.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

"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
>' 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
>
>
>

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

"Joel" <(E-Mail Removed)> wrote in message
news:0C7D283D-B379-419A-8933-(E-Mail Removed)...
> 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.microsof" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:8AE0EC0B-CCDC-4864-B9CC-(E-Mail Removed)...
>> >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
>> > "Doug Robbins - Word MVP on news.microsof" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> --
>> >> Thanks and Regards
>> >>
>> >> Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
>> >>
>> >>
>> >>
>>
>>
>>