I would like to ask help how I can improve this VBA code to import a table data from an html web source (attach image new Web) file using VBA into an Excel worksheet in which I can key in some values to modify like date, Equipment ID so that I can change the date range on the table and the equipment name that I will import. Here is a example of the VBA code which was created last 2013 (attached image Excel-output) and I would like to modify this with the new existing web which has wider data range (includes start/end processing time) -(attached image New-web table). I hope someone can help me. Thank you in advance.
Sub updatemachinelot()
Dim Cnn As Object
Dim Rst As Object
Dim Sql, Toolname As String
Dim RecQty As Long
Set Cnn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
T = Timer
Dim I, J, K As Integer
Application.ScreenUpdating = False
Sheets("ToolLotHistory").Select
Toolname = Range("b1").Text
RecQty = Range("e1").Value
With Cnn
.ConnectionString = "Provider=SQLOLEDB;Data Source=db-sngfabtimeprod01.ad.skynet;Initial Catalog=FabTime717sng;User Id=fabtimeguest;Password=T37Qx"
.Open
End With
Sql = "select top " & RecQty & " tbllot.lot AS Lot,tblarea.area AS PLocation,tblStep.step as Step,tblOperation.description AS Recipe,tblproduct.product AS Part," & _
"tblfamily.family AS Family,tblemployee.employee AS Emp,tblroute.route as Route," & _
"tbltool.tool AS Eqp,tblSubSegment.SubSegment As Stage,tblwiphistory.time AS MoveTime,tblwiphistory.units AS Main,tblwiphistory.subunits AS Sub" & _
" FROM tblLot INNER JOIN " & _
" tblwiphistory on tbllot.lotid=tblwiphistory.lotid left join" & _
" tblwiptrantype on tblwiptrantype.WIPTranTypeID = tblwiphistory.WIPTranTypeID left join" & _
" tblarea on tblarea.areaid = tblwiphistory.areaid left join" & _
" tblcomment on tblwiphistory.commentid=tblcomment.commentid left join" & _
" tblOperation on tblOperation.operationid=tblwiphistory.operationid left join" & _
" tblproduct on tblproduct.productid=tblwiphistory.productid left join" & _
" tbltool on tbltool.toolid=tblwiphistory.toolid left join" & _
" tblfamily on tblwiphistory.familyid=tblfamily.familyid left join" & _
" tblSubSegment on tblwiphistory.SubSegmentid=tblSubSegment.SubSegmentid left join" & _
" tblStep on tblStep.stepid=tblwiphistory.stepid left join" & _
" tblEmployee on tblemployee.employeeid=tblwiphistory.employeeid left join" & _
" tblroute on tblroute.routeid=tblwiphistory.routeid" & _
" where tblwiptrantype.wiptrantype='Move'" & _
" and tblwiphistory.FactoryID=4" & _
" and tblTool.tool = '" & Toolname & "' " & _
" and tblarea.area in ('s3photo','s3sputter','s3plate','s3solder','s3grind')" & _
" order by tblwiphistory.time desc"
Rst.Open Sql, Cnn
Range("a3:aa60000").ClearContents
Cells(3, 1).Select
For Each F In Rst.Fields
ActiveCell.Value = F.Name
ActiveCell.Offset(0, 1).Select
Next
Cells(4, 1).Select
ActiveCell.CopyFromRecordset Rst
Columns("k:k").Select
Selection.NumberFormat = "dd-mmm-yyyy h:mm:ss;@"
Range("b1").Select
Range("h1") = Timer - T
Rst.Close
Cnn.Close
Set Rst = Nothing
Set Cnn = Nothing
Application.ScreenUpdating = True
End Sub
Sub updatemachinelot()
Dim Cnn As Object
Dim Rst As Object
Dim Sql, Toolname As String
Dim RecQty As Long
Set Cnn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
T = Timer
Dim I, J, K As Integer
Application.ScreenUpdating = False
Sheets("ToolLotHistory").Select
Toolname = Range("b1").Text
RecQty = Range("e1").Value
With Cnn
.ConnectionString = "Provider=SQLOLEDB;Data Source=db-sngfabtimeprod01.ad.skynet;Initial Catalog=FabTime717sng;User Id=fabtimeguest;Password=T37Qx"
.Open
End With
Sql = "select top " & RecQty & " tbllot.lot AS Lot,tblarea.area AS PLocation,tblStep.step as Step,tblOperation.description AS Recipe,tblproduct.product AS Part," & _
"tblfamily.family AS Family,tblemployee.employee AS Emp,tblroute.route as Route," & _
"tbltool.tool AS Eqp,tblSubSegment.SubSegment As Stage,tblwiphistory.time AS MoveTime,tblwiphistory.units AS Main,tblwiphistory.subunits AS Sub" & _
" FROM tblLot INNER JOIN " & _
" tblwiphistory on tbllot.lotid=tblwiphistory.lotid left join" & _
" tblwiptrantype on tblwiptrantype.WIPTranTypeID = tblwiphistory.WIPTranTypeID left join" & _
" tblarea on tblarea.areaid = tblwiphistory.areaid left join" & _
" tblcomment on tblwiphistory.commentid=tblcomment.commentid left join" & _
" tblOperation on tblOperation.operationid=tblwiphistory.operationid left join" & _
" tblproduct on tblproduct.productid=tblwiphistory.productid left join" & _
" tbltool on tbltool.toolid=tblwiphistory.toolid left join" & _
" tblfamily on tblwiphistory.familyid=tblfamily.familyid left join" & _
" tblSubSegment on tblwiphistory.SubSegmentid=tblSubSegment.SubSegmentid left join" & _
" tblStep on tblStep.stepid=tblwiphistory.stepid left join" & _
" tblEmployee on tblemployee.employeeid=tblwiphistory.employeeid left join" & _
" tblroute on tblroute.routeid=tblwiphistory.routeid" & _
" where tblwiptrantype.wiptrantype='Move'" & _
" and tblwiphistory.FactoryID=4" & _
" and tblTool.tool = '" & Toolname & "' " & _
" and tblarea.area in ('s3photo','s3sputter','s3plate','s3solder','s3grind')" & _
" order by tblwiphistory.time desc"
Rst.Open Sql, Cnn
Range("a3:aa60000").ClearContents
Cells(3, 1).Select
For Each F In Rst.Fields
ActiveCell.Value = F.Name
ActiveCell.Offset(0, 1).Select
Next
Cells(4, 1).Select
ActiveCell.CopyFromRecordset Rst
Columns("k:k").Select
Selection.NumberFormat = "dd-mmm-yyyy h:mm:ss;@"
Range("b1").Select
Range("h1") = Timer - T
Rst.Close
Cnn.Close
Set Rst = Nothing
Set Cnn = Nothing
Application.ScreenUpdating = True
End Sub
