hugo08642
10-24-2022, 04:01 AM
Hi everyone,
I am hoping that someone can help me make this more concise.... maybe a loop is appropriate but I am not very good with this kind of thing.
I have hundreds of stocks that each have their own sheet on a workbook.
I want relevant information from a website to go to a specific row in a specific worksheet.
The following VBA code works well and achieves the above goals, but as you can imagine, this is a time consuming approach. Any help would be much appreciated.
All required variables for the below code have been established in the option explicit section.
Sub Overviewandfinancialsseventh50()
Set ch = New selenium.ChromeDriver
Set ks = New selenium.Keys
Set ws301 = ThisWorkbook.Worksheets("AGY")
Set ws302 = ThisWorkbook.Worksheets("TUA")
Set ws303 = ThisWorkbook.Worksheets("CBO")
Set ws304 = ThisWorkbook.Worksheets("COE")
Set ws305 = ThisWorkbook.Worksheets("SM1")
Set ws306 = ThisWorkbook.Worksheets("SIQ")
Set ws307 = ThisWorkbook.Worksheets("OFX")
Set ws308 = ThisWorkbook.Worksheets("SHV")
Set ws309 = ThisWorkbook.Worksheets("WGB")
Set ws310 = ThisWorkbook.Worksheets("PBH")
Set ws311 = ThisWorkbook.Worksheets("SBM")
Set ws312 = ThisWorkbook.Worksheets("SLX")
Set ws313 = ThisWorkbook.Worksheets("LLL")
Set ws314 = ThisWorkbook.Worksheets("WHF")
Set ws315 = ThisWorkbook.Worksheets("NBI")
Set ws316 = ThisWorkbook.Worksheets("DYL")
Set ws317 = ThisWorkbook.Worksheets("BCB")
Set ws318 = ThisWorkbook.Worksheets("MAD")
Set ws319 = ThisWorkbook.Worksheets("HPI")
Set ws320 = ThisWorkbook.Worksheets("OCA")
Set ws321 = ThisWorkbook.Worksheets("IDX")
Set ws322 = ThisWorkbook.Worksheets("NMT")
Set ws323 = ThisWorkbook.Worksheets("URW")
Set ws324 = ThisWorkbook.Worksheets("PFP")
Set ws325 = ThisWorkbook.Worksheets("ECX")
Set ws326 = ThisWorkbook.Worksheets("AEF")
Set ws327 = ThisWorkbook.Worksheets("RMS")
Set ws328 = ThisWorkbook.Worksheets("HM1")
Set ws329 = ThisWorkbook.Worksheets("AD8")
Set ws330 = ThisWorkbook.Worksheets("JHG")
Set ws331 = ThisWorkbook.Worksheets("MIR")
Set ws332 = ThisWorkbook.Worksheets("OPH")
Set ws333 = ThisWorkbook.Worksheets("ABB")
Set ws334 = ThisWorkbook.Worksheets("PPC")
Set ws335 = ThisWorkbook.Worksheets("AQZ")
Set ws336 = ThisWorkbook.Worksheets("REG")
Set ws337 = ThisWorkbook.Worksheets("EHE")
Set ws338 = ThisWorkbook.Worksheets("STA")
Set ws339 = ThisWorkbook.Worksheets("VG1")
Set ws340 = ThisWorkbook.Worksheets("GWA")
Set ws341 = ThisWorkbook.Worksheets("CTT")
Set ws342 = ThisWorkbook.Worksheets("STX")
Set ws343 = ThisWorkbook.Worksheets("MYR")
Set ws344 = ThisWorkbook.Worksheets("MYX")
Set ws345 = ThisWorkbook.Worksheets("ARU")
Set ws346 = ThisWorkbook.Worksheets("MGX")
Set ws347 = ThisWorkbook.Worksheets("FGX")
Set ws348 = ThisWorkbook.Worksheets("PWR")
Set ws349 = ThisWorkbook.Worksheets("OPT")
Set ws350 = ThisWorkbook.Worksheets("PGH")
Ticker301 = "AGY"
Ticker302 = "TUA"
Ticker303 = "CBO"
Ticker304 = "COE"
Ticker305 = "SM1"
Ticker306 = "SIQ"
Ticker307 = "OFX"
Ticker308 = "SHV"
Ticker309 = "WGB"
Ticker310 = "PBH"
Ticker311 = "SBM"
Ticker312 = "SLX"
Ticker313 = "LLL"
Ticker314 = "WHF"
Ticker315 = "NBI"
Ticker316 = "DYL"
Ticker317 = "BCB"
Ticker318 = "MAD"
Ticker319 = "HPI"
Ticker320 = "OCA"
Ticker321 = "IDX"
Ticker322 = "NMT"
Ticker323 = "URW"
Ticker324 = "PFP"
Ticker325 = "ECX"
Ticker326 = "AEF"
Ticker327 = "RMS"
Ticker328 = "HM1"
Ticker329 = "AD8"
Ticker330 = "JHG"
Ticker331 = "MIR"
Ticker332 = "OPH"
Ticker333 = "ABB"
Ticker334 = "PPC"
Ticker335 = "AQZ"
Ticker336 = "REG"
Ticker337 = "EHE"
Ticker338 = "STA"
Ticker339 = "VG1"
Ticker340 = "GWA"
Ticker341 = "CTT"
Ticker342 = "STX"
Ticker343 = "MYR"
Ticker344 = "MYX"
Ticker345 = "ARU"
Ticker346 = "MGX"
Ticker347 = "FGX"
Ticker348 = "PWR"
Ticker349 = "OPT"
Ticker350 = "PGH"
ch.Timeouts.ImplicitWait = 10000
ch.Start baseUrl:="website url"
ch.Get "/"
ch.Wait 15000
ch.Get "/login"
ch.Wait 15000
ch.FindElementByName("email").SendKeys "login info"
ch.FindElementByName("password").SendKeys "login info"
ch.FindElementByName("password").submit
ch.Get "/asx/" & Ticker301 & ""
ch.Wait 15000
Set Overviews = ch.FindElementsByClass("mt-8")
For Each Overview In Overviews
Set tbls = ch.FindElementsByCss("tbody")
For Each t In tbls
t.AsTable.ToExcel ws301.Range("R1048576").End(xlUp).Offset(1, 0)
Next t
Next Overview
ws301.Range("R1").Value = Ticker301
ch.Get "/asx/" & Ticker302 & ""
ch.Wait 15000
Set Overviews = ch.FindElementsByClass("mt-8")
For Each Overview In Overviews
Set tbls = ch.FindElementsByCss("tbody")
For Each t In tbls
t.AsTable.ToExcel ws302.Range("R1048576").End(xlUp).Offset(1, 0)
Next t
Next Overview
ws302.Range("R1").Value = Ticker302
ch.Get "/asx/" & Ticker303 & ""
ch.Wait 15000
Set Overviews = ch.FindElementsByClass("mt-8")
For Each Overview In Overviews
Set tbls = ch.FindElementsByCss("tbody")
For Each t In tbls
t.AsTable.ToExcel ws303.Range("R1048576").End(xlUp).Offset(1, 0)
Next t
Next Overview
ws303.Range("R1").Value = Ticker303
I am hoping that someone can help me make this more concise.... maybe a loop is appropriate but I am not very good with this kind of thing.
I have hundreds of stocks that each have their own sheet on a workbook.
I want relevant information from a website to go to a specific row in a specific worksheet.
The following VBA code works well and achieves the above goals, but as you can imagine, this is a time consuming approach. Any help would be much appreciated.
All required variables for the below code have been established in the option explicit section.
Sub Overviewandfinancialsseventh50()
Set ch = New selenium.ChromeDriver
Set ks = New selenium.Keys
Set ws301 = ThisWorkbook.Worksheets("AGY")
Set ws302 = ThisWorkbook.Worksheets("TUA")
Set ws303 = ThisWorkbook.Worksheets("CBO")
Set ws304 = ThisWorkbook.Worksheets("COE")
Set ws305 = ThisWorkbook.Worksheets("SM1")
Set ws306 = ThisWorkbook.Worksheets("SIQ")
Set ws307 = ThisWorkbook.Worksheets("OFX")
Set ws308 = ThisWorkbook.Worksheets("SHV")
Set ws309 = ThisWorkbook.Worksheets("WGB")
Set ws310 = ThisWorkbook.Worksheets("PBH")
Set ws311 = ThisWorkbook.Worksheets("SBM")
Set ws312 = ThisWorkbook.Worksheets("SLX")
Set ws313 = ThisWorkbook.Worksheets("LLL")
Set ws314 = ThisWorkbook.Worksheets("WHF")
Set ws315 = ThisWorkbook.Worksheets("NBI")
Set ws316 = ThisWorkbook.Worksheets("DYL")
Set ws317 = ThisWorkbook.Worksheets("BCB")
Set ws318 = ThisWorkbook.Worksheets("MAD")
Set ws319 = ThisWorkbook.Worksheets("HPI")
Set ws320 = ThisWorkbook.Worksheets("OCA")
Set ws321 = ThisWorkbook.Worksheets("IDX")
Set ws322 = ThisWorkbook.Worksheets("NMT")
Set ws323 = ThisWorkbook.Worksheets("URW")
Set ws324 = ThisWorkbook.Worksheets("PFP")
Set ws325 = ThisWorkbook.Worksheets("ECX")
Set ws326 = ThisWorkbook.Worksheets("AEF")
Set ws327 = ThisWorkbook.Worksheets("RMS")
Set ws328 = ThisWorkbook.Worksheets("HM1")
Set ws329 = ThisWorkbook.Worksheets("AD8")
Set ws330 = ThisWorkbook.Worksheets("JHG")
Set ws331 = ThisWorkbook.Worksheets("MIR")
Set ws332 = ThisWorkbook.Worksheets("OPH")
Set ws333 = ThisWorkbook.Worksheets("ABB")
Set ws334 = ThisWorkbook.Worksheets("PPC")
Set ws335 = ThisWorkbook.Worksheets("AQZ")
Set ws336 = ThisWorkbook.Worksheets("REG")
Set ws337 = ThisWorkbook.Worksheets("EHE")
Set ws338 = ThisWorkbook.Worksheets("STA")
Set ws339 = ThisWorkbook.Worksheets("VG1")
Set ws340 = ThisWorkbook.Worksheets("GWA")
Set ws341 = ThisWorkbook.Worksheets("CTT")
Set ws342 = ThisWorkbook.Worksheets("STX")
Set ws343 = ThisWorkbook.Worksheets("MYR")
Set ws344 = ThisWorkbook.Worksheets("MYX")
Set ws345 = ThisWorkbook.Worksheets("ARU")
Set ws346 = ThisWorkbook.Worksheets("MGX")
Set ws347 = ThisWorkbook.Worksheets("FGX")
Set ws348 = ThisWorkbook.Worksheets("PWR")
Set ws349 = ThisWorkbook.Worksheets("OPT")
Set ws350 = ThisWorkbook.Worksheets("PGH")
Ticker301 = "AGY"
Ticker302 = "TUA"
Ticker303 = "CBO"
Ticker304 = "COE"
Ticker305 = "SM1"
Ticker306 = "SIQ"
Ticker307 = "OFX"
Ticker308 = "SHV"
Ticker309 = "WGB"
Ticker310 = "PBH"
Ticker311 = "SBM"
Ticker312 = "SLX"
Ticker313 = "LLL"
Ticker314 = "WHF"
Ticker315 = "NBI"
Ticker316 = "DYL"
Ticker317 = "BCB"
Ticker318 = "MAD"
Ticker319 = "HPI"
Ticker320 = "OCA"
Ticker321 = "IDX"
Ticker322 = "NMT"
Ticker323 = "URW"
Ticker324 = "PFP"
Ticker325 = "ECX"
Ticker326 = "AEF"
Ticker327 = "RMS"
Ticker328 = "HM1"
Ticker329 = "AD8"
Ticker330 = "JHG"
Ticker331 = "MIR"
Ticker332 = "OPH"
Ticker333 = "ABB"
Ticker334 = "PPC"
Ticker335 = "AQZ"
Ticker336 = "REG"
Ticker337 = "EHE"
Ticker338 = "STA"
Ticker339 = "VG1"
Ticker340 = "GWA"
Ticker341 = "CTT"
Ticker342 = "STX"
Ticker343 = "MYR"
Ticker344 = "MYX"
Ticker345 = "ARU"
Ticker346 = "MGX"
Ticker347 = "FGX"
Ticker348 = "PWR"
Ticker349 = "OPT"
Ticker350 = "PGH"
ch.Timeouts.ImplicitWait = 10000
ch.Start baseUrl:="website url"
ch.Get "/"
ch.Wait 15000
ch.Get "/login"
ch.Wait 15000
ch.FindElementByName("email").SendKeys "login info"
ch.FindElementByName("password").SendKeys "login info"
ch.FindElementByName("password").submit
ch.Get "/asx/" & Ticker301 & ""
ch.Wait 15000
Set Overviews = ch.FindElementsByClass("mt-8")
For Each Overview In Overviews
Set tbls = ch.FindElementsByCss("tbody")
For Each t In tbls
t.AsTable.ToExcel ws301.Range("R1048576").End(xlUp).Offset(1, 0)
Next t
Next Overview
ws301.Range("R1").Value = Ticker301
ch.Get "/asx/" & Ticker302 & ""
ch.Wait 15000
Set Overviews = ch.FindElementsByClass("mt-8")
For Each Overview In Overviews
Set tbls = ch.FindElementsByCss("tbody")
For Each t In tbls
t.AsTable.ToExcel ws302.Range("R1048576").End(xlUp).Offset(1, 0)
Next t
Next Overview
ws302.Range("R1").Value = Ticker302
ch.Get "/asx/" & Ticker303 & ""
ch.Wait 15000
Set Overviews = ch.FindElementsByClass("mt-8")
For Each Overview In Overviews
Set tbls = ch.FindElementsByCss("tbody")
For Each t In tbls
t.AsTable.ToExcel ws303.Range("R1048576").End(xlUp).Offset(1, 0)
Next t
Next Overview
ws303.Range("R1").Value = Ticker303