I'm having some trouble with some VBA code. I'm looking to extract information from LinkedIn company profiles and add them to an Excel document.
For example, in C3, I have 'Microsoft'. In C4, is the LinkedIn profile URL: linkedin.com/company/microsoft I need to extract the Specialities, Industry, Type, Headquarters and Company Size information into Excel cells. Is there any way to do this via VBA?
I've have some code below but my VBA skills are really limited. The code doesn't work and I'm not sure why. The original code was designed to get information from user profiles. I've tried to alter it to get information from company profiles (like the one above) as well as change the fields it collects. But, I think I've made a mistake somewhere along the line. Do you have any idea how to fix the code?
Thanks in advance for the help!
_____________
Option Explicit
Dim rData As Range
Dim lDrow As Long
Public Sub ImportWebData()
Dim qTab As QueryTable
Dim lRow As Long
Dim r As Range, rWS As Range, rQ As Range
Dim sURL As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'==============================================================
'Resetting things
'==============================================================
Sheets("Temp").Cells.ClearContents
lDrow = Sheets("Data").Range("A" & Rows.Count).End(xlUp)(2).Row
Sheets("Data").Range("A2:E" & lDrow).ClearContents
'==============================================================
'Finding the if URL list is provided or not!
'==============================================================
lRow = Sheets("URLlist").Range("A" & Rows.Count).End(xlUp).Row
If lRow < 2 Then Exit Sub
Set rQ = Sheets("Temp").Range("A1")
Set rWS = Sheets("URLlist").Range("A2:A" & lRow)
'==============================================================
'Looping through each URL!
'==============================================================
For Each r In rWS
sURL = "URL;" & r.Value
Set qTab = Sheets("Temp").QueryTables.Add(Connection:=sURL, Destination:=rQ)
With qTab
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
'==============================================================
'rData sets the range limit to the web data pulled in!
'There's no need to write separate sub routine FillTable but it
'is rather easier on the eyes!
'Rest is cleanup job, making way for next query
'==============================================================
Set rData = qTab.ResultRange
Call FillTable
qTab.ResultRange.ClearContents
qTab.Delete
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Private Sub FillTable()
Dim rF As Range
Dim vDataString(4) As Variant
vDataString(0) = Sheets("Temp").Range("A6").Value
Set rF = rData.Find(What:="Specialties", Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
vDataString(1) = rF.Offset(1, 0).Value
Set rF = rData.Find(What:="Company-Size", Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
vDataString(2) = rF.Offset(1, 0).Value
Set rF = rData.Find(What:="Founded", Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
vDataString(1) = rF.Offset(1, 0).Value
If InStr(1, rF.Offset(-1, 0).Value, " at ") > 0 Then
vDataString(3) = Right(rF.Offset(-1, 0).Value, (Len(rF.Offset(-1, 0).Value) - _
InStr(1, rF.Offset(-1, 0).Value, "at") - 2))
Else
vDataString(3) = rF.Offset(-1, 0).Value
End If
Set rF = rData.Find(What:="Type", Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
vDataString(4) = rF.Offset(1, 0).Value
Set rF = rData.Find(What:="Industry", Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
vDataString(4) = rF.Offset(1, 0).Value
Set rF = rData.Find(What:="Headquarters", Lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)
vDataString(4) = rF.Offset(1, 0).Value
With Sheets("Data")
lDrow = .Range("A" & Rows.Count).End(xlUp)(2).Row
.Range("A" & lDrow).Value = vDataString(0)
.Range("B" & lDrow).Value = vDataString(3)
.Range("C" & lDrow).Value = vDataString(2)
.Range("D" & lDrow).Value = vDataString(4)
.Range("E" & lDrow).Value = vDataString(1)
End With
End Sub