Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.word.programming,microsoft.public.word.vba.general
|
|||
|
|||
Automating Excel from Word - How to Sort a Range of Cells
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.word.programming,microsoft.public.word.vba.general
|
|||
|
|||
Automating Excel from Word - How to Sort a Range of Cells
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.word.programming,microsoft.public.word.vba.general
|
|||
|
|||
Automating Excel from Word - How to Sort a Range of Cells
' 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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.word.programming,microsoft.public.word.vba.general
|
|||
|
|||
Automating Excel from Word - How to Sort a Range of Cells
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.word.programming,microsoft.public.word.vba.general
|
|||
|
|||
Automating Excel from Word - How to Sort a Range of Cells
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.word.programming,microsoft.public.word.vba.general
|
|||
|
|||
Automating Excel from Word - How to Sort a Range of Cells
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 ... ' 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 |
#7
Posted to microsoft.public.excel.programming,microsoft.public.word.programming,microsoft.public.word.vba.general
|
|||
|
|||
Automating Excel from Word - How to Sort a Range of Cells
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sums; Automating range changes in non-neighbouring cells | Excel Discussion (Misc queries) | |||
vba code to insert an range of Excel cells into a Word document | Excel Programming | |||
Sort column by second word in cells | Setting up and Configuration of Excel | |||
Automating import of Word tables into Excel | Excel Discussion (Misc queries) | |||
Automating Word from Excel | Excel Programming |