Excel / VBA blogs from Wise Owl (page 2 of 5)

How to execute multiple stored procedure by using ADODB.

Ex: I have kept All stored procedure names in Column "A"

I am getting error, please help me, how to solution in below code

Public Sub Get_Result_From_Sp()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

'Create and open a connection to the Staging Database
Set cn = New ADODB.Connection
       cn.ConnectionString = _
       "Provider=MSOLEDBSQL;" & _
       "Server=BDC7-L-D2V4JG3\SQLEXPRESS;" & _
       "Database=Bhanu;" & _
       "Trusted_Connection=yes;"
       
'try to open the connection, exit the subrouting if this fails
On Error GoTo Endpoint
cn.Open

'if anything fails after this point,close the connection before exiting
On Error GoTo closeconnection

' loop All procdure
Dim LastRow As Long
Dim i As Long
LastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To LastRow
'create and populate the command using the stored procedure
Set cmd = New ADODB.Command
       cmd.ActiveConnection = cn
       cmd.CommandType = adCmdStoredProc
       

    cmd.CommandText = i
       cmd.Execute (i)
       
'Create and populate the recordset using the SQLQuery
'Set rs = New ADODB.Recordset
'   Set rs = cmd.Execute

Next i

'=======================================================
'Error HANDLERS
 '======================================================
'if the recordset is opened successfully but a runtime error occurs later we end up here
closeconnection:
cn.Close

Set rs = Nothing
Set cn = Nothing

VBA.MsgBox _
       Prompt:="An occurred after the connection was established." & vbNewLine _
           & vbNewLine & "Error number: " & Err.Number _
           & vbNewLine & "Error description: " & Err.Description, _
           Buttons:=vbCritical, _
           Title:=" Error after connection open "
      Exit Sub
      
'if the connection failed to open we end up here
Endpoint:

VBA.MsgBox _
       Prompt:="the connection failed to open." & vbNewLine _
         & vbNewLine & "Error number: " & Err.Number _
         & vbNewLine & " Error description: " & Err.Description, _
         Buttons:=vbCritical, _
         Title:=" connection error"

End Sub

Hi Andrew

Firstly want to say your scraping series of videos are an excellent resource. I have never coded previously but they have helped me a great deal.

I am in need of some help though with the below code if possible.

Sub Portfolio_Webscrape_IE()

Dim IE As New SHDocVw.InternetExplorer
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim HTMLInput As MSHTML.IHTMLElement
Dim HTMLAs As MSHTML.IHTMLElementCollection
Dim HTMLA As MSHTML.IHTMLElement
Dim URL As String

URL = "https://uk.investing.com/portfolio/"

IE.Visible = True
IE.navigate URL

Do While IE.readyState <> READYSTATE_COMPLETE
Loop

Set HTMLDoc = IE.document
Set HTMLInput = HTMLDoc.getElementById("loginFormUser_email")
HTMLInput.Value = "xxxx@xxxx.co.uk"
Set HTMLInput = HTMLDoc.getElementById("loginForm_password")
HTMLInput.Value = "xxxxx"
Set HTMLAs = HTMLDoc.getElementsByTagName("a")

For Each HTMLA In HTMLAs
If HTMLA.getAttribute("href") = "javascript:void(0)" Then
HTMLA.Click
Exit For
End If

Next HTMLA

End Sub

When I press inspect element on the “sign-in button” on the website it says:

<a class="newButton orange" onclick="loginPageFunctions.submitLogin();" href="javascript:void(0);">Sign In</a>

I've searched through many resouces but they only seem to explain how to do this if you have an actual "button" name or id or the href / rel to grab.

I would but very grateful if you can help me with my problem.  Thanks in advance.

Hello,

I'm having troubles in achieving something on excel and I think you are the right guy's to give me a hand with it.

I have an excel sheet that I use for flight simulation and between other things, I want to create a small compus with shapes(arrows) that rotates whenever I add a value to a specific cell. So far I managed to do it but only with a single arrow. When I try to add a second shape it says "Ambiguous name detected worksheet_Change".

I need 2 arrows because one indicates the heading of the aircraft and another indicates the wind.

On VBA I managed to do the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    'event raised when value in cell changed BY USER
    
    Dim KeyCells As Range
    Set KeyCells = Range("Z2")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MoveArrowToA1
    End If
End Sub
Sub MoveArrowToA1()
    'Select shape to move
    Dim CellValue As Integer
    Dim CurrentlySelected As Range
    
    Set CurrentlySelected = Selection
    
    CellValue = Sheets("747 lbs").Range("Z2")
    ActiveSheet.Shapes.Range(Array("seta")).Select
    Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
    
    'Select prev cell that was in use
    CurrentlySelected.Select
    
End Sub

This gives me only the rotation for one of the arrows (shapes), do you have any idea how do I add a second one?

https://drive.google.com/file/d/0B6JMHUkV2NKJRTZVUmRBZER6bVE/view?usp=sharing  On this link there's a picture to give you the idea of what I'm trying to do.

If you need any other information from me, please let me know.

Thank you very much for all the help you can give me.

Kindly,

Edgar Freitas