-
Dec 7th, 2017, 08:17 AM
#1
Thread Starter
Enjoy the moment
[RESOLVED] trying to export listview items to excel it shows only 1 Item why?
hey,
i have a listview that i want to export to excel.
now when i run it ,it opens excel and show me But
only 1 item from the listview why?
i am looping in the listview
this is my code
Code:
Dim StrItemName As String
Dim QtyName As Double
Dim TotalSumName As Double
Dim I As Long
Dim K As Integer
For K = 1 To LsVw.ListItems.Count
StrItemName = LsVw.ListItems(K).Text
QtyName = LsVw.ListItems(K).SubItems(1)
TotalSumName = LsVw.ListItems(K).SubItems(2)
Set xlwk = xlApp.Workbooks.Open(App.Path & "\Excel\General.xls")
xlApp.Range("K" & Trim(Str(1))).Value = "Item"
xlApp.Range("I" & Trim(Str(1))).Value = "Qty"
xlApp.Range("G" & Trim(Str(1))).Value = "Total"
I = 2
xlApp.Range("K" & Trim(Str(I))).Value = StrItemName
xlApp.Range("I" & Trim(Str(I))).Value = QtyName
xlApp.Range("G" & Trim(Str(I))).Value = FormatCurrency(TotalSumName)
I = I + 1
Totaltsum = FormatCurrency(Lbltotal.Caption)
Next
Tnx for your help
salsa
-
Dec 7th, 2017, 08:25 AM
#2
Addicted Member
Re: trying to export listview items to excel it shows only 1 Item why?
Your constantly resetting I
Utterly useless, but always willing to help
As a finishing touch god created the dutch
-
Dec 7th, 2017, 08:28 AM
#3
Thread Starter
Enjoy the moment
Re: trying to export listview items to excel it shows only 1 Item why?
Originally Posted by Goggy
Your constantly resetting I
beacuse i need to loop
what do i do then?
-
Dec 7th, 2017, 08:32 AM
#4
Addicted Member
Re: trying to export listview items to excel it shows only 1 Item why?
Just set I once, outside the loop
Utterly useless, but always willing to help
As a finishing touch god created the dutch
-
Dec 7th, 2017, 08:37 AM
#5
Thread Starter
Enjoy the moment
Re: trying to export listview items to excel it shows only 1 Item why?
Originally Posted by Goggy
Just set I once, outside the loop
i dont understand
where?
-
Dec 7th, 2017, 08:50 AM
#6
Addicted Member
Re: trying to export listview items to excel it shows only 1 Item why?
Maybe something like so:
I haven't tested it , but i think you get the idea
Code:
Dim StrItemName As String
Dim QtyName As Double
Dim TotalSumName As Double
Dim K As Integer
Set xlwk = xlApp.Workbooks.Open(App.Path & "\Excel\General.xls")
xlApp.Range("K" & Trim(Str(1))).Value = "Item"
xlApp.Range("I" & Trim(Str(1))).Value = "Qty"
xlApp.Range("G" & Trim(Str(1))).Value = "Total"
For K = 1 To LsVw.ListItems.Count
xlApp.Range("K" & Trim(Str(I))).Value = LsVw.ListItems(K + 1).Text
xlApp.Range("I" & Trim(Str(I))).Value = LsVw.ListItems(K+ 1).SubItems(1)
xlApp.Range("G" & Trim(Str(I))).Value = FormatCurrency(LsVw.ListItems(K + 1).SubItems(2))
Totaltsum = FormatCurrency(Lbltotal.Caption)
Next
Utterly useless, but always willing to help
As a finishing touch god created the dutch
-
Dec 7th, 2017, 09:20 AM
#7
Thread Starter
Enjoy the moment
Re: trying to export listview items to excel it shows only 1 Item why?
Originally Posted by Goggy
Maybe something like so:
I haven't tested it , but i think you get the idea
Code:
Dim StrItemName As String
Dim QtyName As Double
Dim TotalSumName As Double
Dim K As Integer
Set xlwk = xlApp.Workbooks.Open(App.Path & "\Excel\General.xls")
xlApp.Range("K" & Trim(Str(1))).Value = "Item"
xlApp.Range("I" & Trim(Str(1))).Value = "Qty"
xlApp.Range("G" & Trim(Str(1))).Value = "Total"
For K = 1 To LsVw.ListItems.Count
xlApp.Range("K" & Trim(Str(I))).Value = LsVw.ListItems(K + 1).Text
xlApp.Range("I" & Trim(Str(I))).Value = LsVw.ListItems(K+ 1).SubItems(1)
xlApp.Range("G" & Trim(Str(I))).Value = FormatCurrency(LsVw.ListItems(K + 1).SubItems(2))
Totaltsum = FormatCurrency(Lbltotal.Caption)
Next
the excel sheet is empty lol
-
Dec 7th, 2017, 09:39 AM
#8
Re: trying to export listview items to excel it shows only 1 Item why?
Hi Salsa,
try this...
Code:
Private Sub Command3_Click()
Dim xlApp As Object, xlBook As Workbook, xlsheet As Object, i As Integer
Dim s As String
Dim lst As ListItem, lst1 As ListSubItem, row As Integer, col As Integer
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add 'creates a blank workbook
Set xlsheet = xlBook.Worksheets.Item(1) 'to select the sheet within the workbook
row = 1
col = 1
For i = 1 To ListView1.ColumnHeaders.Count
xlsheet.Cells(row, col) = ListView1.ColumnHeaders(i)
col = col + 1
Next
col = 1
row = row + 1
For Each lst In ListView1.ListItems
col = 1
xlsheet.Cells(row, col) = lst.Text
col = col + 1
For Each lst1 In lst.ListSubItems
xlsheet.Cells(row, col) = lst1.Text
col = col + 1
Next
row = row + 1
Next
xlBook.Close savechanges:=True, FileName:="c:\ExceltestExport.xls"
xlApp.Quit
End Sub
regards
Chris
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Dec 7th, 2017, 09:39 AM
#9
Addicted Member
Re: trying to export listview items to excel it shows only 1 Item why?
the moment i posted it was already obsolete. thanks to Chris )))
Would you be so kind as to try this?
Code:
Dim XlApp As Object 'Holds an excel app
Dim XlWb As Object 'Holds a workbook
Dim XlWs As Object 'Holds a worksheet
Dim ColumnIndex As Integer
Dim RowIndex As Integer
Set XlApp = CreateObject("Excel.Application")
'Add a new workbook
Set XlWb = XlApp.workbooks.Add
'Get the first worksheet
Set XlWs = XlWb.worksheets(1)
With XlWs
' Set ColumnHeaders
RowIndex = 1
For ColumnIndex = 1 To 3
.cells(RowIndex, ColumnIndex).Value = "Column_" & CStr(i)
Next
' Loop through a List, and fill the cells.
For RowIndex = 1 To LsVw.ListItems.Count
XlWs.cells(RowIndex + 1, 1).Value = LsVw.ListItems(RowIndex).Text
XlWs.cells(RowIndex + 1, 2).Value = LsVw.ListItems(RowIndex).SubItems(1)
XlWs.cells(RowIndex + 1, 3).Value = FormatCurrency(LsVw.ListItems(RowIndex).SubItems(2))
Next
End With
'Save the workbook
XlWb.save
XlWb.Close
'Close the excel application
XlApp.quit
End Sub
Utterly useless, but always willing to help
As a finishing touch god created the dutch
-
Dec 7th, 2017, 10:27 AM
#10
Thread Starter
Enjoy the moment
Re: trying to export listview items to excel it shows only 1 Item why?
i tried both methods still some errors
i rather use my old code combine and fixed with your Help!!
Code:
Dim StrItemName As String
Dim QtyName As Double
Dim TotalSumName As Double
Dim K As Integer
Set xlwk = xlApp.Workbooks.Open(App.Path & "\Excel\General.xls")
xlApp.Range("K" & Trim(Str(1))).Value = "Item"
xlApp.Range("I" & Trim(Str(1))).Value = "Qty"
xlApp.Range("G" & Trim(Str(1))).Value = "Total"
For K = 1 To LsVw.ListItems.Count
xlApp.Range("K" & Trim(Str(I))).Value = LsVw.ListItems(K + 1).Text
xlApp.Range("I" & Trim(Str(I))).Value = LsVw.ListItems(K+ 1).SubItems(1)
xlApp.Range("G" & Trim(Str(I))).Value = FormatCurrency(LsVw.ListItems(K + 1).SubItems(2))
Totaltsum = FormatCurrency(Lbltotal.Caption)
Next
-
Dec 7th, 2017, 11:23 AM
#11
Re: trying to export listview items to excel it shows only 1 Item why?
Sorry...late post...wife got me doing other things after I started it...
-
Dec 7th, 2017, 11:28 AM
#12
Addicted Member
Re: trying to export listview items to excel it shows only 1 Item why?
Originally Posted by SamOscarBrown
Sorry...late post...wife got me doing other things after I started it...
Lol �� no problem, better late then never.
Except at a party after all the beer is gone!
Utterly useless, but always willing to help
As a finishing touch god created the dutch
-
Dec 7th, 2017, 10:48 AM
#13
Thread Starter
Enjoy the moment
Re: trying to export listview items to excel it shows only 1 Item why?
tnx all with all codes here i got it working
-
Dec 7th, 2017, 11:22 AM
#14
Re: [RESOLVED] trying to export listview items to excel it shows only 1 Item why?
First, you are overwriting your 'header' K1, I1, G1, etc. In your first part, you put Item, Qty and Total there.
Then, the first time through your LV, you overwrite them with whatever is in lsVw.ListItems(K+1).Text.
Then, you increment K in your loop, but not I...I stays as zero (assuming you dimmed it).
Suggest you walk through your loop in the debugger and see for yourself how K changes, yet I does not.
-
Dec 7th, 2017, 11:41 AM
#15
Thread Starter
Enjoy the moment
Re: [RESOLVED] trying to export listview items to excel it shows only 1 Item why?
Except at a party after all the beer is gone!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|