Import Table Data from Web using VBA to excel Worksheet and able to input Values which can modify the web data to be extract

Ambrosia

New Member
Joined
Dec 18, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • New-Web.JPG
    New-Web.JPG
    198.5 KB · Views: 15
  • Excel-output.PNG
    Excel-output.PNG
    162.8 KB · Views: 14

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,224,541
Messages
6,179,418
Members
452,912
Latest member
alicemil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top