PowerShell Cmdlet List
PowerShell Cmdlet List
Read MorePowerShell Cmdlet List
Read MoreExport Data from Access to Excel Ratings: http://www.consultdmw.com/export-access-data-to-excel.html ReDim Limitations: 1. ReDim only for Dynamic Array, not Static Arrays Example: Dim MyArray() as integer ‘ Ok ReDim MyArray(3) Dim MyArray(2) As Integer ‘Declare as Static Array ReDim MyArray(3) ‘This will cause an error Collection with SET NEW
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | Sub dmwExport(query$, path$) On Error GoTo errHandler Dim xlApp As Object Dim wkbk As Object Dim msg$ DoCmd.TransferSpreadsheet _ TransferType: = acExport, _ SpreadsheetType: = acSpreadsheetTypeExcel12Xml, _ TableName: = query$, _ Filename: = path$, _ HasFieldNames: = True Set xlApp = CreateObject("Excel.Application") With xlApp .Visible = True Set wkbk = .Workbooks.Open(path$) End With procDone: Set wkbk = Nothing Set xlApp = Nothing Exit Sub errHandler: msg$ = Err.Description MsgBox msg$, vbExclamation, "Unanticipated Error" Resume procDone End Sub |
[…]
Read MoreExport 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | 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:
Read MoreImport 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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:
Read MoreOutlook 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 […]
Read MoreCMDLET: Get_Childitem DESCRIPTION: To list any empty directory full name
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 […]
Read MoreBinding 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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:
Read MoreClass DESCRIPTION: Example of Class module implementation to populate Grade column. Procedure
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | '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:
Read MoreCollection 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 […]
Read MoreFind 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 […]
Read MoreReference 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 […]
Read MoreVBA Constants Ratings: VBA includes a number of built-in or intrinsic constants whose values are predefined by VBA. The best place to find information about the available intrinsic constants is in the VB object browser or by pressing F2. Intrinsic Constants Button Constant Value Description vbOkOnly 0 Display OK button only vbOKCancel 1 Display OK and Cancel buttons. […]
Read MoreStrange Numbers Ratings: DESCRIPTION: Find any combination numbers that have the result contain all the numbers. Example: 142857 = 142857 X 1 285714 = 142857 X 2 428571 = 142857 X 3 571428 = 142857 X 4 714285 = 142857 X 5 857142 = 142857 X 6 Redim With Preserve keyword
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | Sub StrangeNumbers() Dim a As Long Dim Maxno As Long Dim StartNo As Long Dim Result As Long Dim d As Integer Dim i As Integer Dim ctr As Integer Dim ii As Integer Dim lastrow As Integer ' StartNo = UserForm1.TextBox3 Maxno = UserForm1.TextBox4 'Process each X Number until max number For a = StartNo To Maxno ctr = 0 'Take the X Number and multiple by multiplier until 6 loop For i = 1 To 6 Result = a * i d = Len(CStr(Result)) 'check if the result contains the X numbers FoundMatchingNumber = FindTheMatching(d, CStr(Result), CStr(a)) If FoundMatchingNumber = d Then ctr = ctr + 1 End If Next i 'if Found counter = 6 then display X number If ctr = 6 Then lastrow = Cells(Rows.Count, "A").End(xlUp).Row lastrow = lastrow + 1 For ii = 1 To 6 Cells(lastrow + ii, 1) = a Cells(lastrow + ii, 2) = ii Cells(lastrow + ii, 3) = a * ii Next ii End If Next a MsgBox "done" End Sub Function FindTheMatching(Multiplier_MAX As Integer, StringTobeSearch As String, StringResult As String) As Integer Dim aa As Integer aa = 0 For i = 1 To Multiplier_MAX chartobesearch = Mid(StringTobeSearch, i, 1) a = InStr(1, StringResult, chartobesearch, vbTextCompare) If a > 0 Then StringResult = Replace(StringResult, chartobesearch, "", 1, 1) aa = aa + 1 End If Next i FindTheMatching = aa End Function |
Output:
Read MoreSyntax ACCRINT( issue, first_interest, settlement, rate, [par], frequency, [basis], [calc_method] ). Parameters ssue: The date the security is issued. first_interest: The date when the initial interest is paid. settlement: The settlement date of the security. rate: The annual interest rate or coupon when the security was issued. par: The par value of the security. frequency: […]
Read MoreData base name too long When you install SharePoint Central Admin database using Configuration wizard, the SharePoint Admin Content database name has a name : SharePoint_adminContent plus very long GUID numbers. Read more >> Missing Site Template Once you click Site Settings and you see no Site as template option is available. Most common reasons […]
Read MoreMacro Settings Issue Ratings: Issues Description: Excel macro is not working when the ExcelSheet is saved as Protected Password. Although, ExcelSheet has “Macro Settings” enable, yet many kinds of error pop up erroneously Problem details: 1. Enable macro settings : File -> Options -> Trust Center -> Trust Center Settings -> Enable all macro 2. […]
Read MoreReDim Preserve Ratings: ReDim Preserve is the way to make a dynamic array and maintain the values currently stored in the array by using the ReDim Preserve. If you want to resize your array while remembering all the elements in the array, you must use the Preserve keyword. However, there are some limitations: 1. Redim […]
Read MoreThis example provides convenient way to download and export job listing from internet. “Setting” page on excel gives your flexibility to minor modification on variable names in case their sites change the tag name. This example demonstrate on how to use the most common methods in the HTML DOM to manipulate, or get info from, […]
Read More