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:
Thanks in advance
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
Basically I need to declare what "servicetag" is and use a loop to return each result.Thanks in advance
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.GetDat aColumn(St ring columnName)
at System.Data.DataRow.get_It em(String columnName)
at Caterpillar_Support_Tools_ .NET.Warra ntyList.bt nExecute_C lick(Objec t sender, EventArgs e) in C:\Users\foxwesr\Documents \Caterpill ar Support Tools v3\Caterpillar Support Tools v3\WarrantyList.vb:line 76
at System.Windows.Forms.Contr ol.OnClick (EventArgs e)
at System.Windows.Forms.Butto n.OnClick( EventArgs e)
at System.Windows.Forms.Butto n.OnMouseU p(MouseEve ntArgs mevent)
at System.Windows.Forms.Contr ol.WmMouse Up(Message & m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Contr ol.WndProc (Message& m)
at System.Windows.Forms.Butto nBase.WndP roc(Messag e& m)
at System.Windows.Forms.Butto n.WndProc( Message& m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.OnMessa ge(Message & m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.WndProc (Message& m)
at System.Windows.Forms.Nativ eWindow.De buggableCa llback(Int Ptr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Unsaf eNativeMet hods.Dispa tchMessage W(MSG& msg)
at System.Windows.Forms.Appli cation.Com ponentMana ger.System .Windows.F orms.Unsaf eNativeMet hods.IMsoC omponentMa nager.FPus hMessageLo op(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoopInner (Int32 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoop(Int3 2 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Run Dialog(For m form)
at System.Windows.Forms.Form. ShowDialog (IWin32Win dow owner)
at System.Windows.Forms.Form. ShowDialog ()
at Support_Tools_.NET.CheckWa rranty.btn List_Click (Object sender, EventArgs e) in C:\Users\fox\Documents\Sup port Tools v3\Support Tools v3\CheckWarranty.vb:line 142
at System.Windows.Forms.Contr ol.OnClick (EventArgs e)
at System.Windows.Forms.Butto n.OnClick( EventArgs e)
at System.Windows.Forms.Butto n.OnMouseU p(MouseEve ntArgs mevent)
at System.Windows.Forms.Contr ol.WmMouse Up(Message & m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Contr ol.WndProc (Message& m)
at System.Windows.Forms.Butto nBase.WndP roc(Messag e& m)
at System.Windows.Forms.Butto n.WndProc( Message& m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.OnMessa ge(Message & m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.WndProc (Message& m)
at System.Windows.Forms.Nativ eWindow.De buggableCa llback(Int Ptr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Unsaf eNativeMet hods.Dispa tchMessage W(MSG& msg)
at System.Windows.Forms.Appli cation.Com ponentMana ger.System .Windows.F orms.Unsaf eNativeMet hods.IMsoC omponentMa nager.FPus hMessageLo op(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoopInner (Int32 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoop(Int3 2 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Run (Applicati onContext context)
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.OnRun( )
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.DoAppl icationMod el()
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.Run(St ring[] commandLine)
at Support_Tools_.NET.My.MyAp plication. Main(Strin g[] Args) in 17d14f5c-a337-4978-8281-53 493378c107 1.vb:line 81
at System.AppDomain._nExecute Assembly(R untimeAsse mbly assembly, String[] args)
at System.AppDomain.ExecuteAs sembly(Str ing assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.Hos tingProces s.HostProc .RunUsersA ssembly()
at System.Threading.ThreadHel per.Thread Start_Cont ext(Object state)
at System.Threading.Execution Context.Ru nInternal( ExecutionC ontext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.Execution Context.Ru n(Executio nContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.Execution Context.Ru n(Executio nContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHel per.Thread Start()
InnerException:
Here's the code:
System.ArgumentException was unhandled
HResult=-2147024809
Message=Column '2' does not belong to table .
Source=System.Data
StackTrace:
at System.Data.DataRow.GetDat
at System.Data.DataRow.get_It
at Caterpillar_Support_Tools_
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Nativ
at System.Windows.Forms.Unsaf
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Form.
at System.Windows.Forms.Form.
at Support_Tools_.NET.CheckWa
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Nativ
at System.Windows.Forms.Unsaf
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at Support_Tools_.NET.My.MyAp
at System.AppDomain._nExecute
at System.AppDomain.ExecuteAs
at Microsoft.VisualStudio.Hos
at System.Threading.ThreadHel
at System.Threading.Execution
at System.Threading.Execution
at System.Threading.Execution
at System.Threading.ThreadHel
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
I probably haven't implemented this code correctly as I'm only a beginner at VB.NET!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works great Scott, thanks.
Here's the code now:
Is it possible to change it so it adds each result as it happens?
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
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?
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(
Dim eData As Object() = sOutput(0).Entitlements
For Each ed As com.dell.xserv.Entitlement
Dim EndDate As String
Next
Next dtr
End Using
End Using
End Using
Try working on that, and post back with errors and such ...