VBA Excel - Encontra a palavra e destaca a Linha - Highlighting a row

| Blog Office VBA | Blog Excel | Blog Access |

Busque pela palavra na planilha.

Sub SearchWord()

Let nWord = InputBox("Digite a palavra", _

".: A&A",  "Palavra:")

If nWord = cancel Then ' Cancel

    Exit Sub

End If

Cells.Find(_

What:=nWord, _

After:=ActiveCell, _

LookIn:=xlFormulas, _

LookAt:=xlPart, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False, _

SearchFormat:=False_

).Select

MsgBox "A Palavra (" & nWord & ") foi localizada", _

vbInformation, _

".: A&A"

End Sub

Reference:

Tags: VBA, Excel, highlighting, search, row

VBA Excel - Conectando-se à distância através do IP

| Blog Office VBA | Blog Excel | Blog Access |

Talvez tenha se deparado com a necessidade de plugar-se a um Banco de Dados à distância, em outra rede, ou num servidor externo à rede que utiliza. Como fazer referência a ele utilizando uma conexão ODBC?

No exemplo a seguir temos a conexão a um banco MySQL. O código abaixo será útil nessa experiência, apenas certifique-se de utilizar a versão correta do Driver de ODBC.

Sub DeleteMySQLDatabase()

Dim Database_Name As String

Dim User_ID As String

Dim Password As String

Dim Cn As ADODB.Connection

Dim Server_Name As String

Dim SQLStr As String

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

Let Server_Name = Sheets(3).Range("B24").Value               ' IP number or servername

Let Database_Name = Sheets(3).Range("B21").Value             ' Name of database

Let User_ID = Sheets(3).Range("B25").Value                   ' ID user or username

Let Password = Sheets(3).Range("B23").Value                  ' Password

Let Tabellen = Sheets(3).Range("B22").Value                  ' Name of table to write to

Let auxilia = Sheets(1).Range("L16").Value

Let ID = Sheets(1).Cells(auxilia, "A").Value

Let SQLStr = "DELETE FROM " & Tabellen & " WHERE ID = '" & ID & "';"

Set Cn = New ADODB.Connection

Cn.Open "Driver={MySQL ODBC 3.51 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _

";Uid=" & User_ID & ";Pwd=" & Password & ";"

Cn.Execute SQLStr

Set rs = Nothing

Cn.Close

Set Cn = Nothing

End Sub

Outros artigos:

O que é ODBC?

Reference

Tags: VBA, Excel, SQL, MySQL, ODBC, connect, Database, Banco de Dados, conexão, server, servidor, IP

VBA Excel - alterando os Tipos de um Gráfico - ChartType Property

Termo de Responsabilidade

Alguns dos Dashboards desenvolvidos no MS Excel, permitem que os usuários mudem o tipo de gráfico visualizado. Isso é muito produtivo quando contribui para a compreensão dos dados. Mas, é importante lembar que alguns tipos de gráficos não ficam disponíveis para as PivotChart Report.

Seria útil saber quais são as propriedades (do tipo constantes), que ChartType do objeto ChartObjects suporta:

xlLine. Line

xlLineMarkersStacked. Stacked Line with Markers

xlLineStacked. Stacked Line

xlPie. Pie

xlPieOfPie. Pie of Pie

xlPyramidBarStacked. Stacked Pyramid Bar

xlPyramidCol. 3D Pyramid Column

xlPyramidColClustered. Clustered Pyramid Column

xlPyramidColStacked. Stacked Pyramid Column

xlPyramidColStacked100. 100% Stacked Pyramid Column

xlRadar. Radar

xlRadarFilled. Filled Radar

xlRadarMarkers. Radar with Data Markers

xlStockHLC. High-Low-Close

xlStockOHLC. Open-High-Low-Close

xlStockVHLC. Volume-High-Low-Close

xlStockVOHLC. Volume-Open-High-Low-Close

xlSurface. 3D Surface

xlSurfaceTopView. Surface (Top View)

xlSurfaceTopViewWireframe. Surface (Top View wireframe)

xlSurfaceWireframe. 3D Surface (wireframe)

xlXYScatter. Scatter

xlXYScatterLines. Scatter with Lines.

xlXYScatterLinesNoMarkers. Scatter with Lines and No Data Markers

xlXYScatterSmooth. Scatter with Smoothed Lines

xlXYScatterSmoothNoMarkers. Scatter with Smoothed Lines and No Data Markers

xl3DArea. 3D Area

xl3DAreaStacked. 3D Stacked Area

xl3DAreaStacked100. 100% Stacked Area

xl3DBarClustered. 3D Clustered Bar

xl3DBarStacked. 3D Stacked Bar

xl3DBarStacked100. 3D 100% Stacked Bar

xl3DColumn. 3D Column

xl3DColumnClustered. 3D Clustered Column

xl3DColumnStacked. 3D Stacked Column

xl3DColumnStacked100. 3D 100% Stacked Column

xl3DLine. 3D Line

xl3DPie. 3D Pie

xl3DPieExploded. Exploded 3D Pie

xlArea. Area

xlAreaStacked. Stacked Area

xlAreaStacked100. 100% Stacked Area

xlBarClustered. Clustered Bar

xlBarOfPie. Bar of Pie

xlBarStacked. Stacked Bar

xlBarStacked100. 100% Stacked Bar

xlBubble. Bubble

xlBubble3DEffect. Bubble with 3D effects

xlColumnClustered. Clustered Column

xlColumnStacked. Stacked Column

xlColumnStacked100. 100% Stacked Column

xlConeBarClustered. Clustered Cone Bar

xlConeBarStacked. Stacked Cone Bar

xlConeBarStacked100. 100% Stacked Cone Bar

xlConeCol. 3D Cone Column

xlConeColClustered. Clustered Cone Column

xlConeColStacked. Stacked Cone Column

xlConeColStacked100. 100% Stacked Cone Column

xlCylinderBarClustered. Clustered Cylinder Bar

xlCylinderBarStacked. Stacked Cylinder Bar

xlCylinderBarStacked100. 100% Stacked Cylinder Bar

xlCylinderCol. 3D Cylinder Column

xlCylinderColClustered. Clustered Cone Column

xlCylinderColStacked. Stacked Cone Column

xlCylinderColStacked100. 100% Stacked Cylinder Column

xlDoughnut. Doughnut

xlDoughnutExploded. Exploded Doughnut

xlLineMarkers. Line with Markers

xlLineMarkersStacked100. 100% Stacked Line with Markers

xlLineStacked100. 100% Stacked Line

xlPieExploded. Exploded Pie

xlPyramidBarClustered. Clustered Pyramid Bar

xlPyramidBarStacked100. 100% Stacked Pyramid Bar

Segue exemplo de como usar essas constantes:

Este exemplo define o tamanho do gráfico de Bolhas num grupo com 200% do tamanho padrão, caso o gráfico esteja no padrão Bolhas 2D.

With Worksheets(1).ChartObjects(1).Chart      If .ChartType = xlBubble Then          .ChartGroups(1).BubbleScale = 200      End If  End With

Reference:

Tags: VBA, Excel, chart, dynamic, Chart Object, Series, Type, Property, chart, Gráfico, tipo, série, objeto , Bolhas, Bubbles

VBA Outlook - Pesquise todos os e-mails no Outlook e extraia informações - Browse through all emails in Sub Folders of Specific Folder in Outlook and extract the information

Termo de Responsabilidade

Efetuar um loop em todos os e-mails disponíveis na sua estrutura do MS Outlook deve ser muito útil em algumas circunstâncias de identificação.

Dim oitem As Outlook.MailItem

Sub browse_all_emails_in_all_subfolders_of_specific_folder()

'Tools Reference Microsoft Outlook 

Dim olapp As Outlook.Application

Dim olappns As Outlook.Namespace

Dim oinbox As Outlook.Folder

Dim oFolder As Outlook.MAPIFolder

'tools->refrence->microsoft outlook

Set olapp = New Outlook.Application

Set olappns = olapp.GetNamespace("MAPI")

Set oinbox = olappns.GetDefaultFolder(olFolderInbox)

Set oinbox = oinbox.Folders("Ashish")

For Each oitem In oinbox.Items

' u can add if condtions to filter the emails etc.

' u can add data it to excel sheet or database table

MsgBox "Mail Subject -> " & oitem.Subject

MsgBox "Sender Email Address -> " & oitem.SenderEmailAddress

MsgBox "Sender Name -> " & oitem.SenderName

MsgBox "Mail Body -> " & oitem.Body

MsgBox "Recived Date -> " & oitem.ReceivedTime

MsgBox oinbox.Name

MsgBox oinbox.FolderPath

Next

For Each oFolder In oinbox.Folders

Call subfolders_go(oFolder)

Next

End Sub

Private Sub subfolders_go(oParent As Outlook.Folder)

Dim oFolder1 As Outlook.MAPIFolder

For Each oitem In oParent.Items

' u can add if condtions to filter the emails etc.

' u can add data it to excel sheet or database table

MsgBox "Mail Subject -> " & oitem.Subject

MsgBox "Sender Email Address -> " & oitem.SenderEmailAddress

MsgBox "Sender Name -> " & oitem.SenderName

MsgBox "Mail Body -> " & oitem.Body

MsgBox "Recived Date -> " & oitem.ReceivedTime

MsgBox oParent.Name

MsgBox oParent.FolderPath

Next

If (oParent.Folders.Count > 0) Then

For Each oFolder1 In oParent.Folders

Call subfolders_go(oFolder1)

Next

End If

End Sub

Reference: Excelvbamacros.com

Tags: VBA, Outlook, folder, loop, extract