Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
549 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Showing blogs 1-20 (out of 94)
Posted by Andy Brown on 06 December 2024
Posted by Andy Brown on 05 November 2024
Posted by Andy Brown on 05 November 2024
Posted by Andrew Gould on 01 November 2024
Posted by Andy Brown on 25 September 2024
Posted by Andrew Gould on 01 August 2024
Posted by Andy Brown on 03 July 2024
Posted by Andy Brown on 03 July 2024
Posted by Andy Brown on 05 June 2024
Posted by Andy Brown on 08 May 2024
Posted by Andy Brown on 03 April 2024
Posted by Andy Brown on 03 April 2024
Posted by Andy Brown on 03 April 2024
Posted by Andy Brown on 12 October 2023
Posted by Andy Brown on 29 August 2023
Posted by Andy Brown on 07 June 2023
Posted by Andy Brown on 07 June 2023
Posted by Andy Brown on 25 April 2023
Posted by Andy Brown on 27 February 2023
Posted by Andy Brown on 01 February 2023
From: | Chandra |
When: | 22 Nov 22 at 11:51 |
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
From: | Andrew G |
When: | 23 Nov 22 at 06:42 |
HI,
In this line:
cmd.CommandText = i
You've set the command text to be equal to the value of your loop counter, rather than referring to the value of the cell in which you have your stored procedure name.
I hope it helps!
From: | xUser |
When: | 28 Mar 20 at 12:05 |
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.
From: | Andy B |
When: | 29 Mar 20 at 12:09 |
We're a bt preoccupied with COVID-19 events here, but I've posted your question in the hope that perhaps one of other kind readers can help?
From: | edgarfreitas |
When: | 04 May 17 at 18:18 |
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
From: | Andrew G |
When: | 05 May 17 at 07:05 |
Hi Edgar, that sounds like a cool idea!
The "ambiguous name detected" error occurs when you create multiple procedures in the same scope with the same name. What I imagine has happened is that you've copied and pasted the Worksheet_Change subroutine to create a separate procedure for the second arrow? Sadly, you can't do it this way - you can only have one Worksheet_Change subroutine per worksheet. The simple solution is to add the code for the second arrow to the same subroutine as the first.
I hope that helps and good luck with the project!
From: | edgarfreitas |
When: | 05 May 17 at 18:30 |
Andrew, I can't thank you enough!! It worked perfectly!!! :D
Thank you so much for your help.
Have a great weekend.
Kindly,
Edgar Freitas
From: | edgarfreitas |
When: | 05 May 17 at 16:40 |
Hello Andrew,
Thank you very much for your reply.
That's exactly what happened. I thought I could simply copy and paste it to create new arrows.
Your solution is: "...to add the code for the second arrow to the same subroutine as the first."
As I'm not good with VBA, could you please indicate me how to do this? I already tried but I think it's not the right way of doing it as I got an error saying "Duplicate declaration on the same scope".
This is how I did it:
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
Dim KeyCells As Range
Set KeyCells = Range("Z3")
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
Dim CellValue As Integer
Dim CurrentlySelected As Range
Set CurrentlySelected = Selection
CellValue = Sheets("747 lbs").Range("Z3")
ActiveSheet.Shapes.Range(Array("seta2")).Select
Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
'Select prev cell that was in use
CurrentlySelected.Select
End Sub
Thank you once more Andrew!
Kindly,
Edgar Freitas
From: | Andrew G |
When: | 05 May 17 at 17:29 |
Hi Edgar,
In the same way that you can't declare two subroutines with the same name in the same module, you can't declare more than one variable with the same name in the same subroutine. Simply remove the duplicate Dim statements so that you don't have more than one variable with the same name in a single procedure.
I hope that helps!
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.