Excel VBA - Easy and Fast

SQL Server 2008 Connection

2016-12-20T08:28:00.000-08:00

With Windows authentication
Global cnDB As ADODB.Connection
Public Function Connect_DB() As Boolean
Dim strConnString AS String

On Error GoTo Err
Connect_DB= False
strConnString = ""
strConnString = "Provider=SQLOLEDB; Initial Catalog=yourDbName;Data Source=yourServerName"

Set cnDB= CreateObject("ADODB.Connection")
cnDB.ConnectionString = strConnString
cnDB.ConnectionTimeout = 0 'no time limit

If cnDB.State <> 1 Then
cnDB.open
End If

Connect_DB= True

Err:
Exit Function
MsgBox "Error number : " & Err.Number & " - " & Err.Description, vbCritical, "Error Title"
End Function

With SQL Server authentication
Global cnDB As ADODB.Connection
Public Function Connect_DB() As Boolean
...
strConnString = "Provider=SQLOLEDB; Initial Catalog=yourDbName; Data Source=yourServerName; User ID=yourUserName; Password=yourPassword"
...
End Function

Others Connection String references:
https://www.connectionstrings.com/

MS Excel to seek jobs in Craigslist and Monster website

2016-11-23T21:55:00.001-08:00

VBA and Excel to seek jobs in Craigslist and Monster websites

This VBA program allows you to search for jobs in most important of cities located in Canada (more than 50 cities) and the USA (more than 400 cities) at once using Google Chrome accessing Craigslist and Monster. Some changes can be made to perform the search in other countries and cities as well..

Example:
1 - Initial screen

2 - Choose the sites

3- Process finished:

4-Result:

Step-by-step:

Step 1: Check the Chrome.exe location in your computer.

Step 2: Select with “x” in coll “Execute” (A) the cities that will be searched by the program

Step 3: Type the skills, jobs, title, etc.

Step 4: Click on the button ‘Search’ ou press CTRL+J.
Step 5: Choose which site you'll send/get the information.

The file is available and can be downloaded from Google drive from the link:

The Canada file (Craigslist and Monster):
https://drive.google.com/drive/folders/0B5Lm5dsdSPYacUZzVXQyWE5haXc?usp=sharing

The United States file (just Craigslist)
https://drive.google.com/drive/folders/0B5Lm5dsdSPYaTDRiNEhoWHVaTUE

Firebird database connexion

2016-11-23T08:56:00.001-08:00

Function to connect to a Firebird database (Connect_DB) and close it as well using object ADO.

'ADO Variable Global
Global oADOConn As ADODB.Connection

'Set the connexion with a dbase
Function Connect_DB () As Boolean
Dim strIP As String, strFolder As String, strDB As String, strUser As String, strPassWord As String, strConn As String

On Error GoTo Erro
strIP = "": strFolder = "": strDB = "": strUser = "": strPassWord = "": strConn = ""
Connect_DB = False
strIP = "localhost"
strFolder = "C:\Your\Database\Folder"
strDB = "DbName.FDB"
strUser = "SYSDBA" 'you user
strPassWord = "masterkey" 'your password

strConn = "Driver={Firebird/InterBase(r) driver};Dbname=" & strIP & ":" & strFolder & "\" & strDB & ";"

If oADOConn Is Nothing Then
Set oADOConn = New ADODB.Connection
End If
If oADOConn.State = 0 Then
oADOConn.Open strConn, strUser, strPassWord
End If

Connect_DB = True

Exit Function

Erro:
MsgBox "Error number : " & Err.Number & vbNewLine & _
"Description : " & Err.Description, vbCritical
End Function

'Close the connexion
private Sub Close_conexao()
oADOConn.Close
End Sub

2015-12-19T00:23:00.000-08:00

App Android

For those who love animals and want to test your knowledge about the names of them.
Here's an app for android that I developed recently.

Also very good for kids.

https://play.google.com/store/apps/details?id=com.barbara.alicia.mariana.easyenglishvocabulary

Windows Silent Install (Microsoft Windows Installer)

2015-12-17T17:17:00.004-08:00

Silent Install
'Uninstall app:
msiexec /uninstall {register app number} /qn

'Install app:
app.exe /s /v /qn

Parameters:
/ s
Hides the startup window.
/ l #### (Optional)
Configure the localized language for the installer. If this option is not specified, English is the default language. If this option is specified, make sure that there is no space between the flag and the language code. Language codes have four digits.
/ v
Transmits the following installation options for Microsoft Windows Installer:
/ qn
Run the installer in silent mode.
/ l
log_name

'Using VBScript
'******************** 'Example Scrip VBS' 'Begin '*****************
Dim oShell
Set oShell = WScript.CreateObject("WSCript.shell")
'Uninstall app:
oShell.run "msiexec /uninstall {register app number} /qn"
'Install app:
oShell.run "app.exe /s /v /qn"
set oShell = nothing WScript.echo("Finished!")
'***** 'End Script
'*****

VBS Script : App installation folder

2013-09-16T07:15:00.003-07:00

App installation folder
'Picking up the installation folder of a particular program, eg: Excel
Dim WSHShell, strAppPath
Set WSHShell = Wscript.CreateObject("Wscript.Shell")

strAppPath = WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\Excel.exe\")

WScript.echo ("Excel path installation: " & strAppPath)Set WSHShell = nothing

VBS Script Special Folders

2013-09-16T06:41:00.005-07:00

Using Visual Basic Script (VBS) to execute routines / automation without the need VBA macro. The code below shows the stores and (s) folder (s) special (s) of Windows.
The VBS script files are saved with the extension .VBS
'*******Begin of the code
'Declare Variables
Dim strFolder, objFolders

'*** Set the Special Folders to "objFolders"
Set objFolders = CreateObject("WScript.Shell").SpecialFolders

'Folder Application Data
strFolder = objFolders("AppData")
WScript.echo (strFolder)

'Loop to visualize others folders
WScript.echo (objFolders.count)
for i = 0 to objFolders.count -1
'* translated
WScript.echo ("Special forder" + objFolders.item(i))
next
Set objFolders= nothing
'************************** End

/**** Special folders' references
'AppData; AllUsersDesktop; AllUsersStartMenu; AllUsersPrograms;
AllUsersStartup; Desktop; Favorites; Fonts; MyDocuments; NetHood;
PrintHood; Programs; Recent; SendTo; StartMenu; Startup; Templates;
***********************************/

Testa variável: String, len, "On Error"

2012-08-09T07:36:00.000-07:00

Estava pesquisando sobre a variável do tipo String ... cujo tamanho máximo é de ~2 bilhões de caracteres (fonte Help do Excel)

Mas achei que 2 bilhões de caracteres é muito para essa variável. Então resolvi criar uma macro pra verificar esse tamanho. O teste foi feito em uma máquina com 500Gb de HD, 3Gb de RAM e Window7.

O resultado obtido foi de 265.217.278 (duzentos e sessenta e cinco milhões ...) caracteres, quase oito vezes menor (8 bytes por caracter). Se alguém quiser fazer esse teste, segue o código da macro:

'***Procedimento VBA excel para testar o tamanho máximo de uma string
Sub Maximo_tamanho_de_string()
Dim vStr As String
Dim vTam As String
vStr = "A"
vTam = Len(vStr)

On Error Resume Next

While Err.Number = 0
vStr = vStr & vStr ' A cada loop o tamanho dobra
vTam = Len(vStr)
Wend
MsgBox "O Tamanho da String (valor parcial) é : " & Format(vTam, "###,###,###.00")

Err.Number = 0

'** Após o primeiro erro, testar adicionando 10 em 10 milhoes de caracteres a variável
While Err.Number = 0
vStr = vStr & Mid(vStr, 1, 10000000)
vTam = Len(vStr)
Wend

MsgBox "O Tamanho da String no momento do erro foi : " & Format(vTam, "###,###,###.00")

End Sub