Last column – VBAxcel

Export To SQL

May 24, 2019

Export To SQL DESCRIPTION: This macro exports stock daily data: FINRA Reg SHO Daily Files(FINRA/NASDAQ TRF Carteret) to SQL server. http://regsho.finra.org/regsho-Index.html Export To SQL

Sub ExportToSQL()
	'String Connection To export To SQL Server
	Dim conn As New ADODB.Connection
	Dim i
	Dim lastRow As Integer
	Dim sDate
	Dim sSymbol
	Dim market
	Dim connectString
	Dim sShortInt
	Dim sShortexempt
	Dim sShortVol As String
	Set conn = New ADODB.Connection
	conn.Open "Provider=SQLOLEDB;Data Source=DESKTOP-TG65LTOSQL2014;Initial Catalog=MyDbase;Integrated Security=SSPI;"
	If conn.State = adStateClosed Then
		Debug.Print rs.State
		MsgBox "Problem opening connection, check connection string"
	End If
	With Sheets("Sheet1")
		lastRow = Cells(Rows.Count, "A").End(xlUp).Row
		For i = 1 To lastRow - 1
			sDate         = "‘" &amp; .Cells(i, 1) &amp; "‘,"
			sSymbol       = "‘" &amp; .Cells(i, 2) &amp; "‘,"
			sShortInt     = .Cells(i, 3) &amp; ","
			sShortexempt  = .Cells(i, 4) &amp; ","
			sShortVol     = .Cells(i, 5) &amp; ","
			market        = "‘" &amp; .Cells(i, 6) &amp; "‘"
			connectString = "insert into dbo.finra (Sdate, Ssymbol, Svolume, SExemptVolume, TotalVolume, market ) values (" &amp; _
			sDate &amp; sSymbol &amp; sShortInt &amp; sShortexempt &amp; sShortVol &amp; market &amp; ");"
			conn.Execute connectString
		Next i
		MsgBox "Completed", vbOKOnly
		conn.Close
		Set conn = Nothing
	End With
End Sub

Sub ExportToSQL()

'String Connection To export To SQL Server

Dim conn As New ADODB.Connection

Dim i

Dim lastRow As Integer

Dim sDate

Dim sSymbol

Dim market

Dim connectString

Dim sShortInt

Dim sShortexempt

Dim sShortVol As String

Set conn = New ADODB.Connection

conn.Open "Provider=SQLOLEDB;Data Source=DESKTOP-TG65LTOSQL2014;Initial Catalog=MyDbase;Integrated Security=SSPI;"

If conn.State = adStateClosed Then

Debug.Print rs.State

MsgBox "Problem opening connection, check connection string"

End If

With Sheets("Sheet1")

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow - 1

sDate = "‘" & .Cells(i, 1) & "‘,"

sSymbol = "‘" & .Cells(i, 2) & "‘,"

sShortInt = .Cells(i, 3) & ","

sShortexempt = .Cells(i, 4) & ","

sShortVol = .Cells(i, 5) & ","

market = "‘" & .Cells(i, 6) & "‘"

connectString = "insert into dbo.finra (Sdate, Ssymbol, Svolume, SExemptVolume, TotalVolume, market ) values (" & _

sDate & sSymbol & sShortInt & sShortexempt & sShortVol & market & ");"

conn.Execute connectString

Next i

MsgBox "Completed", vbOKOnly

conn.Close

Set conn = Nothing

End With

End Sub

Ratings:

Import From SQL Server

No Comments

Import From SQL DESCRIPTION: This macro imports stock daily data: FINRA Reg SHO Daily Files(FINRA/NASDAQ TRF Carteret) from SQL server. http://regsho.finra.org/regsho-Index.html Import From SQL

Sub ImportFromSQLServer()
'Import from SQL Server
	Dim conn As ADODB.Connection
	Dim rs As ADODB.Recordset
	Dim sConnString As String
	'Create the connection String
	'============================ 
	sConnString = "Provider = SQLOLEDB;Data Source = DESKTOP - TG65LTOSQL2014; _
	               initial Catalog = MyDbase; Integrated Security = SSPI;"
	'Create the Connection And Recordset objects
	'============================================ 
	Set conn = New ADODB.Connection
	Set rs   = New ADODB.Recordset
	'Open the connection And execute
	'=============================== 
	conn.Open sConnString
	Set rs = conn.Execute(Select * FROM finra;)
	'Check we have data then export
    '============================== 
	If Not rs.EOF Then
		Sheets(1).Range(A1).CopyFromRecordset rs
		rs.Close
	Else
		MsgBox "Error: No records returned.", vbCritical
	End If
	'Clean up
	'========
	If CBool(conn.State And adStateOpen) Then conn.Close
	Set conn = Nothing
	Set rs = Nothing
	MsgBox "Completed", vbCritical
	End Sub

Sub ImportFromSQLServer()

'Import from SQL Server

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim sConnString As String

'Create the connection String

'============================

sConnString = "Provider = SQLOLEDB;Data Source = DESKTOP - TG65LTOSQL2014; _

initial Catalog = MyDbase; Integrated Security = SSPI;"

'Create the Connection And Recordset objects

'============================================

Set conn = New ADODB.Connection

Set rs = New ADODB.Recordset

'Open the connection And execute

'===============================

conn.Open sConnString

Set rs = conn.Execute(Select * FROM finra;)

'Check we have data then export

'==============================

If Not rs.EOF Then

Sheets(1).Range(A1).CopyFromRecordset rs

rs.Close

Else

MsgBox "Error: No records returned.", vbCritical

End If

'Clean up

'========

If CBool(conn.State And adStateOpen) Then conn.Close

Set conn = Nothing

Set rs = Nothing

MsgBox "Completed", vbCritical

End Sub

Ratings:

Outlook Automation DESCRIPTION: To export emails to ExcelSheet by using Outlook macro and Excel macro. Basically, the logic of the codes of the two are similar. The only major differences is using different references. When you run macro in outlook, you need to Excel application references. On the other hand, if you run macro in […]

PowerShellDetail

June 10, 2019

No Comments

CMDLET: Get_Childitem DESCRIPTION: To list any empty directory full name

$a = Get-ChildItem 'e:\' -Recurse | Where { $_.PsIsContainer -eq $true }
$a | Where { $_.GetFiles().Count -eq 0 -and $_.GetDirectories().Count -eq 0 } |Select-Object fullname

1 2	$a = Get-ChildItem 'e:\' -Recurse \| Where { $_.PsIsContainer -eq $true } $a \| Where { $_.GetFiles().Count -eq 0 -and $_.GetDirectories().Count -eq 0 } \|Select-Object fullname

I spoke to Rose on Wednesday, before Game 3 of the Finals, which he was covering from Oakland. I had been interested in talking about his basketball and media career, but I started by asking him about the analytics movement, which has revolutionized […]

Binding

May 20, 2019

No Comments

Binding DESCRIPTION: The difference between EARLY Binding and LATE Binding. EARLY Binding 1. New requires that a type library is reference (Microsoft Internet Control Reference Library) 2. IntelliSense available LATE Binding 1. CreateObject uses the registry. No reference needed 2. No IntelliSense available Early/Late Binding

Sub Early_VS_Late_Binding()
	 Call EARLY_Binding(“www.YAHOO.com”)
	 Call LATE_Binding(“www.GOOGLE.com”)
End Sub

'—————————————————————–
Sub EARLY_Binding(Website As String)
	 Dim ObjIE As InternetExplorer
	 Set ObjIE = New InternetExplorer
	ObjIE.Navigate (Website)
	If ObjIE.Busy Or ObjIE.ReadyState <> READYSTATE_COMPLETE Then
		DoEvents
	End If
	ObjIE.Visible = True
End Sub

'—————————————————————–

Sub LATE_Binding(Website As String)
	Dim ObjIE As Object
	Set ObjIE = CreateObject(“InternetExplorer.Application”)
	ObjIE.Navigate (Website)
	If ObjIE.Busy Or ObjIE.ReadyState <> READYSTATE_COMPLETE Then
		DoEvents
	End If
	ObjIE.Visible = True

End Sub

Sub Early_VS_Late_Binding()

Call EARLY_Binding(“www.YAHOO.com”)

Call LATE_Binding(“www.GOOGLE.com”)

End Sub

'—————————————————————–

Sub EARLY_Binding(Website As String)

Dim ObjIE As InternetExplorer

Set ObjIE = New InternetExplorer

ObjIE.Navigate (Website)

If ObjIE.Busy Or ObjIE.ReadyState <> READYSTATE_COMPLETE Then

DoEvents

End If

ObjIE.Visible = True

End Sub

'—————————————————————–

Sub LATE_Binding(Website As String)

Dim ObjIE As Object

Set ObjIE = CreateObject(“InternetExplorer.Application”)

ObjIE.Navigate (Website)

If ObjIE.Busy Or ObjIE.ReadyState <> READYSTATE_COMPLETE Then

DoEvents

End If

ObjIE.Visible = True

End Sub

Ratings:

Class

No Comments

Class DESCRIPTION: Example of Class module implementation to populate Grade column. Procedure

Sub StudentClassModule_M()

Dim Student As clsStudent
Dim i As Integer

Set Student = New clsStudent
' The 2 lines above can be written as "Dim iStudent As New clsStudent"
i = 2
While Cells(i, 1) <> ""
    Student.Name = Cells(i, 1)                      'This will execute LET statement in class module
    Debug.Print "Student.Name= " & Student.Name     'This will execute GET statement in class module
    Student.Marks = Cells(i, 2)
    Debug.Print "Student.Marks = " & Student.Marks
    Cells(i, 3) = Student.Grade
i = i + 1
Wend

End Sub

Sub StudentClassModule_M()

Dim Student As clsStudent

Dim i As Integer

Set Student = New clsStudent

' The 2 lines above can be written as "Dim iStudent As New clsStudent"

i = 2

While Cells(i, 1) <> ""

Student.Name = Cells(i, 1) 'This will execute LET statement in class module

Debug.Print "Student.Name= " & Student.Name 'This will execute GET statement in class module

Student.Marks = Cells(i, 2)

Debug.Print "Student.Marks = " & Student.Marks

Cells(i, 3) = Student.Grade

i = i + 1

Wend

End Sub

Class

'clsStudent - Class module for Student
Private strStuName As String
Private StudentMark As Double

Public Property Let Name(strN As String)
    strStuName = strN
End Property

Public Property Get Name() As String
    Name = strStuName
End Property

Public Property Let Marks(iMarks As Double)
    StudentMark = iMarks
End Property

Public Property Get Marks() As Double
    Marks = StudentMark
End Property
Public Function Grade() As String

Dim StudentGrade As String

Select Case StudentMark
    Case Is >= 80
         StudentGrade = "A"
    Case 70 To 79
        StudentGrade = "B"
    Case 50 To 69
        StudentGrade = "C"
    Case Else
        StudentGrade = "F"
End Select


Grade = StudentGrade

End Function

'clsStudent - Class module for Student

Private strStuName As String

Private StudentMark As Double

Public Property Let Name(strN As String)

strStuName = strN

End Property

Public Property Get Name() As String

Name = strStuName

End Property

Public Property Let Marks(iMarks As Double)

StudentMark = iMarks

End Property

Public Property Get Marks() As Double

Marks = StudentMark

End Property

Public Function Grade() As String

Dim StudentGrade As String

Select Case StudentMark

Case Is >= 80

StudentGrade = "A"

Case 70 To 79

StudentGrade = "B"

Case 50 To 69

StudentGrade = "C"

Case Else

StudentGrade = "F"

End Select

Grade = StudentGrade

End Function

Ratings:

Collection

No Comments

Collection DESCRIPTION: VBA Collection is a simple native data structure available in VBA to store (collect as you wish) objects. VBA Collections are more flexible than VBA Arrays as they are not limited in their size at any point in time and don’t require manual re-sizing. Collections are also useful when you don’t want to […]

Last Row and Column

No Comments

Find Last Row and Column DESCRIPTION: There are certain situations where we perform some tasks by finding last used Row with data in a Column. For examples, There may be many columns with data and each column may have different number of items (rows). In this situation we need to find exact number of rows […]

Reference

May 13, 2019

No Comments

Reference DESCRIPTION: As a programmer, it’s a good practice to add references you need on the body of the codes rather than manually added to the application. This gives an assurance that the application always work whenever new implementation is done. There are 2 ways to add references in VBA: 1. Via GUID 2. Via […]