Import from MS Access

Export 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

[…]

Read More

Export To SQL

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

    Ratings:

Read More

Import From SQL Server

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  

    Ratings:

Read More

Outlook Automation

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 […]

Read More

PowerShellDetail

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

  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 More

Binding

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

    Ratings:

Read More

Class

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

  Class

  Ratings:

Read More

Collection

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 […]

Read More

Last Row and Column

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 […]

Read More

Reference

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 […]

Read More

VBA Constants

VBA Constants Ratings: Intrinsic Constants 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. Button Constant Value Description vbOKOnly 0 Display OK button only. vbOKCancel 1 Display OK and Cancel buttons. vbAbortRetryIgnore 2 Display Abort, Retry, […]

Read More

Strange Number

Strange 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

Output:

Read More

ACCPRINT

Syntax 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 More

SharePoint Issues

Data 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 More

Issues

Macro 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 More

Redim Preserve

ReDim 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 More

Web Automation

This 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