Month: December 2018

Copy entire Word content in Excel, one paragraph by one

Ok, I had to deal with a Word document consisting in normal paragraphs and tables, and I wanted it on Excel. But tables have several columns while paragraph is just monocolumn, so I needed to get them separated but with some order. Here is the initial macro to separate contents: 
Option Explicit

Sub WordImport()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wdParag As Word.Paragraph
    Dim wdTable As Word.Table
    Dim wdRow As Word.Row
    Dim wdColumn As Word.Column
    Dim aWidth() As Single
    Dim oWsh As Excel.Worksheet
    Dim wdRng As Word.Range
    Dim wdRngTable As Word.Range
    
    Set wdApp = GetObject(, "Word.Application")
    'Set wApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.ActiveDocument
    'Set wDoc = wApp.Documents.Open("C:\File.docx", ReadOnly:=True)

    Dim lgR As Long: lgR = 0
    Set oWsh = ActiveSheet
    With wdDoc
        
        Set wdRng = .Paragraphs(1).Range
        Do While wdRng.Paragraphs(1).Range.End <> .Range.End
            If Not wdRng.Information(wdWithInTable) Then
                'MsgBox wdRng.Text
                wdRng.Collapse wdCollapseEnd
                wdRng.MoveEnd wdParagraph, 1 'move one paragraph in text
                'Call sParagraphPaste(wdParag, oWsh, lgR)
            Else
            ' We have reached a table...
                Set wdRngTable = wdRng.Tables(1).Range
                
                'For Each wdColumn In wdRng.Tables(1).Columns
                '    Merge columns until oCell.Width >= wdColumn.Width
                'Next wdColumn
                
                ' Move to external procedure...
                Set oWsh = ThisWorkbook.Worksheets.Add
                Call sTablePaste(wdRngTable, oWsh, lgR)
                
                wdRngTable.Collapse wdCollapseEnd ' so initial and end points are equal
                wdRngTable.MoveEnd wdParagraph, 1
                Set wdRng = wdRngTable.Paragraphs(1).Range
            End If
        Loop 'Until wdRng.End = .Range.End
        
        'Check the last paragraph
        If Not wdRng.Information(wdWithInTable) Then
            'MsgBox wdRng.Text
        End If
        
    End With

    'wDoc.Close
    'wApp.Quit
End Sub

Private Sub sTablePaste(ByVal wdRngTable As Word.Range, _
                        ByVal oWsh As Excel.Worksheet, _
                        ByRef lgR As Long)
    
    'For Each wdTable In .Tables
    'Next wdTable
    
    'For Each wdParag In .Paragraphs
        'If wdParag.Range.Words.Count > 1 Then
            wdRngTable.Copy
            With oWsh
                .Range("A1").Offset(lgR, 0).Activate
                .Paste
                lgR = lgR + lgR
            End With
        'End If
    'Next wdParag
End Sub

Private Sub sParagraphPaste(ByVal wdParag As Word.Paragraph, _
                            ByVal oWsh As Excel.Worksheet, _
                            ByRef lgR As Long)
    'For Each wdParag In .Paragraphs
        'If wdParag.Range.Words.Count > 1 Then
            wdParag.Range.Copy
            With oWsh
                .Range("A1").Offset(lgR, 0).Activate
                .Paste
                lgR = lgR + lgR
            End With
        'End If
    'Next wdParag
End Sub
And there is something more coming here, as we need to autofit rows to content, and they may be merged, so new problem. A little piece of code to solve it (from www.thesmallman.com, and contextures blog). The Small Man explores some handy alternatives to this topic in the workbook on that post. 
Sub MergedAreaRowAutofit()
    Dim j As Long
    Dim n As Long
    Dim i As Long
    Dim MW As Double 'merge width
    Dim RH As Double 'row height
    Dim MaxRH As Double
    Dim rngMArea As Range
    Dim rng As Range
    
    Const SpareCol As Long = 26
    Set rng = Range("C10:O" & Range("C" & Rows.Count).End(xlUp).Row)
    
    With rng
        For j = 1 To .Rows.Count
            'if the row is not hidden
            If Not .Parent.Rows(.Cells(j, 1).Row).Hidden Then
                'if the cells have data
                If Application.WorksheetFunction.CountA(.Rows(j)) Then
                    MaxRH = 0
                    For n = .Columns.Count To 1 Step -1
                        If Len(.Cells(j, n).Value) Then
                            'mergecells
                            If .Cells(j, n).MergeCells Then
                                Set rngMArea = .Cells(j, n).MergeArea
                                With rngMArea
                                    MW = 0
                                    If .WrapText Then
                                        'get the total width
                                        For i = 1 To .Cells.Count
                                            MW = MW + .Columns(i).ColumnWidth
                                        Next
                                        MW = MW + .Cells.Count * 0.66
                                        'use the spare column
                                        'and put the value,
                                        'make autofit,
                                        'get the row height
                                        With .Parent.Cells(.Row, SpareCol)
                                            .Value = rngMArea.Value
                                            .ColumnWidth = MW
                                            .WrapText = True
                                            .EntireRow.AutoFit
                                            RH = .RowHeight
                                            MaxRH = Application.Max(RH, MaxRH)
                                            .Value = vbNullString
                                            .WrapText = False
                                            .ColumnWidth = 8.43
                                        End With
                                        .RowHeight = MaxRH
                                    End If
                                End With
                            ElseIf .Cells(j, n).WrapText Then
                                RH = .Cells(j, n).RowHeight
                                .Cells(j, n).EntireRow.AutoFit
                                If .Cells(j, n).RowHeight < RH Then .Cells(j, n).RowHeight = RH
                            End If
                        End If
                    Next
                End If
            End If
        Next
        .Parent.Parent.Worksheets(.Parent.Name).UsedRange
    End With
End Sub
Hope you enjoy it Subscripts: ₀₁₂₃₄₅₆₇₈₉₊₋₌₍₎ Superscripts: ⁰¹²³⁴⁵⁶⁷⁸⁹⁺⁻⁼⁽⁾ 
Sub TestScriptness()
    Dim wdDoc As Word.Document
    Dim aWords As Word.Words
    Dim myRange As Word.Range
    Dim myNext As Word.Range

    Set wdDoc = ThisDocument 'ActiveDocument
    
    For Each myRange In ThisDocument.Words
        If myRange.Text Like "*[a-z,A-Z]#[0-9,a-z,A-Z]*" Then
            Set myNext = myRange.Next(Unit:=wdWord, Count:=1)
Stop
            Call FormatScript(myRange, myNext)
        End If
Exit For
        myNext.Select
        Stop
        myNext = myRange
    Next myRange
End Sub

Public Sub FormatScript(ByVal mySource As Word.Range, _
                        ByRef myDestination As Word.Range)
    Dim myChr As Word.Range
    Dim myChrDest As Word.Range
    Dim lgChr As Long
    'Do
        lgChr = 0
        For Each myChr In mySource.Characters
            lgChr = lgChr + 1
            With myChr
                If lgChr <= myDestination.Characters.Count Then
                    Set myChrDest = myDestination.Characters(lgChr)
                End If
                If .Font.Superscript = True Then
                    ' for LaTeX:
                    '.Font.Superscript = False: .InsertBefore "^"
                    myChrDest.Font.Superscript = True
                End If
                
                If .Font.Subscript = True Then
                    ' for LaTeX:
                    '.Font.Subscript = False: .InsertBefore "_"
                    myChrDest.Font.Subscript = True
                End If
            End With
        Next
        'Set myRange = myRange.NextStoryRange
    'Loop Until myRange Is Nothing
End Sub

Number of pages of Excel worksheet

There are a handful of functions on the Excel core, but none of them can retrieve the number of pages a worksheet has. It could be done with old versions via the  Excel 4.0 macros (XLM) GET functionality (but for me, no more). You can also solve this via VBA macro, which will not be my preferred option since UDT functions have a poor performance in Excel (in this case, specially terrible performance, everything that has to deal with PrintArea is extremelly slow). Here are some links to do the task recalling to Excel Macro 4.0: But I couldn’t make them to work and finally gave up, so I needed a macro, tunned convenientely to show number of pages, total in vertical or total in horizontal: 
Public Function fPages(ByVal Target As Excel.Range, _
                       Optional ByVal bVertical As Boolean = True, _
                       Optional ByVal bHorizontal As Boolean = True) As Long
    Dim oWsh As Excel.Worksheet
    Dim lgHpBreaks As Long
    Dim lgVBreaks As Long
    
    Set oWsh = Target.Parent
    With oWsh
        '.DytisplayAutomaticPageBreaks = False
        '.UsedRange.EntireRow.Hidden = True
        '.UsedRange.EntireColumn.Hidden = True
        '.Range(.PageSetup.PrintArea).EntireRow.Hidden = False
        '.Range(.PageSetup.PrintArea).EntireColumn.Hidden = False
        lgHpBreaks = .HPageBreaks.Count + 1
        lgVBreaks = .VPageBreaks.Count + 1
        '.UsedRange.EntireRow.Hidden = False
        '.UsedRange.EntireColumn.Hidden = False
    End With
    
    fPages = IIf(bVertical, lgVBreaks, 1) * IIf(bVertical, lgHpBreaks, 1)  
End Function
But this function do not autorecalculates… so you better use combined with a function that it does (the Now() function will perform good). If you use it in the worksheet, go for: 
=fPages(A1,True,True)+(NOW()*0)

WordPress plugins

I’m getting myself in this world of WP, and starting to look at plugins. There are thousands of them for WP, so I can barely know which one to use. It’s a good idea to rely on someone that can guide you here. I got my leaders, aside from my own work digging in the plugin search engine, this post was very profuse. You must note that the use of plugins has its own handicaps, very well exposed here. For the best perfomance, and to avoid the “too much query noise” associated with the plugins, it’s highly recomended to use a Cache plugin. To test the perfomance, go here. Finally, would you like how to create a plugin, then read this one. An spanish blog with a good course on WP, and appifier plugins page. If at any time you want to migrate to other hoster, the best option you can take is the Duplicator plugin, that will do the job quite simple and straight forward. Look at this youtube video.

Can VMware has “own” mouse and keyboard?

This is the first point to start.

 VMWare tech support tells to add the following line to the Workstation VMX file:

vmmouse.present = "FALSE"

It seems that the VMware bios doesn`t have support for USB keyboards – the keyboard isn`t useable on early boot. maybe you can add support for HID keyboard in the VMware bios?

some information on that:

http://www.microsoft.com/whdc/archive/Lf.mspx

http://www.microsoft.com/whdc/device/input/w2kbd.mspx#EQB

Cell Selection of No blank cells via specialcells (xlCellTypeConstants + xlCellTypeFormulas)

It’s a bit frustrating if you have to select any NoBlank cells (both xlCellTypeConstants and xlCellTypeFormulas) using the SpecialCells Method, as there is not one constant that handles both of them, and it goes throwing errors if any range Is Nothing. So here is the code to properly select no blank cells. You can also combine with Selection range and with formated cells to refine more the selection.

Continue reading “Cell Selection of No blank cells via specialcells (xlCellTypeConstants + xlCellTypeFormulas)”

Excel to App (with VBA macros)

This is a continuation (or better, a prologue) of this post, where I’m trying to accomplish an “Excel to web App” solution that can handle macros (VBA to JavaScript). It’s only shown a little piece of code on how to get an “If” block to JS, but there is more done waiting to create an online service to be published. Continue reading “Excel to App (with VBA macros)”

LEAN

Introduction

The Lean Production presents an alternative vision of conventional production, introducing a novel approach that is taking more strength every day worldwide. This new perspective, based on the Toyota Production System (TPS), addresses the causes of many of the problems that limit efficiency, focusing on the reduction of losses along the productive flow, minimizing waste and adding systematically value the manufacturing process. I’ll not bother you about the history of the LEAN philosophy, you can check it in Wikipedia article. The philosophy behind LEAN is the ‘Total Productive Maintenance’ or ‘Total Productive Manufacturing’, and for TOTAL, meaning ‘total participation’ – that’s, everyone in the company – playing an active role in productive flow, adding value to the organization by developing its own staff and associated (suppliers and subcontractors) and continuously solve root problems generating organizational learning. The goal of the system is to eliminate “waste” or “loss” (Muda in Japanese). I would recomend the following site to get more information about LEAN and TPM.

BIM

Introduction

BIM (Building Information Modelling, or let’s say it, Management) is the “digital” language with which the Construction sector should be able to produce more efficiently. It will cause a deep digitalization of all procedures and stages, and implies a interdisciplinary collaboration, supported by new technologies, that can bring more efficiency in the job done. So, it’s very likely to be the most important revolution that this sector has experienced. Digitization gives the opportunity to bring, to the Construction sector, values of industrial production that allow access to higher levels of quality, control, traceability, productivity and efficiency, which should help achieve a more sustainable activity. But don’t get me wrong, the use of a specific technology is not, by itselft, a guarantee to a better work; regardless of the technology they use, the work depends on the professionals using it, and how they use it. One final thing to note, with which we have started. BIM stands for:
  •  “Building” (or “construction”, and even the action of “build”), 
  • “Information”, relating it with classified, stored data, with the capacity to be transmitted and later processed with computer technology.
  • “Modelling”, relating to a 3D representation model or simplified idealization of something. But we must consider here that BIM methodology, in the end, is more related to the global management of the whole “building”, throughout the phases of life (planning, project, construction and exploitation).
The digital representation must comprehend both the physical and the functional characteristics of the facility. It must serve as a knowledge resource for information about a facility forming a reliable basis for decisions during its life-cycle from conception to demolition. It must let insert, extract, update or modify information in the BIM to support and reflect the roles of the stakeholders involved to help achieve the best possible efficiency, so it enables those who interact with the model to optimize their actions, resulting in a greater whole life value for the asset.

Current situation

The Construction sector has some peculiarities:
  • Low productivity indexes.
  • Significant specific weight in the economy of any country.
  • Mobilizes a very significant and diversified workforce.
  • Operates in a very sequential manner, with agents that intervene with very heterogeneous levels of interconnection.
All this leads to each stakeholder having their own interests, and will hardly align them with the other agents involved. In addition, legislation in the public sphere seems to promote this way of working. It faces several requirements of a more digital world:
  • Increasingly demanding regulations
  • Complex technical facilities
  • Need to reduce energy needs
  • Introduction of ecodesign
  • Introduction of sensorics and efficient management
  • Reduction of environmental impacts
  • Greater demand for quality
  • Elimination of unforeseen extra costs
  • Adjusted control of temporary schedules
And it must face them adapting the way things are done with the help of new technological possibilities in a methodology that BIM can represent. BIM clearly has to sides, one technological , another one of process.
  • From the technological point of view, we have to see it as a set of software applications and information hosting systems that can allow more efficient production work. New technologies tools arise in a linear way, and are usually adopted continuously without producing large jumps in the way of operating. 
  • From the point of view as a process, it supposes the establishment of a work of collaboration between the diverse interested parties, that covers all the cycle of life, being based on the new software and the technologies of communication. The change in a process is an always more disruptive factor.
The sector is very impervious to changes and investment in technology leading to the automatization or optimization of process. The reasons must be sought in:
  • the scarce industrialization of processes,
  • the atomization of industry,
  • poorly collaborative work systems,
  • poorly qualified labor,
  • a difficult capitalization of knowledge acquired in daily practice,
  • the rigidity of contracting systems,
  • sequentiality,
  • etc.
The sector has the opportunity to carry out the revolution of incorporating new technologies of manufacturing, models of management and business, collaborative forms of greater intensity, using the digitization as a thread.

Sequentiality

There is an inefficiency when each agent enters and leaves the project at a specific time, having had a very low level of dialogue with respect to the other agents. If they do not dialogue, they probably can not contrast the different criteria in early phases, when it is easy to make decisions. Worse still, if the time they are tied to the project is short, they will never get a global vision that can lead to better options. BIM_ActualWorkflow It’s needed a more imbricated implication of the stakeholders, with the intensity representing their role in the project, based on their experience, in the initial stages of development when decisions are more efficient and involve a lower cost. Introducing changes on the fly in the construction phase is inefficient, and can derive in clashes not well pondered.

Integrated Project Delivery

The IPD (Integrated Project Delivery) methodology. Is about making decisions based on the experience of the different agents, in the initial stages of development of a project, just when making decisions, and changes in criteria, are more efficient and involve a lower cost. BIM_IDP_MacLeamy

The alignment of interests

A certain cultural change is desirable, in which the different agents understand the need to work as a team that aims for a common goal. This is a cultural shift hard to achieve, as business practice is conducted by each agent seeking its own maximum economic benefit, that does not have to favor the project considered globally. Therefore, each stakeholder applies resources and strategies that, even being favorable to their particular interest, it can be detrimental to other agents, or even to the final outcome of the project. To overcome this difficulty, the problem of sequentiality must be solved, forcing the different agents to collaborate during the different phases of the project. Ideally this could be achieved by the parties understanding that they will achieve a greater particular benefit by acting together than acting separately; or, directly, forcing collaboration. Current business models, and hiring, clash with this idea.

The complete life-cycle

All the stakeholders should consider the project as the sum of all the phases through which an asset passes: Planning, Design, Construction, Exploitation and End of useful life, and not only taking to consideration the part in which they are affected and bounded to it. BIM_Lifecycle BIM_Lifecycle_applications.jpg

BIM benefits:

  • Information about all the elements can be used throughout the building lifecycle from construction to maintenance.
  • Provides a 3D model that serves as a centralized location for all the parties involved to put in and pull out the information.
  • Provides a 4D model that is used for scheduling during construction, modification and maintenance phase.
  • Provides a 5D model to reduce the expenses and maintenance costs.
  • Provides a 6D model to answer all the questions like who, what, when, and how of the different aspects of the building lifecycle.

Benefits of BIM for facility management:

  • Space Management: BIM gives a proper know-how of the space used thus increasing the scope of reducing vacant spaces. Ultimately, it reduces the expenses required for maintenance.
  • Efficient Maintenance: Maintenance of information becomes tough when a large amount of data is involved in big projects. BIM eliminates months of work by providing a streamlined maintenance of the information.
  • Efficient Energy Usage: Through BIM, facility managers can avoid negative impacts on the environment by helping find energy alternatives. Hence, it optimizes the building performance.
  • Affordable Renovations: BIM Services help in providing better information on the existing state of the project. Hence, reducing the complexity, time and money required to renovate it.
  • Lifecycle Management: BIM supports an in-depth knowledge of the materials to be used for lower maintenance cost. Some materials are costly compared to others but they are more durable hence reducing the overall lifecycle costs.