Link to home
Start Free TrialLog in
Avatar of fruitloopy
fruitloopy

asked on

VB.NET - Read each cell in column X in Excel spreadsheet and do stuff

I've got so far with a VB.NET Windows Form application but now I'm stuck
I have an excel spreadsheet containing the service tag of Dell PC's. I normally use Dell's Warranty Tool on individual PC service tags to get the warranty information but now I want to do it in bulk so we can find out when all our PC's warranties expire.
So this needs to read each service tag in a specified sheet and column, use the Dell Warranty Lookup and ideally write the associated information returned in the next column.
Here's my code so far:
    Private Sub btnExecute_Click(sender As Object, e As EventArgs) Handles btnExecute.Click
        If txtFile.Text Is Nothing Then
            MessageBox.Show("No text file has been selected", "No text file selected", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else

            Dim xlApp As excel.Application
            Dim xlWorkBook As excel.Workbook
            Dim xlWorkSheet As excel.Worksheet

            xlApp = New excel.Application
            xlWorkBook = xlApp.Workbooks.Open(txtFile.Text)
            Dim cb As String = ComboBox1.SelectedItem.ToString()
            Dim cb2 As String
            Dim mychar() As Char = {"$"}
            cb = cb.Replace("'", "")
            cb2 = cb.Remove(cb.Length - 1)

            Dim wksheet = cb
            xlWorkSheet = xlWorkBook.Worksheets(cb2)
            Dim sheet As String = xlWorkSheet.ToString()
            Dim strconn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFile.Text & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
            Dim conn As New OleDbConnection(strconn)
            conn.Open()
            Dim ds As New OleDb.OleDbDataAdapter("Select * from [" & wksheet & "]", conn)
            Dim dt As New DataTable
            ds.Fill(dt)
            Dim rowcount As Integer
            rowcount = dt.Rows.Count()
            lblPBStatus.Text = "Running..."
            pb1.Visible = True
            pb1.Maximum = 100

            Application.DoEvents()
            Dim proxy As New com.dell.xserv.AssetService()

            Dim sGUID As New Guid("11111111-1111-1111-1111-111111111111")

            Dim sOutput As com.dell.xserv.Asset() = proxy.GetAssetInformation(sGUID, "Dell Warranty Tool", ServiceTag)
            Dim eData As Object() = sOutput(0).Entitlements
            For Each ed As com.dell.xserv.EntitlementData In eData
                Dim EndDate As String


            Next
            xlWorkBook.Close()
            xlApp.Quit()

            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
            pb1.Value = 100
        End If
    End Sub

Open in new window

Basically I need to declare what "servicetag" is and use a loop to return each result.
Thanks in advance
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

I assume you are filling the dt Datatable variable with information from your Excel sheet containing your Dell info? If so, after filling that you can loop through the rows and do what's needed. I do it like this:

Using con As New OLEDB.Connection("Your connection string")
  con.OPen
  Using cmd As New OLEDB.Command
    cmd.Connection = con
    Using dt As New Datatable
      dt.Load cmd.ExecuteReader
      For each dtr As Datarow in dt.Rows
        '/ Get the value from the row here:
         Dim sOutput As com.dell.xserv.Asset() = proxy.GetAssetInformation(sGUID, "Dell Warranty Tool", dtr("TheColumnWithTheServiceTag")
            Dim eData As Object() = sOutput(0).Entitlements
            For Each ed As com.dell.xserv.EntitlementData In eData
                Dim EndDate As String
            Next
      Next dtr
    End Using
  End Using
End Using

Try working on that, and post back with errors and such ...
Avatar of fruitloopy
fruitloopy

ASKER

Ok I've given this a try and I get the error "Column 'O' does not belong to table"
System.ArgumentException was unhandled
  HResult=-2147024809
  Message=Column '2' does not belong to table .
  Source=System.Data
  StackTrace:
       at System.Data.DataRow.GetDataColumn(String columnName)
       at System.Data.DataRow.get_Item(String columnName)
       at Caterpillar_Support_Tools_.NET.WarrantyList.btnExecute_Click(Object sender, EventArgs e) in C:\Users\foxwesr\Documents\Caterpillar Support Tools v3\Caterpillar Support Tools v3\WarrantyList.vb:line 76
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.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(IntPtr 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.RunDialog(Form form)
       at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
       at System.Windows.Forms.Form.ShowDialog()
       at Support_Tools_.NET.CheckWarranty.btnList_Click(Object sender, EventArgs e) in C:\Users\fox\Documents\Support Tools v3\Support Tools v3\CheckWarranty.vb:line 142
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.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(IntPtr 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 Support_Tools_.NET.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly 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.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

Here's the code:
Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFile.Text & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";")
                con.Open()
                Using cmd As New OleDbCommand
                    cmd.Connection = con
                    Dim ds As New OleDb.OleDbDataAdapter("Select * from [" & wksheet & "]", con)
                    'Dim dt As New DataTable
                    '
                    Using dt As New DataTable
                        ds.Fill(dt)
                        'dt.Load(cmd.ExecuteReader)
                        For Each dtr As DataRow In dt.Rows
                            '/ Get the value from the row here:
                            Dim proxy As New com.dell.xserv.AssetService()

                            Dim sGUID As New Guid("11111111-1111-1111-1111-111111111111")
                            Dim sOutput As com.dell.xserv.Asset() = proxy.GetAssetInformation(sGUID, "Dell Warranty Tool", dtr(txtColumn.Text))
                            'Dim sOutput As com.dell.xserv.Asset() = proxy.GetAssetInformation(sGUID, "Dell Warranty Tool", dtr("TheColumnWithTheServiceTag")
                            Dim eData As Object() = sOutput(0).Entitlements
                            For Each ed As com.dell.xserv.EntitlementData In eData
                                Dim EndDate As String
                                EndDate = ed.EndDate.ToString()
                                EndDate.Remove(10, 9)
                                Dim lvi As New ListViewItem
                                lvi = ListView1.Items.Add(EndDate)
                            Next
                        Next dtr
                    End Using
                End Using
            End Using

Open in new window

I probably haven't implemented this code correctly as I'm only a beginner at VB.NET!
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Works great Scott, thanks.
Here's the code now:
Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtFile.Text & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";")
                con.Open()
                Using cmd As New OleDbCommand
                    cmd.Connection = con
                    Dim ds As New OleDb.OleDbDataAdapter("Select * from [" & wksheet & "]", con)
                    'Dim dt As New DataTable
                    '
                    Using dt As New DataTable
                        ds.Fill(dt)
                        Dim rowcount As Integer
                        rowcount = dt.Rows.Count()
                        Dim sB As Long = rowcount
                        Dim cR As Long = 0
                        cR = cR + rowcount
                        'pb1.Value = (cR / sB) * 100
                        'dt.Load(cmd.ExecuteReader)
                        For Each dtr As DataRow In dt.Rows
                            '/ Get the value from the row here:
                            Dim counter As Integer
                            Dim proxy As New com.dell.xserv.AssetService()
                            Dim sGUID As New Guid("11111111-1111-1111-1111-111111111111")
                            Dim sOutput As com.dell.xserv.Asset() = proxy.GetAssetInformation(sGUID, "Dell Warranty Tool", dtr("serial_number"))
                            Dim eData As Object() = sOutput(0).Entitlements
                            For Each ed As com.dell.xserv.EntitlementData In eData
                                If ed.ToString() > "" Then
                                    Dim PC As String = dtr("computer_name")
                                    Dim user As String = dtr("user_name")
                                    Dim model As String = sOutput(0).AssetHeaderData.SystemModel.ToString()
                                    Dim EndDate As String
                                    EndDate = ed.EndDate.ToString()
                                    Dim endD = EndDate.Remove(10, 9)
                                    Dim refresh As Date = endD
                                    Dim currentdate As DateTime = DateTime.Now
                                    Dim expirydate As String

                                    If model.Contains("OptiPlex") Then
                                        expirydate = DateAdd(DateInterval.Year, 2, refresh)
                                    ElseIf model.Contains("Latitude") Then
                                        expirydate = DateAdd(DateInterval.Year, 1, refresh)
                                    ElseIf model.Contains("Precision") Then
                                        expirydate = DateAdd(DateInterval.Year, 1, refresh)
                                    End If
                                    Dim refreshdate As Date = expirydate
                                    Dim diff As Long = DateDiff(DateInterval.Day, currentdate, refreshdate)

                                    Dim lvi As New ListViewItem
                                    lvi = ListView1.Items.Add(PC)
                                    lvi.SubItems.Add(user)
                                    lvi.SubItems.Add(model)
                                    lvi.SubItems.Add(endD)
                                    If expirydate < currentdate Then
                                        lvi.SubItems.Add("Lifecycle Expired")
                                    End If
                                End If
                                counter += 1
                                pb1.Value = counter / rowcount * 100
                            Next

                            AddHandler Me.ListView1.ColumnClick, AddressOf ColumnClick
                        Next dtr
                    End Using
                End Using
            End Using

Open in new window

The only thing I would like to improve is how the data is added to the listview. At the moment it doesnt add the results until the entire spreadsheet has been processed and with around 600 entries it can hang for a few minutes making it seem that nothing is happening.
Is it possible to change it so it adds each result as it happens?