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


Inline image 2
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 |
Inline image 1

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:



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

Inline image 2

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

Inline image 1

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



diHITT - Notícias