quarta-feira, setembro 14, 2016

How to export from a Excel Sheet to a PDF file


If you are needing an way to export from a Excel Sheet to a PDF file, you just need to create a macro and assign to a button.

When you press the button you will have a popup asking for the base name of the Report and then you can select a name of sheets to print to pdf.

I use 2 especial sheets to work better the print and reduce the list

FromNowOn -> This worksheet is the markup from where I will considere the sheets to print (before this one all will be ignored in the List)

Off -> If you put "off" in the name of the sheet it will be ignored also.


Sub ExportExcelSheets()

    Dim ReportName As String
    Dim i As Integer
    Dim TopPos As Integer
    Dim SheetCount As Integer
    Dim PrintDlg As DialogSheet
    Dim CurrentSheet As Worksheet
    Dim cb As CheckBox
    Dim myReportType As Variant
    Dim intPos As Integer
    Dim intStartPos As Integer
   
    Dim blnFirstAdded As Boolean
    blnFirstAdded = False
   
    Dim ReleaseName As String
    ReleaseName = ThisWorkbook.Sheets("Macros").Range("C5")
    Dim intIndexSheet As Integer
    intIndexSheet = 0
    Dim strIndexStr1 As String
    Dim strIndexStr2 As String
   
    intStartPos = -1
    ReportName = "Testing Status for "
   
    Application.ScreenUpdating = False
    myReportType = InputBox("Insert Report Type", "Report Type", "Release " & ReleaseName)
'   Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If
'   Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add
    SheetCount = 0
'   Add the checkboxes
    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
       
'       Validate if the string "off" is present
       
        intPos = -1
        intPos = InStr(1, UCase(CurrentSheet.Name), UCase("OFF"), vbTextCompare)
       
'       Defining the starting point
        If InStr(1, UCase(CurrentSheet.Name), UCase("FromNowOn"), vbTextCompare) > 0 Then
            intStartPos = CurrentSheet.Index()
        End If

'       Skip hidden sheets
        If CurrentSheet.Visible And intPos = 0 And intStartPos > -1 Then
            SheetCount = SheetCount + 1
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
            'PrintDlg.CheckBoxes(SheetCount).Text = CurrentSheet.Index & " - " & CurrentSheet.Name
            PrintDlg.CheckBoxes(SheetCount).Text = CurrentSheet.Name
            PrintDlg.CheckBoxes(SheetCount).Value = xlOn
            TopPos = TopPos + 13
        End If
    Next i

    ' PPI: Put in the first sheet for export
    Set CurrentSheet = ActiveWorkbook.Worksheets(intStartPos)
'   Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240
'   Set dialog height, width, and caption
    With PrintDlg.DialogFrame
        .Height = Application.Max _
            (68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = 230
        .Caption = "Select sheets to print"
    End With
'   Change tab order of OK and Cancel buttons
'   so the 1st option button will have the focus
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront
' ******************************
' * Export Process
' ******************************
'   Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
       
    Dim arrSheets() As String
    Dim intArrPos As Integer
    intArrPos = 1
   
    ReDim arrSheets(40)
    intArrPos = 0
       
    If SheetCount <> 0 Then
        ' Inicia o dialog para validar as sheets a imprimir
        If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                   
                    ' Array de Sheets selecionadas
                    arrSheets(intArrPos) = cb.Caption
                    intArrPos = intArrPos + 1
                   
                    If (Not blnFirstAdded) Then
                        ThisWorkbook.Sheets(cb.Caption).Select (False)
                        blnFirstAdded = True
                    Else
                        ThisWorkbook.Sheets(cb.Caption).Select (False)
                    End If
                   
                End If
            Next cb
          
            Dim intMaxArrSheetsSelected As Integer
            intMaxArrSheetsSelected = intArrPos - 1
            
            Dim arrSheetsSelected() As String
            ReDim arrSheetsSelected(intMaxArrSheetsSelected)
            For intArrPos = 0 To intMaxArrSheetsSelected
             arrSheetsSelected(intArrPos) = arrSheets(intArrPos)
            Next intArrPos
            
            ThisWorkbook.Worksheets(arrSheetsSelected).Select
          
            ' PPI: For export selected
            With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & ReportName & myReportType & " - Week (" & Format(Now(), "ww") & ") " & _
            Format(Now(), "ddmmyy") & " v" & Format(Now(), "hhmmss") & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
            End With
        End If
    Else
        MsgBox "Não existe nenhuma sheet."
    End If
    ThisWorkbook.Sheets(2).Activate
'   Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete

End Sub

segunda-feira, julho 25, 2016

How to use mySQL (create database, use database, create table, see column table) and commands


Commands list:

http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm

Sample:

Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.73-community MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)
mysql> create database sysformatdb;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sysformatdb        |
+--------------------+
3 rows in set (0.00 sec)
mysql> use sysformatdb
Database changed
mysql> CREATE TABLE employees (firstname VARCHAR(20), middleinitial VARCHAR(3),
lastname VARCHAR(35),suffix VARCHAR(3), officeid VARCHAR(10),userid VARCHAR(15),
username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),date
stamp DATE,timestamp time,pgpemail VARCHAR(255));
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
+-----------------------+
| Tables_in_sysformatdb |
+-----------------------+
| employees             |
+-----------------------+
1 row in set (0.00 sec)
mysql> show columns from employees;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| firstname     | varchar(20)  | YES  |     | NULL    |       |
| middleinitial | varchar(3)   | YES  |     | NULL    |       |
| lastname      | varchar(35)  | YES  |     | NULL    |       |
| suffix        | varchar(3)   | YES  |     | NULL    |       |
| officeid      | varchar(10)  | YES  |     | NULL    |       |
| userid        | varchar(15)  | YES  |     | NULL    |       |
| username      | varchar(8)   | YES  |     | NULL    |       |
| email         | varchar(35)  | YES  |     | NULL    |       |
| phone         | varchar(25)  | YES  |     | NULL    |       |
| groups        | varchar(15)  | YES  |     | NULL    |       |
| datestamp     | date         | YES  |     | NULL    |       |
| timestamp     | time         | YES  |     | NULL    |       |
| pgpemail      | varchar(255) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
mysql>

how to get test execution date/user in testlink 1.9.8



Testlink: creating new report: Results per tester

I was asked to create a new report in testlink , to be the same like existing report “Results per Tester per Build” but only for the latest build and to have latest results from previous builds, so for example if a test case was executed in release 1 and passed, and we are now in release 2 and it is not executed in release 2, it will come in the report as passed.
These are the steps I followed to create the report

https://amirshenouda.wordpress.com/2012/03/25/testlink-creating-new-report-results-per-tester/