Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1111
  • Last Modified:

listview to list column headers in excel worksheet

vb.net 2008
excel object

I have the following code..using common dialog to open 2 workbooks and list the sheet names.
source and destination workbooks


 I plan on copying data FROM ONE WORKBOOK TO ANOTHER.



when I click on list1(source workbook)...I want the listview to show the column headers for that sheet..?


Imports System.Windows.Forms.ListView
Imports System.Windows.Forms.ListViewItem
Imports System.Windows.Forms.ListViewItem.ListViewSubItem
Imports System.Windows.Forms.DataObject


Public Class Form1

    Public EFile, xlFile, dbTablename1, oDest, oSource, CF, SourceColumn, SourceDest
    Private Sub cmdSourceExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSourceExcel.Click
        CommonDialog1Open.Filter = "MS Excel Files(*.xls)|*.xls|All Files(*.*)|*.*"
        CommonDialog1Open.ShowDialog()
        EFile = CommonDialog1Open.FileName
        Text1.Text = EFile ' efile is a variable
        'get workbook name
        Me.List1.Items.Clear()
        Me.lvwMap.Items.Clear()
        Me.lvwSource.Items.Clear()
        'create workbook
        On Error Resume Next
        oSource.quit()
        oSource = Nothing
        oSource = CreateObject("Excel.Application")

        oSource.Visible = False
        oSource.workbooks.OPEN(EFile)
        'oSource.Workbooks.Open sName
        'list sheets in workbook
        For i = 1 To oSource.SHeets.Count
            Me.List1.Items.Add(oSource.SHeets(i).Name)
        Next
        'List1.ListItems.Add
    End Sub

    Private Sub Command2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Command2.Click
        CommonDialog1Open.Filter = "MS Excel Files(*.xls)|*.xls|All Files(*.*)|*.*"
        CommonDialog1Open.ShowDialog()
        EFile = CommonDialog1Open.FileName
        Text2.Text = EFile ' efile is a variable
        'get workbook name
        Me.List4.Items.Clear()
        Me.lvwMap.Items.Clear()
        Me.lvwSource.Items.Clear()
        'create workbook
        On Error Resume Next
        oDest.quit()
        oDest = Nothing
        oDest = CreateObject("Excel.Application")
        oDest.Visible = False
        oDest.workbooks.OPEN(EFile)
        'oSource.Workbooks.Open sName
        'list sheets in workbook
        For i = 1 To oDest.SHeets.Count
            Me.List4.Items.Add(oDest.SHeets(i).Name)
        Next
    End Sub

    Private Sub List1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles List1.Click
        Call ListSourceHeaders(List1.SelectedItem.ToString)
    End Sub

    Private Sub List4_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles List4.Click
        Call ListDestHeaders(List1.SelectedItem.ToString)
    End Sub

    Sub ListSourceHeaders(ByVal shName As String)
        Dim i As Long
        'get the header count
        'Clears out the listview so we won't see invalid columns or sheets
        Me.lvwMap.Items.Clear()
        Me.lvwSource.Items.Clear()

        With oSource.workbooks(1).SHeets(shName)
            .Activate()
            .range("A1").Select()
            .range(oSource.Selection, oSource.Selection.END(-4161)).Select() <--- error here            'fill headers in listbox
            For i = 1 To oSource.Selection.Count
                Me.lvwSource.Items.Add(.Cells(1, i).Value)
            Next
        End With

    End Sub





source-list-headers-in-listview.png
0
Fordraiders
Asked:
Fordraiders
  • 9
  • 6
1 Solution
 
CodeCruiserCommented:
Try following code


With oSource.workbooks(1).SHeets(shName)
   .Activate()
   For i = 1 To .UsedRange.Columns.Count
       Me.lvwSource.Items.Add(.Range("A" & i).Value)
   Next
End With
0
 
FordraidersAuthor Commented:
codecruiser, thanks for the reply.

error on

Me.lvwSource.Items.Add(.Range("A" & i).Value)  <---- error below




System.Reflection.AmbiguousMatchException was unhandled
  Message="Overload resolution failed because no Public 'Add' is most specific for these arguments:     'Public Overrides Function Add(text As String) As System.Windows.Forms.ListViewItem':         Not most specific.     'Public Overrides Function Add(value As System.Windows.Forms.ListViewItem) As System.Windows.Forms.ListViewItem':         Not most specific."
  Source="Microsoft.VisualBasic"
  StackTrace:
       at Microsoft.VisualBasic.CompilerServices.OverloadResolution.ResolveOverloadedCall(String MethodName, List`1 Candidates, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, BindingFlags LookupFlags, Boolean ReportErrors, ResolutionFailure& Failure)
       at Microsoft.VisualBasic.CompilerServices.OverloadResolution.ResolveOverloadedCall(String MethodName, MemberInfo[] Members, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, BindingFlags LookupFlags, Boolean ReportErrors, ResolutionFailure& Failure)
       at Microsoft.VisualBasic.CompilerServices.NewLateBinding.ResolveCall(Container BaseReference, String MethodName, MemberInfo[] Members, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, BindingFlags LookupFlags, Boolean ReportErrors, ResolutionFailure& Failure)
       at Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
       at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
       at EnterpriseMapper.Form1.ListSourceHeaders(String shName) in C:\EE\EnterpriseMapper\EnterpriseMapper\Form1.vb:line 85
       at EnterpriseMapper.Form1.List1_Click1(Object sender, EventArgs e) in C:\EE\EnterpriseMapper\EnterpriseMapper\Form1.vb:line 59
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.ListBox.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at EnterpriseMapper.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:
0
 
CodeCruiserCommented:
Is the listview multicolumn? Try this

Me.lvwSource.Items.Add(New ListViewItem(.Range("A" & i).Value))
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
FordraidersAuthor Commented:
Is the listview multicolumn -----------  yes

getting error below
error-listview.png
0
 
CodeCruiserCommented:
Ah so you have options explicit on.

Try

Me.lvwSource.Items.Add(New ListViewItem(Ctype(.Range("A" & i).Value, String)))
0
 
FordraidersAuthor Commented:
codecruiser, OK..The code is working now...but I think Myabe I have a property on my listview not set correctly..It will only show 1 column header from sheet... The code is looping correctly and seeing the total count..?
0
 
CodeCruiserCommented:
Have you stepped through
For i = 1 To .UsedRange.Columns.Count
       Me.lvwSource.Items.Add(.Range("A" & i).Value)
   Next

?

What is in Count?
0
 
FordraidersAuthor Commented:
yes I stepped but "i"  keeps incrementing..The code is fine, i think its only showing the first column header name in the listview..
0
 
FordraidersAuthor Commented:
I can't get a value from " .UsedRange.Columns.Count"

0
 
FordraidersAuthor Commented:
codecruiser, got this to work...

Added a value for column header and traslated to alpha character ?

changed this:
 Me.lvwSource.Items.Add(New ListViewItem(CType(oSource.workbooks(1).SHeets(shName).Range(t & 1).Value, String)))





Dim t As String
        'get the header count
        'Clears out the listview so we won't see invalid columns or sheets
        Me.lvwMap.Items.Clear()
        Me.lvwSource.Items.Clear()

        'With oSource.workbooks(1).SHeets(shName).Activate()
        oSource.workbooks(1).SHeets(shName).Activate()
        For i = 0 To oSource.workbooks(1).SHeets(shName).UsedRange.Columns.Count
            t = ChrW(65 + i)
            Me.lvwSource.Items.Add(New ListViewItem(CType(oSource.workbooks(1).SHeets(shName).Range(t & 1).Value, String)))
        Next
0
 
FordraidersAuthor Commented:
since excel reads from zero in column headers,,, I changed

i =1   to    i = 0

Thanks for the insight and help !
0
 
CodeCruiserCommented:
>since excel reads from zero in column headers
That's a news because I usually COM starts from 1.
0
 
FordraidersAuthor Commented:
when I changed it to zero it added the "A"...??

Well let me say this...It was only showing B and so on...bypassing Column A header name...
0
 
CodeCruiserCommented:
Ah right.
0
 
FordraidersAuthor Commented:
Thanks for all the help !
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now