We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

listview to list column headers in excel worksheet

Fordraiders
Fordraiders asked
on
Medium Priority
1,324 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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

Author

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:
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Is the listview multicolumn? Try this

Me.lvwSource.Items.Add(New ListViewItem(.Range("A" & i).Value))

Author

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

getting error below
error-listview.png
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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..?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Have you stepped through
For i = 1 To .UsedRange.Columns.Count
       Me.lvwSource.Items.Add(.Range("A" & i).Value)
   Next

?

What is in Count?

Author

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..

Author

Commented:
I can't get a value from " .UsedRange.Columns.Count"

Author

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

Author

Commented:
since excel reads from zero in column headers,,, I changed

i =1   to    i = 0

Thanks for the insight and help !
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
>since excel reads from zero in column headers
That's a news because I usually COM starts from 1.

Author

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...
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Ah right.

Author

Commented:
Thanks for all the help !
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.