PIECE OF CAKE - MS Excel - Zipando - Compacte no formato Zip usando o programa padrão do Windows - Zip file(s) with the default Windows zip program (VBA)



Copie o código abaixo num módulo padrão de sua planilha.

O código abaixo não é suportado pela Microsoft.
Não é possível ocultar a caixa de diálogo de cópia quando copia para uma pasta zip (isso só funciona com pastas normais, até onde eu sei). Também não há possibilidade de evitar que alguém possa cancelar a operação ou que seu código VBA notifique algo caso a operação seja cancelada.

Não redimensione, por exemplo, FileNameZip como String nos exemplos de código. Essa deve ser Variant, caso contrário, o código não funcionará.

Sub NewZip (sPath)
    If Len(Dir(sPath)) > 0 Then Kill sPath

    Open sPath For Output As #1

    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)

    Close #1
End Sub


Function bIsBookOpen (ByRef szBookName As String) As Boolean

    On Error Resume Next

    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Function Split97 (sStr As Variant, sdelim As String) As Variant
    Let Split97 = Evaluate("{""" & _
                       Application.Substitute(sStr, sdelim, """,""") & """}")
End Function


Veja também:



#A&A #PIECEOFCAKE #POC #VBA #RondeBruin #MS #Excel



Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛
• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 


⬛◼◾▪ CONTATO ▪

PIECE OF CAKE - MS Excel - Zipando - Abra uma Janela e Escolha os Arquivos que deseja Compacatar - Browse to the folder you want and select the file or files

Título auto-explicativo.


Sub Zip_File_Or_Files()
    Dim strDate As String, DefPath As String, sFName As String
    Dim oApp As Object, iCtr As Long, I As Integer
    Dim FName, vArr, FileNameZip

    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If

    strDate = Format(Now, " dd-mmm-yy h-mm-ss")
    FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip"

    'Browse to the file(s), use the Ctrl key to select more files
    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
                    MultiSelect:=True, Title:="Select the files you want to zip")
    If IsArray(FName) = False Then
        'do nothing
    Else
        'Create empty Zip File
        NewZip (FileNameZip)
        Set oApp = CreateObject("Shell.Application")
        I = 0
        For iCtr = LBound(FName) To UBound(FName)
            vArr = Split97(FName(iCtr), "\")
            sFName = vArr(UBound(vArr))
            If bIsBookOpen(sFName) Then
                MsgBox "You can't zip a file that is open!" & vbLf & _
                       "Please close it and try again: " & FName(iCtr)
            Else
                'Copy the file to the compressed folder
                I = I + 1
                oApp.Namespace(FileNameZip).CopyHere FName(iCtr)

                'Keep script waiting until Compressing is done
                On Error Resume Next
                Do Until oApp.Namespace(FileNameZip).items.Count = I
                    Application.Wait (Now + TimeValue("0:00:01"))
                Loop
                On Error GoTo 0
            End If
        Next iCtr

        MsgBox "You find the zipfile here: " & FileNameZip
    End If
End Sub



#A&A #PIECEOFCAKE #POC #VBA #RondeBruin #MS #Excel

Veja também:



Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛
• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 


⬛◼◾▪ CONTATO ▪

PIECE OF CAKE - MS Excel - Zipando - Escolha uma Pasta e Compacte Tudo Nela - Browse to a folder and zip all files in it


Bem, o título já descreve bem a ação deste código.

Sub Zip_All_Files_in_Folder_Browse()
    Dim FileNameZip, FolderName, oFolder
    Dim strDate As String, DefPath As String
    Dim oApp As Object

    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If

    strDate = Format(Now, " dd-mmm-yy h-mm-ss")
    FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip"

    Set oApp = CreateObject("Shell.Application")

    'Browse to the folder
    Set oFolder = oApp.BrowseForFolder(0, "Select folder to Zip", 512)
    If Not oFolder Is Nothing Then
        'Create empty Zip File
        NewZip (FileNameZip)

        FolderName = oFolder.Self.Path
        If Right(FolderName, 1) <> "\" Then
            FolderName = FolderName & "\"
        End If

        'Copy the files to the compressed folder
        oApp.Namespace(FileNameZip).CopyHere oApp.Namespace(FolderName).items

        'Keep script waiting until Compressing is done
        On Error Resume Next
        Do Until oApp.Namespace(FileNameZip).items.Count = _
        oApp.Namespace(FolderName).items.Count
            Application.Wait (Now + TimeValue("0:00:01"))
        Loop
        On Error GoTo 0

        MsgBox "You find the zipfile here: " & FileNameZip

    End If
End Sub


#A&A #PIECEOFCAKE #POC #VBA #RondeBruin #MS #Excel

Veja também:


Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛
• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 


⬛◼◾▪ CONTATO ▪

PIECE OF CAKE - MS Excel - Zipando - Compacte Todos os Arquivos Contidos na Pasta Informada no Código - Zip all files in the folder that you enter in the code


Antes de executar este código, altere a pasta na linha 
FolderName = "C: \ Bernardes \"



Sub Zip_All_Files_in_Folder()
    Dim FileNameZip, FolderName
    Dim strDate As String, DefPath As String
    Dim oApp As Object

    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If

    FolderName = "C:\Bernardes\"    '<< Change

    strDate = Format(Now, " dd-mmm-yy h-mm-ss")
    FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip"

    'Create empty Zip File
    NewZip (FileNameZip)

    Set oApp = CreateObject("Shell.Application")

    'Copy the files to the compressed folder
    oApp.Namespace(FileNameZip).CopyHere oApp.Namespace(FolderName).items

    'Keep script waiting until Compressing is done
    On Error Resume Next
    Do Until oApp.Namespace(FileNameZip).items.Count = _
       oApp.Namespace(FolderName).items.Count
        Application.Wait (Now + TimeValue("0:00:01"))
    Loop

    On Error GoTo 0

    MsgBox "You find the zipfile here: " & FileNameZip
End Sub



#A&A #PIECEOFCAKE #POC #VBA #RondeBruin #MS #Excel
Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛
• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 


⬛◼◾▪ CONTATO ▪

PIECE OF CAKE - MS Excel - Zipando - Compactando a Planilha Atual - Zip the ActiveWorkbook


Este script faz uma cópia do Activeworkbook e o compacta em "C: \ Bernardes \" com um carimbo de data e hora. Altere esta pasta ou use seu caminho padrão Application.DefaultFilePath


Sub Zip_ActiveWorkbook()
    Dim strDate As String, DefPath As String
    Dim FileNameZip, FileNameXls
    Dim oApp As Object
    Dim FileExtStr As String

    DefPath = "C:\Bernardes\"    '<< Change
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If

    'Create date/time string and the temporary xl* and Zip file name
    If Val(Application.Version) < 12 Then
        FileExtStr = ".xls"
    Else
        Select Case ActiveWorkbook.FileFormat
        Case 51: FileExtStr = ".xlsx"
        Case 52: FileExtStr = ".xlsm"
        Case 56: FileExtStr = ".xls"
        Case 50: FileExtStr = ".xlsb"
        Case Else: FileExtStr = "notknown"
        End Select
        If FileExtStr = "notknown" Then
            MsgBox "Sorry unknown file format"
            Exit Sub
        End If
    End If

    strDate = Format(Now, " yyyy-mm-dd h-mm-ss")
    
    FileNameZip = DefPath & Left(ActiveWorkbook.Name, _
    Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & ".zip"
    
    FileNameXls = DefPath & Left(ActiveWorkbook.Name, _
    Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & FileExtStr

    If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then

        'Make copy of the activeworkbook
        ActiveWorkbook.SaveCopyAs FileNameXls

        'Create empty Zip File
        NewZip (FileNameZip)

        'Copy the file in the compressed folder
        Set oApp = CreateObject("Shell.Application")
        oApp.Namespace(FileNameZip).CopyHere FileNameXls

        'Keep script waiting until Compressing is done
        On Error Resume Next
        Do Until oApp.Namespace(FileNameZip).items.Count = 1
            Application.Wait (Now + TimeValue("0:00:03"))
        Loop
        On Error GoTo 0
        'Delete the temporary xls file
        Kill FileNameXls

        MsgBox "Your Backup is saved here: " & FileNameZip

    Else
        MsgBox "FileNameZip or/and FileNameXls exist"

    End If
End Sub


#A&A #PIECEOFCAKE #POC #VBA #RondeBruin #MS #Excel
Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛
• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 


⬛◼◾▪ CONTATO ▪

PIECE OF CAKE - MS Excel - Zipando - Compactando e Enviando por e-Mail - Zip and mail the ActiveWorkbook


Este código foi escrito para que estiver usando o Outlook como seu programa de e-mail.

Este código enviará a planilha recém-criada (cópia do seu Activeworkbook). Guardará o zip na pasta de trabalho antes de enviá-lo com um carimbo de data | hora. Depois que o arquivo zip for enviado, o arquivo zip e a pasta de trabalho serão excluídos.


Sub Zip_Mail_ActiveWorkbook()
    Dim strDate As String, DefPath As String, strbody As String
    Dim oApp As Object, OutApp As Object, OutMail As Object
    Dim FileNameZip, FileNameXls
    Dim FileExtStr As String

    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If

    'Create date/time string and the temporary xl* and zip file name
    If Val(Application.Version) < 12 Then
        FileExtStr = ".xls"
    Else
        Select Case ActiveWorkbook.FileFormat
        Case 51: FileExtStr = ".xlsx"
        Case 52: FileExtStr = ".xlsm"
        Case 56: FileExtStr = ".xls"
        Case 50: FileExtStr = ".xlsb"
        Case Else: FileExtStr = "notknown"
        End Select
        If FileExtStr = "notknown" Then
            MsgBox "Sorry unknown file format"
            Exit Sub
        End If
    End If

    strDate = Format(Now, " yyyy-mm-dd h-mm-ss")

    FileNameZip = DefPath & Left(ActiveWorkbook.Name, _
    Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & ".zip"

    FileNameXls = DefPath & Left(ActiveWorkbook.Name, _
    Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & FileExtStr


    If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then

        'Make copy of the activeworkbook
        ActiveWorkbook.SaveCopyAs FileNameXls

        'Create empty Zip File
        NewZip (FileNameZip)

        'Copy the file in the compressed folder
        Set oApp = CreateObject("Shell.Application")
        oApp.Namespace(FileNameZip).CopyHere FileNameXls

        'Keep script waiting until Compressing is done
        On Error Resume Next
        Do Until oApp.Namespace(FileNameZip).items.Count = 1
            Application.Wait (Now + TimeValue("0:00:01"))
        Loop
        On Error GoTo 0

        'Create the mail
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                  "This is line 1" & vbNewLine & _
                  "This is line 2" & vbNewLine & _
                  "This is line 3" & vbNewLine & _
                  "This is line 4"

        On Error Resume Next
        With OutMail
            .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = strbody
            .Attachments.Add FileNameZip
            .Send   'or use .Display
        End With
        On Error GoTo 0

        'Delete the temporary Excel file and Zip file you send
        Kill FileNameZip
        Kill FileNameXls
    Else
        MsgBox "FileNameZip or/and FileNameXls exist"
    End If
End Sub


#A&A #PIECEOFCAKE #POC #VBA #RondeBruin #MS #Excel

Veja também:



Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛
• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 


⬛◼◾▪ CONTATO ▪

PIECE OF CAKE - Connecting to Oracle 12g with Excel VBA


Um aplicativo muito antigo ao qual presto suporte há vários anos carrega dados de planilhas do Excel em um banco de dados de relatórios. Essas planilhas do Excel sempre foram atualizadas manualmente por vários usuários. No entanto, como os dados que os usuários estão inserindo nessas planilhas são primeiro inseridos em outro banco de dados de aplicativos, esses usuários têm feito uma entrada dupla - um processo redundante que pode ser facilmente corrigido por vários meios.

Idealmente, a solução para este problema seria extrair os dados do banco de dados do aplicativo e carregá-lo no banco de dados de relatórios usando um pacote SSIS. Infelizmente, isso exigiria alguma readequação do aplicativo que carrega dados no banco de dados de relatórios, e eu (e o cliente) não possuímos largura de banda para isso. Então, encontrei uma solução rápida que deixou todos felizes - Usar um script VBA para preencher automaticamente as planilhas com dados quando os usuários as abrirem.

A parte complicada aqui foi fazer com que o Excel se conectasse ao Oracle, com um mínimo de trabalho a ser feito nos PCs dos usuários, que estão longe da minha localização.

Primeiro, não exigir que esses usuários tenham o SQL Plus ou quaisquer ferramentas de desenvolvimento para Oracle, o software de cliente completo era desnecessário.

Tenho o software Instant Client instalado nos PCs, então adicionei as informações de conexão do banco de dados necessárias ao arquivo tnsnames.ora .

Nota: no Instant Client (ou pelo menos na nossa configuração, usando a versão 11.2.0.4), o arquivo tnsnames está em 
C: \ oracle \ instantclient_11_2_0_4 em vez de em C: \ oracle \ product \ 11.2.0.4 \ client_1 \ NETWORK \ ADMIN como normalmente seria no cliente Oracle completo.


A conexão no VBA foi bastante simples, mas não imediatamente óbvia: note que a seqüência de conexão inclui Microsoft ODBC Driver for Oracle em vez de um driver Oracle; Mesmo que isso seja usado, nenhuma conexão ODBC precisa ser configurada no Administrador de Fonte de Dados ODBC. É imperativo apenas que as entradas apropriadas existam no arquivo tnsnames.ora e que a Biblioteca de Objetos de Dados Microsoft ActiveX esteja instalada e referenciada no Excel. (Adicionar Referências, navegando para Ferramentas -> Referências no editor VBA no Excel.)

Dim SQL_String As String
Dim dbConnectStr As String
Dim con As New ADODB.Connection
Dim recset As New ADODB.Recordset
Dim strUid As String
Dim strPwd As String
Dim strEnv As String
Dim strDSN As String
Dim iRow As Integer    


strEnv = "prod"
strUid = "username"
strPwd = "password"

If strEnv = "prod" Then
    Let strDSN = "(prod database net_service_name* from tnsnames)"
Else
    Let strDSN = "(dev database net_service_name* from tnsnames)"
End If
      
dbConnectStr = "Driver={Microsoft ODBC for Oracle}; " & _
        "Server=" & strDSN & ";" & _
        "uid=" & strUid & ";pwd=" & strPwd & ";"
   
con.ConnectionString = dbConnectStr    
con.Open   

Let SQL_String = "(insert SQL query here)"
       
recset.Open SQL_String, con

iRow = 0 
Do While Not recset.EOF
     'Have a loop here to go through all the fields
    Let Sheet1.Range("A" & iRow).Value = recset.Fields("colname") ' colname = Column Name from SQL query
    
    ' &c. ...

    Let iRow = iRow + 1
    recset.MoveNext
Loop

recset.Close
con.Close


#A&A #PIECEOFCAKE #POC #VBA #oracle #Connect



Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛
• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 


⬛◼◾▪ CONTATO ▪

PIECE OF CAKE - Extract Path From String



Function ExtractPathFromString(nString As String) As String

    ' © 2007-20 Alefe & Bete Processamento de Dados LTDA - ME, except where noted, all rights reserved.
    '      Author: André Bernardes
    '     Contact: andreluizbernardess@gmail.com | https://goo.gl/EUMbSe/
    ' Application: AutoGenClosing®
    ' Description: Extrai somente o path de uma string.

    Let ExtractPathFromString = Left(nString, InStrRev(nString, "\"))

End Function


#A&A #PIECEOFCAKE #POC #VBA

Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛
• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 


⬛◼◾▪ CONTATO ▪

PIECE OF CAKE - Detecta se Arquivo Existe



Function CheckFileExist (nPath As String, nFile As String) As Boolean
    ' © 2007-20 Alefe & Bete Processamento de Dados LTDA - ME, except where noted, all rights reserved.
    '      Author: André Bernardes
    '     Contact: andreluizbernardess@gmail.com | https://goo.gl/EUMbSe/
    ' Application: AutoGenClosing®
    ' Description: Detecta se um determinado arquivo está no referido Path.

    Dim obj_fso As Object

    Set obj_fso = CreateObject("Scripting.FileSystemObject")

    Let CheckFileExist = obj_fso.fileExists(nPath & "\" & nFile)

End Function


#A&A #PIECEOFCAKE #POC #VBA

Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛
• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 


⬛◼◾▪ CONTATO ▪
diHITT - Notícias