MS Excel – Ordenando pastas Alfabéticamente




Quando me refiro a pasta, estou citando as worksheets das planilhas dentro de um mesmo arquivo workbook

É habitual a utilização de diversas pastas em um mesmo arquivo do MS Excel e, por decorrência, torna-se totalmente necessária que elas estejam organizadas por nome. Geralmente ocorre o contrário: Criamos várias pastas, ordenamos algumas e deixamos outras pelo caminho. No final estamos perdidos com várias pastas perdidas em nossas planilhas.

A funcionalidade que passo a seguir visa automatizar nossa necessidade de organização. Se dermos nomes adequados às nossas pastas, esta rotina fará todo o cansativo e enfadonho serviço de organização por nós, colocando-as em ordem alfabética.

Para ter suas pastas organizadas dentro das suas planilhas, basta que cole o código abaixo em um novo módulo da planilha que deseja organizar, fazendo chamadas a ele. Estas podem ser feitas na abertura e fechamento da mesma, ou através de um comando por combinação de teclas, um botão, ou o que desejar. 

Use a imaginação!

Option Explicit

Function SheetsAlphaSort()
' Author: Date: Contact:
' André Bernardes 13/10/2008 11:07 bernardess@gmail.com
' Ordena de forma alfabética todas as pastas em uma planilha MS Excel.

Dim i As Integer
Dim j As Integer
Dim PrimPastaOrdenar As Integer
Dim UltiPastaOrdenar As Integer
Dim DescrescOrdem As Boolean

Let DescrescOrdem = False

If ActiveWindow.SelectedSheets.Count = 1 Then
'Altera o 1 para o número da pasta que deseja ordenar primeiro.
Let PrimPastaOrdenar = 1
Let UltiPastaOrdenar = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For i = 2 To .Count
If .Item(i - 1).Index <> .Item(i).Index - 1 Then
MsgBox "Não há como ordenar PASTAS não-adjacentes!"
Exit Sub
End If
Next i
Let PrimPastaOrdenar = .Item(1).Index
Let UltiPastaOrdenar = .Item(.Count).Index
End With
End If

For j = PrimPastaOrdenar To UltiPastaOrdenar
For i = j To UltiPastaOrdenar
If DescrescOrdem = True Then
If UCase(Worksheets(i).Name) > UCase(Worksheets(j).Name) Then
Worksheets(i).Move Before:=Worksheets(j)
End If
Else
If UCase(Worksheets(i).Name) < UCase(Worksheets(j).Name) Then
Worksheets(i).Move Before:=Worksheets(j)
End If
End If
Next i
Next j

End Function

Tags: VBA, Office, Excel, automation, alfabética, ordem, ordenar, ascending, descendin, order, sort, workbook, worksheet, sheet






MS Access – Exec Proc in SYBASE

O Código abaixo deve ser colado em um novo módulo do MS Access. A partir deste a função ReturnData() pode ser executada.

É verdade que uma query também precisará ser criada para a execução do código abaixo. Esta é apenas uma técnica de como obter resultados de uma Stored Procedure no Sybase que retorna dados.

'============================================================================================== ' Microsoft® Office Access developing by A&A - In Any Place. ' Copyright© A&A - In Any Place. All Rights Reserved. '==============================================================================================

Public Colmn0 As Variant Public Colmn1 As Variant Public Colmn2 As Variant Public Colmn3 As Variant Public Colmn4 As Variant Public Colmn5 As Variant Public Colmn6 As Variant Public Colmn7 As Variant Public Colmn8 As Variant Public Colmn9 As Variant Public Colmn10 As Variant Public Colmn11 As Variant

Option Compare Database

Function ReturnData() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess@gmail.com

' Retorna o conteúdo de PROC do Sybase.

Dim db As Database Dim l As Long Dim Ss As Recordset Dim StrConection As String Dim i As Integer Dim flag As Boolean Dim nTx1, nTx2, nTx3, nTx4, nTx5, nTx6 As String

Let nTx1 = [Form_frm_sys_SybaseImport].CxStoredProcedure.Value Let nTx2 = [Form_frm_sys_SybaseImport].CxDSN.Value Let nTx3 = [Form_frm_sys_SybaseImport].CxServer.Value Let nTx4 = [Form_frm_sys_SybaseImport].CxDatabase.Value Let nTx5 = [Form_frm_sys_SybaseImport].CxUser_ID.Value Let nTx6 = [Form_frm_sys_SybaseImport].CxPassword.Value

' String de conexão. Let StrConection = "DSN=" & nTx2 & ";" & _ "SRVR=" & nTx3 & ";" & _ "DB=" & nTx4 & ";" & _ "UID=" & nTx5 & ";" & _ "PWD=" & nTx6

Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, StrConection) Set rs = db.OpenRecordset("EXEC " & nTx1, dbOpenSnapshot, dbSQLPassThrough)

Let flag = True Do Until rs.EOF ' Nome dos campos. If flag Then Let j = 1

While j <= 11 'Debug.Print rs(j).Name Let j = j + 1 Wend Let flag = False End If

'a = SysCmd(acSysCmdSetStatus, Now() & " |" & rs(0).Value & rs(1).Value & rs(2).Value & rs(3).Value & rs(4).Value)

' & rs(5).Value & rs(6).Value & rs(7).Value & rs(8).Value & rs(9).Value & rs(10).Value & rs(11).Value)

'Debug.Print i, rs(0).Value, rs(1).Value, rs(2).Value, rs(3).Value, rs(4).Value, rs(5).Value, rs(6).Value, rs(7).Value, rs(8).Value, rs(9).Value, rs(10).Value, rs(11).Value

[Form_frm_sys_SybaseImport].lblStatus.Caption = "|" & Trim(Str(i)) & "| " & Now() & " |" & rs(0).Value & " |" & rs(1).Value & " |" & rs(2).Value & " |" & Trim(rs(3).Value) & " |" & rs(4).Value

[Form_frm_sys_SybaseImport].Repaint [Form_frm_sys_SybaseImport].Refresh

Let Colmn0 = Trim(rs(0).Value) Let Colmn1 = Trim(rs(1).Value) Let Colmn2 = Trim(rs(2).Value) Let Colmn3 = Trim(rs(3).Value) Let Colmn4 = Trim(rs(4).Value) Let Colmn5 = Trim(rs(5).Value) Let Colmn6 = Trim(rs(6).Value) Let Colmn7 = Trim(rs(7).Value) Let Colmn8 = Trim(rs(8).Value) Let Colmn9 = Trim(rs(9).Value) Let Colmn10 = Trim(rs(10).Value) Let Colmn11 = Trim(rs(11).Value)

' Atualiza a tabela de Recursos Humanos. DoCmd.SetWarnings (False) DoCmd.OpenQuery "qry_sys_SetPeople", acViewNormal, acAdd DoCmd.SetWarnings (True)

rs.MoveNext

Let i = i + 1 Loop

rs.Close

Set db = Nothing Let Colmn0 = Null Let Colmn1 = Null Let Colmn2 = Null Let Colmn3 = Null Let Colmn4 = Null Let Colmn5 = Null Let Colmn6 = Null Let Colmn7 = Null Let Colmn8 = Null Let Colmn9 = Null Let Colmn10 = Null

MsgBox "Atualização terminada", vbInformation, "Colaboradores importados" End Function

Function ReturnField0() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField0 = Colmn0 End Function

Function ReturnField1() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField1 = Colmn1 End Function

Function ReturnField2() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField2 = Colmn2 End Function

Function ReturnField3() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField3 = Colmn3 End Function

Function ReturnField4() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField4 = Colmn4 End Function

Function ReturnField5() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField5 = Colmn5 End Function

Function ReturnField6() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField6 = Colmn6 End Function

Function ReturnField7() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField7 = Colmn7 End Function

Function ReturnField8() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField8 = Colmn8 End Function

Function ReturnField9() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField9 = Colmn9 End Function

Function ReturnField10() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField10 = Colmn10 End Function

Function ReturnField11() ' Author: Date: Contact: ' André Bernardes 16/10/2008 07:45 bernardess @ gmail.com.

Let ReturnField11 = Colmn11 End Function

Query SQL Code:

INSERT INTO tbl_data_SetPeople ( Registro, CPF, Nome, funcao, nivel_profis, Disciplina, data_admissao, data_demissao, lotacao, razao_soci, local_trabalho )

SELECT ReturnField1() AS RE, ReturnField2() AS nCFP, ReturnField3() AS nNome, ReturnField4() AS nFuncao, ReturnField5() AS nNivelProfissional, ReturnField6() AS nDisciplina, ReturnField7() AS nDtAdmissao, ReturnField8() AS nDtDemissao, ReturnField9() AS nLotacao, ReturnField10() AS nRazaoSocial, ReturnField11() AS nLocalTrabalho

FROM tbl_sys_Add

WITH OWNERACCESS OPTION;

ANDRÉ BERNARDES Santos - SP - Brasil

MSN: bernardess@gmail.com SKYPE: inanyplace TWITTER: bernardess

My Profile: http://al-bernardes.sites.uol.com.br/ In LinkedIn: http://www.linkedin.com/in/andrebernardes In Plaxo: http://andrebernardes.myplaxo.com/ In Jobster: http://www.jobster.com/people/andrebernardes

My experiences: http://andrelbernardes.sites.uol.com.br/MIS.html http://andrelbernardes.sites.uol.com.br/XLS.html

Acesse meus Feeds: http://www.google.com/reader/shared/17429357110218427555

Acesse o portfólio: http://www.slideshare.net/bernardes/informaes-ao-invs-de-dados-presentation

MS Access – Exec Proc in ORACLE




A questão é a seguinte: Ao utilizarmos qualquer versão do MS Access (2007/2003/XP/2000/97) como interface de aplicação [front-end], acessando certas funcionalidades no banco de dados Oracle [back-end], como executo ou efetuo chamadas a uma stored procedure no Oracle (RDBMS) a partir do MS Access?

Considere: application_program_interface (member_id, provider_id, service_date)
Procedimento: Para chamar a stored procedure no Oracle, é necessário criar algum código VBA.

Antes do código VBA, precisamos de uma conexão ODBC no Windows para o seu banco de dados Oracle, usando o driver {Microsoft ODBC for Oracle}.

Para criar a conexão no Windows vá até o Painel de Controle | Data Sources ODBC e crie um novo Data Source usando o driver {Microsoft ODBC for Oracle}.

Configure a sua conexão ODBC:
Neste exemplo configuraremos o Data Source com o nome de AAAA, o usuário com o nome de BBBB, e um servidor Oracle chamado CCCC. Você precisará configurar a sua conexão ODBC com as suas próprias informações para estes parâmetros.

No exemplo abaixo chame sua stored procedure do Oracle através da application_program_interface em VBA.

No MS Access crie um novo Módulo e cole o código abaixo:
Function CallSProc() As Boolean
Dim db As Database Dim LSProc As QueryDef
On Error GoTo Err_Execute
Set db = CurrentDb()
Set LSProc = db.CreateQueryDef("")
' Utilize {Microsoft ODBC for Oracle} para a conexão ODBC LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC" LSProc.SQL = "BEGIN application_program_interface; END;" LSProc.ReturnsRecords = False LSProc.ODBCTimeout = 0
LSProc.Execute
Set LSProc = Nothing
CallSProc = True
Exit Function
Err_Execute: MsgBox "A execução da Stored Procedure no Oracle falhou." CallSProc = False 
End Function

Ei! Note que você precisará customizar o seu código adequando a linha abaixo:

LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

Isto é:

AAAA é o nome ODBC do "Data Source" que você configurou. 
BBBB é o nome do usuário que se logará no Oracle. 
CCCC é o nome do seu servidor Oracle. 
DDDD é a senha que utilizará para logar-se no Oracle.

Pode acontecer que ao tentar executar a stored procedure, receba uma mensagem similar a "not defined" error on the "Dim db as Database" declaration, neste caso precisará seguir os passos abaixo:

Na janela VBE do MS Access escolha a opção de Referências. 

Quando a janela aparecer, role para baixo até achar a opção "Microsoft DAO 3.6 Object Library", clicando a caixa e depois o botão OK. 

Agora sua aplicação MS Access reconhecerá o objeto database e a mensagem de erro desaparecerá.

Dica importante: Ao passo que é possível executar Stored Procedures do Oracle através do MS Access, não é possível recuperar informações históricas de status vindas do Oracle. Recomendo que crie uma tabela para auditoria posteriormente. Grave nela registros com informações das ações executadas através da sua interface MS Access, propiciando posteriores auditorias em seus procedimentos. Através desta solução, poderá consultar a tabela do MS Access e visualizar o status da sua stored procedure.

Stored procedure with parameters
O segundo exemplo executa uma stored procedure chamada application_program_interface com parâmetros, considere:

application_program_interface (member_id, provider_id, service_date)

Onde

member_id é um valor numérico
provider_id é um valor numérico
service_date é um valor de data

No MS Access crie um novo Módulo e cole o código abaixo:

Function CallSProc() As Boolean
Dim db As Database Dim LSProc As QueryDef Dim LSQL As String
On Error GoTo Err_Execute
Set db = CurrentDb()
Set LSProc = db.CreateQueryDef("")
' Código SQL para executar a stored procedure (com parâmetros) LSQL = "BEGIN application_program_interface (" & member_id & ", " LSQL = LSQL & provider_id & ", " LSQL = LSQL & "to_date('" & Format(service_date, "mm/dd/yyyy") & "','mm/dd/yyyy'))" LSQL = LSQL & "; END;"
' Utilize {Microsoft ODBC for Oracle} para a conexão ODBC LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC" LSProc.SQL = LSQL LSProc.ReturnsRecords = False LSProc.ODBCTimeout = 0
LSProc.Execute
Set LSProc = Nothing
CallSProc = True
Exit Function
Err_Execute: MsgBox "A execução da Stored Procedure no Oracle falhou." CallSProc = False 
End Function

Ei! Não se esqueça de customizar o seu código na seguinte linha:

LSProc.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

Isto é:

AAAA é o nome ODBC do "Data Source" que você configurou. BBBB é o nome do usuário que se logará no Oracle. CCCC é o nome do seu servidor Oracle. DDDD é a senha que utilizará para logar-se no Oracle.

Tags: VBA, Office, Access, Oracle, frontend, interface, backend, RDBMS, SGBDR, stored procedure, PROC, ODBC







MS Excel - Populando Combobox - Multi-Column



Mais uma necessidade que se nos apresenta no dia-a-dia, popular combobox em formulários.

Nível da implementação: Intermediário.
Versão em que foi testada: 2000 - 2007.
Descrição: Um combobox é disponibilizado com 10 linhas e treze colunas de informação.

Option Explicit

Private Sub UserForm_activate()
Dim MyList(10, 10) 'Definindo como array.
' O combobox neste exemplo contém 3 colunas - Implemente quantas colunas desejar

With ComboBox1
.ColumnCount = 3
.ColumnWidths = 75
.Width = 220
.Height = 15
.ListRows = 6
End With

' Definindo tanto a lista como o local de onde obter os dados (Colunas A, D, G)
With ActiveSheet
' MyList (Linha{0 to 9}, Coluna{primeira}) = (Coluna A neste exemplo) ' Não se esqueça de continuar acrescentando para LINHA e COLUNA ' iniciando do zero Não de um MyList(0, 0) = .Range("A1")
MyList(1, 0) = .Range("A2")
MyList(2, 0) = .Range("A3")
MyList(3, 0) = .Range("A4")
MyList(4, 0) = .Range("A5")
MyList(5, 0) = .Range("A6")
MyList(6, 0) = .Range("A7")
MyList(7, 0) = .Range("A8")
MyList(8, 0) = .Range("A9")
MyList(9, 0) = .Range("A10")
' MyList (Linha {0 to 9}, Coluna{segunda}) = (Coluna D neste exemplo) MyList(0, 1) = .Range("D1")
MyList(1, 1) = .Range("D2")
MyList(2, 1) = .Range("D3")
MyList(3, 1) = .Range("D4")
MyList(4, 1) = .Range("D5")
MyList(5, 1) = .Range("D6")
MyList(6, 1) = .Range("D7")
MyList(7, 1) = .Range("D8")
MyList(8, 1) = .Range("D9")
MyList(9, 1) = .Range("D10")
' MyList (Linha {0 to 9}, Coluna {Terceira}) = (Coluna G neste exemplo) MyList(0, 2) = .Range("G1")
MyList(1, 2) = .Range("G2")
MyList(2, 2) = .Range("G3")
MyList(3, 2) = .Range("G4")
MyList(4, 2) = .Range("G5")
MyList(5, 2) = .Range("G6")
MyList(6, 2) = .Range("G7")
MyList(7, 2) = .Range("G8")
MyList(8, 2) = .Range("G9")
MyList(9, 2) = .Range("G10")
End With
' Agora populamos o Combobox
ComboBox1.List() = MyList
End Sub


Como usar:
  1. Abra uma planilha MS Excel
  2. Selecione Editor Visual Basic (Tools/Macro/Visual Basic Editor)
  3. Na janela do editor VBA (VBE window), selecione Insert/UserForm
  4. Selecione ComboBox a partir da caixa de ferramentas (toolbox), cole-o no Formulário
  5. Clique o botão direito do mouse no formulário
  6. Selecione Inserir código
  7. Então copie e cole o código acima
Testando o código:
  1. Digite alguns dados nas colunas A, D e G na planilha
  2. Exiba o formulário novamente, agora verá as três colunas preenchidas no Combobox

Tags: VBA, Office, Application, Automation, field,  Plan, planilhas, column, Excel, Multi-Collumn, populando, combobox, 

MS Access - Desvinculando Tabelas




Desanexe todas as tabelas que desejar do MS Access. Pode ser uma tabela, uma planilha MS Excel ou qualquer outro objeto anexado.

Function UnLinkTmp (nTable As String)  
' Author: Date: Contact:  
' André Bernardes 24/09/2008 10:49  bernardess@gmail.com  
' Desvincula planilhas temporárias do MS Access atual. 
Dim tdf As TableDef  
Dim i As Integer  
Dim strFile As String  
Dim ThisFormName As String 

Let ThisFormName = "frmTarget" 
Let strFile = nTable  
Let i = 0 
For Each tdf In CurrentDb.TableDefs 
If tdf.Name = strFile And (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
Let [Form_"frmTarget"].LblImportStatus.Caption = "Último objeto desanexado: " & _
tdf.Name & _ 
CHR(13) & CHR(10) & "(total Desanexado(s): " & i & ")"

CurrentDb.TableDefs.Delete tdf.Name 
End If

Next tdf Set tdf = Nothing 
End Function
 
Lembre-se de trocar a expressão "frmTarget", pelo nome do formulário que utilizar para informar ao usuário o que está acontecendo (no código não pode haver aspas). Também o nome do Label "LblImportStatus", pertencente ao mesmo FORM  deve ser substituido pelo nome do Label que desejar utilizar. 

Tags: VBA, Office, automation, Access, table, unlink, desanexar, desconectar


diHITT - Notícias