如何将数据从ListView导出到Excel和PDF（VBA）

Question

我在ListView中有数据并希望将这些数据与列标题一起导出到新的Excel文件和PDF文件中。

的LV的定义如下：

'listview: 
    | # | Col1 | Col2 | Col3 | ... 
    ================================== 
    | 1 | Val1 | Val2 | Val3 | ... 

我开始使用此代码：

Dim objExcel As Excel.Application 
Dim objWb As Excel.Workbook 
Dim objWs As Excel.Worksheet 
Dim objRange As Excel.Range 
Dim i%, j% 

On Error GoTo errMsg 

Set objExcel = New Excel.Application 
objExcel.Visible = False 

Set objWb = objExcel.Workbooks.Add 
Set objWs = objWb.Sheets("Sheet1") 

With objWs 
     For i = 1 To Me.lvlist.ListItems.Count 
      .Cells(i, 1) = Me.lvlist.ListItems(i).Text 

      For j = 1 To Me.lvlist.ListItems(i).ListSubItems.Count 
       .Cells(i, j + 1) = Me.lvlist.ListItems(i).SubItems(j) 
      Next j 
     Next i 
End With 

objExcel.Visible = True 
objWs = Nothing 
objWb = Nothing 
objExcel = Nothing 

但我得到一个错误。我怎样才能达到我的意图？谢谢你的帮助。

编辑： 我编辑了上面的代码，现在我得到error 438: Object doesn't support this property or method。并且列标题不会导出为ex​​cel。

Answer 1

我解决我的问题。这是下面的代码，其中出口的columnheaders数据在Excel：

With Me.lvlist 
    For k = 1 To .ColumnHeaders.Count 
     objWs.Cells(1, k) = .ColumnHeaders.item(k).Text 
     objWs.Cells(1, k).Font.Bold = True 
     objWs.Cells(1, k).Font.Size = 12 
     objWs.Cells(1, k).Interior.Color = vbYellow 
    Next 

    For i = 2 To .ListItems.Count + 1 
     objWs.Cells(i, 1) = .ListItems(i - 1).Text 

     For j = 1 To .ListItems(i - 1).ListSubItems.Count 
      objWs.Cells(i, j + 1) = .ListItems(i - 1).SubItems(j) 
     Next 
    Next 
End With 

它非常好:)。但是感谢R3UK的支持和努力！

Answer 2

你接近，但你需要使用特定Item的SubItems的.Count：
Me.lvlist.ListItems(i - 1).SubItems.Count

Dim objExcel As Excel.Application 
Dim objWb As Excel.Workbook 
Dim objWs As Excel.Worksheet 
Dim objRange As Excel.Range 
Dim SubItmCount As Long 
Dim i%, j% 

On Error GoTo errMsg 

Set objExcel = New Excel.Application 
objExcel.Visible = False 

Set objWb = objExcel.Workbooks.Add 
Set objWs = objWb.Sheets("Sheet1") 

With objWs 
     For i = 1 To Me.lvlist.ListItems.Count 
      .Cells(i, 1) = Me.lvlist.ListItems(i - 1).Text 
      SubItmCount = Me.lvlist.ListItems(i - 1).SubItems.Count - 1 '<== argument is not optional .SubItems(..) 
      For j = 1 To SubItmCount 
       .Cells(i, j + 1) = Me.lvlist.ListItems(i - 1).SubItems(j).Text 
      Next 
     Next 
End With 

objExcel.Visible = True 
objWs = Nothing 
objWb = Nothing 
objExcel = Nothing