Hi,

I am doing a project using VBA to generate a monthly report. By clicking a CommandButton4, the code will run to generate the reports.

But when the code runs, it opens and generate the report halfway, a run-time error occurs. It displays the following..

Run-time error "5941":
The requested member of the collection does not exist
(The part "Selection.Rows.HeadingFormat = True". is hightlighted)

I stop the code and attempt to run it again. Now another runtime error 4605 occur.... (The part "Selection.SelectColumn". is hightlighted)

Im running on Win98, using VBA 6.3.I have checked the box in the reference for Microsoft Word 10.0 object library and Excel 10.0 object library.

I paste the whole code to make it easier to understand the problem.
Im very bad at explaining so any question feel free to ask i will try my best to answer. Thx alot !!!!

Code:

VB Code:
  1. Dim AppWord As Word.Application

  2. Dim AppExcel As Excel.Application

  3.  
  4. Private Sub CommandButton4_Click()

  5. Dim tmpText As String

  6. Dim Msg As String, thisMonth As String, thisMonthYr As String

  7. Dim s As Integer, i As Integer, j As Integer

  8.  
  9. 'Process into Arrays

  10.  
  11. Dim file As file

  12. Dim fld As Folder

  13. Dim fso As New FileSystemObject

  14. Dim SummarySize As Integer, DetailSize As Integer

  15. Dim inputFiles_s() As String, inputFiles_d() As String

  16. Dim outputFiles_s() As String, outputFiles_d() As String

  17. Const DEFAULT_FILE_PATH As String = "C:\Sin\MMMYYYY\Source"

  18.  
  19. SummarySize = 0

  20. DetailSize = 0

  21. thisMonth = "_" & Format(DateAdd("m", -1, Date), "mmm") & "_"

  22.  
  23. If fso.FolderExists(DEFAULT_FILE_PATH) Then

  24. Set fld = fso.GetFolder(DEFA

  25. ULT_FILE_PATH)

  26. For Each file In fld.Files

  27.  
  28. If InStr(UCase(file.Name), "MMM") > 0 Then

  29.  
  30. If InStr(UCase(file.Name), "SUMMARY") > 0 Then

  31. If InStr(file, "MMM") > 0 And InStr(file, ".txt") > 0 Then

  32.  
  33. SummarySize = SummarySize + 1

  34. ReDim Preserve inputFiles_s(SummarySize)

  35. ReDim Preserve outputFiles_s(SummarySize)

  36. inputFiles_s(SummarySize) = file

  37. 'MsgBox (file.Name)

  38.  
  39. outputFiles_s(SummarySize) = Replace(outputFiles_s(SummarySize), ".txt", ".doc")

  40. outputFiles_s(SummarySize) = Replace(outputFiles_s(SummarySize), "_MMM_", thisMonth)

  41. 'MsgBox (outputFiles_s(SummarySize))

  42.  
  43. End If

  44. End If

  45.  
  46. If InStr(UCase(file.Name), "DETAIL") > 0 Then

  47. If InStr(file, "_MMM_") > 0 And InStr(file, ".txt") > 0 Then

  48.  
  49. DetailSize = DetailSize + 1

  50. ReDim Preserve inputFiles_d(DetailSize)

  51. ReDim Preserve outputFiles_d(DetailSize)

  52. inputFiles_d(DetailSize) = file

  53. 'MsgBox (file.Name)

  54.  
  55. outputFiles_d(DetailSize) = Replace(inputFiles_d(DetailSize), "_MMM_", thisMonth)

  56. outputFiles_d(DetailSize) = Replace(outputFiles_d(DetailSize), ".txt", ".doc")

  57. 'MsgBox (outputFiles_d(DetailSize))

  58.  
  59. End If

  60. End If

  61. End If

  62. Next file

  63. End If

  64.  
  65.  
  66. 'End process into arrays

  67.  
  68. 'This loop is for the summary Files

  69. For i = 1 To SummarySize

  70. On Error GoTo error

  71. If Not IsEmpty(inputFiles_s(i)) Then

  72. Documents.Open inputFiles_s(i)

  73. With ActiveDocument

  74. .PageSetup.Orientation = wdOrientLandscape

  75. If .Sentences.Count = 1 Then

  76. .Sentences(1).ParagraphFormat.Alignment = wdAlignParagraphCenter

  77. .Range.InsertAfter " " & vbCr

  78. .Range.InsertAfter "Period: " & vbCr

  79. .Range.InsertAfter "Report Date: " & vbCr

  80. .Range.InsertAfter "Device Name,Event Summary,Outages,Total Down Time (Days:Hours:Minutes),Reason" & vbCr

  81. .Range.InsertAfter "N/A,N/A,N/A,N/A"

  82. Set ToConvert = .Range(Start:=.Sentences(4).Start)

  83. ToConvert.ConvertToTable Separator:=wdSeparateByCommas

  84. Else

  85. Set FirstThree = .Range(Start:=.Sentences(1).Start, End:=.Sentences(3).End)

  86. FirstThree.ParagraphFormat.Alignment = wdAlignParagraphCenter

  87. s = .Sentences.Count - 1

  88. Set RemainingText = .Range(Start:=.Sentences(4).Start, End:=.Sentences(s).End)

  89. RemainingText.ConvertToTable Separator:=wdSeparateByCommas

  90. insertHeader

  91. CenterCols

  92. insertFooter

  93. repeatHeader

  94. End If

  95. .SaveAs FileName:=inputFiles_s(i), FileFormat:=wdFormatDocument

  96. .OnTime When:=Now, Name:="CloseDocument"

  97. .Close

  98. End With

  99.  
  100. End If

  101. error:

  102. Next i

  103. 'End of loop for summary files

  104.  
  105. 'This second loop is for the Detailed Files

  106. For j = 1 To DetailSize

  107. If Not IsEmpty(inputFiles_d(j)) Then

  108. Documents.Open inputFiles_d(j)

  109. With ActiveDocument

  110. .PageSetup.Orientation = wdOrientLandscape

  111. If .Sentences.Count = 1 Then

  112. .Sentences(1).ParagraphFormat.Alignment = wdAlignParagraphCenter

  113. .Range.InsertAfter " " & vbCr

  114. .Range.InsertAfter "Period: " & vbCr

  115. .Range.InsertAfter "Report Date: " & vbCr

  116. .Range.InsertAfter "Device Name,Event Details,Polls Missed,DownTime DD:HH:MM,Reason" & vbCr

  117. .Range.InsertAfter "N/A,N/A,N/A,N/A"

  118. Set ToConvert = .Range(Start:=.Sentences(4).Start)

  119. ToConvert.ConvertToTable Separator:=wdSeparateByCommas

  120. Else

  121. Set FirstThree = .Range(Start:=.Sentences(1).Start, End:=.Sentences(3).End)

  122. FirstThree.ParagraphFormat.Alignment = wdAlignParagraphCenter

  123. Set RemainingText = .Range(Start:=.Sentences(4).Start)

  124. RemainingText.ConvertToTable Separator:=wdSeparateByCommas

  125. insertHeader

  126. CenterCols

  127. insertFooter

  128. repeatHeader

  129. End If

  130. .SaveAs FileName:=outputFiles_d(j), FileFormat:=wdFormatDocument

  131. .OnTime When:=Now, Name:="CloseDocument"

  132. .Close

  133. End With

  134. End If

  135. Next j

  136. 'End of loop for Detailed Files

  137. End Sub

  138.  
  139. Private Sub CommandButton5_Click()

  140. End

  141. End Sub

  142.  
  143. Private Sub insertHeader()

  144. Selection.MoveDown Unit:=wdLine, Count:=3, Extend:=wdExtend

  145. Selection.Copy

  146. Selection.Cut

  147. If ActiveWindow.View.SplitSpecial <> wdPaneNone Then

  148. ActiveWindow.Panes(2).Close

  149. End If

  150. If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _

  151. ActivePane.View.Type = wdOutlineView Then

  152. ActiveWindow.ActivePane.View.Type = wdPrintView

  153. End If

  154. ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

  155. Selection.Paste

  156. ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

  157. End Sub

  158. Sub repeatHeader()

  159. Selection.MoveDown Unit:=wdLine, Count:=1

  160. Selection.MoveLeft Unit:=wdCharacter, Count:=36

  161. Selection.MoveRight Unit:=wdCharacter, Count:=21, Extend:=wdExtend

  162. Selection.MoveLeft Unit:=wdCharacter, Count:=3, Extend:=wdExtend

  163. Selection.MoveUp Unit:=wdLine, Count:=2, Extend:=wdExtend

  164. Selection.MoveRight Unit:=wdCharacter, Count:=2, Extend:=wdExtend

  165. Selection.Rows.HeadingFormat = True <------- THIS PART IS HIGHTLIGHTED WHEN I PRESS DEBUG BUTTON (runtime error 5941)

  166. End Sub

  167.  
  168. Sub insertFooter()

  169. If ActiveWindow.View.SplitSpecial <> wdPaneNone Then

  170. ActiveWindow.Panes(2).Close

  171. End If

  172. If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _

  173. ActivePane.View.Type = wdOutlineView Then

  174. ActiveWindow.ActivePane.View.Type = wdPrintView

  175. End If

  176. ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

  177. Selection.MoveDown Unit:=wdLine, Count:=4

  178. NormalTemplate.AutoTextEntries("Page X of Y").Insert Where:=Selection. _

  179. Range

  180. ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

  181. ActiveWindow.ActivePane.VerticalPercentScrolled = 0

  182. End Sub

  183.  
  184. Sub CenterCols()

  185. Selection.MoveDown Unit:=wdLine, Count:=1

  186. Selection.SelectColumn <------- THIS PART IS HIGHTLIGHTED WHEN I PRESS DEBUG BUTTON (runtime error 4605)

  187. Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend

  188. Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter

  189. Selection.MoveUp Unit:=wdLine, Count:=1

  190. End Sub