[RESOLVED] Creating An Excel Spreadsheet

I have the following code which creates an Excel spreadsheet and dumps the contents of a Listview, including the Listview columnheaders, to the spreadsheet. (Only the code that pertains to the question is posted.)

It all works great (and thanks to RobDog, the columns that I need to be centered are :thumb: )

My headers go from A:1 to E:1 and the listview contents are dumped from
A:3 across to E:3 and on down for as many rows as the listview has.

Again, everything is working great, but (there is always a "but" right? :D ), now I've been told that my column headers need to start at A:7 and run across to E:7 and the lisview contents start at A:9 and run across to E:9 and down, plus I need to add some free text to A:1 A:2 A:3 A:4 and A:5

So, two questions: How do I modify what I have to move everything down to the A:7 starting point?

How do I write my text to the various columns of row A?

VB Code:

Set objExcel = New Excel.Application
    Set bkWorkBook = objExcel.Workbooks.Add
    Set shWorkSheet = bkWorkBook.ActiveSheet
    With lvwAR
        For i = 1 To .ColumnHeaders.Count
            shWorkSheet.Cells(1, Chr(64 + i)) = .ColumnHeaders(i)
        Next
        objExcel.Workbooks(1).Sheets(1).Columns("A:A").HorizontalAlignment = xlCenter
        objExcel.Workbooks(1).Sheets(1).Columns("C:C").HorizontalAlignment = xlCenter
        objExcel.Workbooks(1).Sheets(1).Columns("d:d").HorizontalAlignment = xlCenter
        objExcel.Workbooks(1).Sheets(1).Columns("E:E").HorizontalAlignment = xlCenter
        For i = 1 To .ListItems.Count
            shWorkSheet.Cells(i + 2, "A") = .ListItems(i).Text
            For j = 2 To .ColumnHeaders.Count
                shWorkSheet.Cells(i + 2, Chr(64 + j)) = .ListItems(i).SubItems(j - 1)
            Next
        Next
        shWorkSheet.Columns("A:BZ").AutoFit

Hello,

Code:

---

Set objExcel = New Excel.Application
    Set bkWorkBook = objExcel.Workbooks.Add
    Set shWorkSheet = bkWorkBook.ActiveSheet
    With lvwAR
    'you can use a row_counter, col_counter variable instead
    'of using the letter of a column
        For i = 1 To .ColumnHeaders.Count
            'cells(row,col)
            shWorkSheet.Cells(i+6, 1) = .ColumnHeaders(i)

            'shWorkSheet.Cells(1, Chr(64 + i)) = .ColumnHeaders(i)
        Next
objExcel.Workbooks(1).Sheets(1).Columns("A:E").HorizontalAlignment = xlCenter
       
        For i = 1 To .ListItems.Count
            shWorkSheet.Cells(i + 2,1) = .ListItems(i).Text
           
            'shWorkSheet.Cells(i + 2, "A") = .ListItems(i).Text
            For j = 2 To .ColumnHeaders.Count
                shWorkSheet.Cells(i + 2,j) = .ListItems(i).SubItems(j - 1)
            Next
        Next
        shWorkSheet.Cells.Select
        shWorkSheet.Columns.AutoFit

        'shWorkSheet.Columns("A:BZ").AutoFit

---

VB Code:

Set objExcel = New Excel.Application
    Set bkWorkBook = objExcel.Workbooks.Add
    Set shWorkSheet = bkWorkBook.ActiveSheet
    With lvwAR
        For i = 1 To .ColumnHeaders.Count
            shWorkSheet.Cells([B][COLOR=Red]7[/COLOR][/B], Chr(64 + i)) = .ColumnHeaders(i) 'You want row 7 instead of row 1
        Next
        objExcel.Workbooks(1).Sheets(1).Columns("A:A").HorizontalAlignment = xlCenter
        objExcel.Workbooks(1).Sheets(1).Columns("C:C").HorizontalAlignment = xlCenter
        objExcel.Workbooks(1).Sheets(1).Columns("d:d").HorizontalAlignment = xlCenter
        objExcel.Workbooks(1).Sheets(1).Columns("E:E").HorizontalAlignment = xlCenter
        For i = 1 To .ListItems.Count
            shWorkSheet.Cells(i + [B][COLOR=Red]8[/COLOR][/B], "A") = .ListItems(i).Text 'You want i to start at 1 for the Listitems loop, but 9 for the rows.
            For j = 2 To .ColumnHeaders.Count
                shWorkSheet.Cells(i + [B][COLOR=Red]8[/COLOR][/B], Chr(64 + j)) = .ListItems(i).SubItems(j - 1) '...and here
            Next
        Next
       
        shWorkSheet.Range("A1") = "Put"
        shWorkSheet.Range("A2") = "Your"
        shWorkSheet.Range("A3") = "Text"
        shWorkSheet.Range("A4") = "In"
        shWorkSheet.Range("A5") = "Here"
 
        shWorkSheet.Columns("A:BZ").AutoFit

Quote:

---

Originally Posted by Hack

How do I write my text to the various columns of row A?

---

Incidentally, I hope that's supposed to be "rows of column A".


Hope that works.

zaza

Quote:

---

Originally Posted by zaza

Incidentally, I hope that's supposed to be "rows of column A".

---

LOL - yes, that is exactly what it was supposed to be.

Thanks. :)

Works like a champ! :D

Thank you zaza! :thumb: