3 Replies - 1263 Views - Last Post: 26 April 2012 - 02:45 PM Rate Topic: -----

#1 ichthuso1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 18-April 12

Export LB to specific excel file

Posted 25 April 2012 - 12:40 PM

So I load some excel data into a LB and can manipulate it, at this point I want to export the data back into excel and do so with the following button:

Public Sub Button4_click(ByVal sender As Object, e As System.EventArgs) Handles Button4.Click
        ListBox1.Sorted = True
        EXCLSetup(ListBox1)
    End Sub

    Public Sub EXCLSetup(ByVal listbox_var As ListBox)
        Dim objExcel As New Excel.Application
        objExcel.Visible = True
        objExcel.Workbooks.Add()
        For idx As Integer = 0 To listbox_var.Items.Count - 1
            Dim cell As String = String.Format("A{0}", 2 + idx)
            objExcel.Range(cell).Select()
            objExcel.ActiveCell.Value = listbox_var.Items(idx)
        Next

        objExcel = Nothing
    End Sub
[\code]

However, I would like to export it to the same file that I imported early (code for importing:

[code]
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ListBox1.Items.Clear()
        With OpenFileDialog1
            .ShowReadOnly = False
            .Filter = "All Files|*.*|Excel Files (*)|*;*.xls;*.xlsx"
            .FilterIndex = 2
            If .ShowDialog = DialogResult.OK Then
                APP1 = New Excel.Application
                workbook1 = APP1.Workbooks.Open(.FileName)
                worksheet1 = workbook1.Worksheets("sheet1")
            End If
        End With

        Dim objRange As Excel.Range
        For intLoopCounter = 1 To CInt(worksheet1.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
            objRange = worksheet1.Range("A" & intLoopCounter)
            ListBox1.Items.Add(objRange.Value)
        Next intLoopCounter
'rest just organizes the data and not important here
        ListBox3.Items.Clear()
        ListBox3.Items.AddRange(ListBox1.Items.Cast(Of String).Except(ListBox2.Items.Cast(Of String)).ToArray)
    End Sub



I've tried

objExcel.Workbooks.Add(.FileName)



and other assorted things but this one is stumping me, thoughts/comments?

Public Sub Button4_click(ByVal sender As Object, e As System.EventArgs) Handles Button4.Click
ListBox1.Sorted = True
EXCLSetup(ListBox1)
End Sub

Public Sub EXCLSetup(ByVal listbox_var As ListBox)
Dim objExcel As New Excel.Application
objExcel.Visible = True
objExcel.Workbooks.Add()
For idx As Integer = 0 To listbox_var.Items.Count - 1
Dim cell As String = String.Format("A{0}", 2 + idx)
objExcel.Range(cell).Select()
objExcel.ActiveCell.Value = listbox_var.Items(idx)
Next

objExcel = Nothing
End Sub
[\code]

However, I would like to export it to the same file that I imported early (code for importing:

[code]
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
ListBox1.Items.Clear()
With OpenFileDialog1
.ShowReadOnly = False
.Filter = "All Files|*.*|Excel Files (*)|*;*.xls;*.xlsx"
.FilterIndex = 2
If .ShowDialog = DialogResult.OK Then
APP1 = New Excel.Application
workbook1 = APP1.Workbooks.Open(.FileName)
worksheet1 = workbook1.Worksheets("sheet1")
End If
End With

Dim objRange As Excel.Range
For intLoopCounter = 1 To CInt(worksheet1.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
objRange = worksheet1.Range("A" & intLoopCounter)
ListBox1.Items.Add(objRange.Value)
Next intLoopCounter
'rest just organizes the data and not important here
ListBox3.Items.Clear()
ListBox3.Items.AddRange(ListBox1.Items.Cast(Of String).Except(ListBox2.Items.Cast(Of String)).ToArray)
End Sub



Edit: fixed a wrong slash

Is This A Good Question/Topic? 0
  • +

Replies To: Export LB to specific excel file

#2 lorenolepi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 26-April 12

Re: Export LB to specific excel file

Posted 26 April 2012 - 04:38 AM

I'm looking to do something similar.... can you post how you imported the excel file into the LB as well as the wrong slash edit.
Thanks a bunch!
Was This Post Helpful? 0
  • +
  • -

#3 ichthuso1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 18-April 12

Re: Export LB to specific excel file

Posted 26 April 2012 - 11:34 AM

the following takes button 3 and imports an excel file of your choosing into listbox1, hope it helps!

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ListBox1.Items.Clear()
        With OpenFileDialog1
            .ShowReadOnly = False
            .Filter = "All Files|*.*|Excel Files (*)|*;*.xls;*.xlsx"
            .FilterIndex = 2
            If .ShowDialog = DialogResult.OK Then
                APP1 = New Excel.Application
                workbook1 = APP1.Workbooks.Open(.FileName)
                worksheet1 = workbook1.Worksheets("sheet1")
            End If
        End With

        Dim objRange As Excel.Range
        For intLoopCounter = 1 To CInt(worksheet1.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
            objRange = worksheet1.Range("A" & intLoopCounter)
            ListBox1.Items.Add(objRange.Value)
        Next intLoopCounter
        ListBox3.Items.Clear()
        ListBox3.Items.AddRange(ListBox1.Items.Cast(Of String).Except(ListBox2.Items.Cast(Of String)).ToArray)
    End Sub



and lines 1-17 are the export back to excel button thingy
Was This Post Helpful? 0
  • +
  • -

#4 lorenolepi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 26-April 12

Re: Export LB to specific excel file

Posted 26 April 2012 - 02:45 PM

Thanks for the input! I'll give it a try.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1