Thursday, October 31, 2019

How to access environment variables in react app on azure devops.



When you don’t want to access values through .env files, then follow the below procedure to access it from azure devops.

First remove env files and set the variables in pipeline
You can set a variable for a build pipeline by following these steps:
·         Navigate to Pipelines page, select the appropriate pipeline, and then select Edit.
·         Locate the Variables for this pipeline.
·         Add or update the variable.


To use a variable group, open your pipeline, select the Variables tab, select Variable groups, and then choose Link variable group. In a build pipeline, you see a list of available groups


CREATE VARIABLE GROUP
Navigate to Library and select  "Variable group" which opens the below screen
Toggle the Azure Key vault radio button to link the key vault.


CREATE BUILD PIPELINE TO ACCESS THE ENVIRONMENT VARIABLES.
The variables created under pipeline variables will be exposed to env and can be accessed without any issues. Since we are using react app make sure to prefix the variable name with “REACT_APP_”
The variables under “variable groups” will not be exposed to env.

Build pipeline Tasks:

1.       Add the “Azure key vault” task and fill out the properties as per your need and select the required key vault

2.       PowerShell Script
This task is required as the variables under “variable groups” are not exposed to env.
Add the below script which will set the env variables.
echo '##vso[task.setvariable variable=REACT_APP_API_URL]$(REACT-APP-API-URL-TEST)'
echo '##vso[task.setvariable variable=REACT_APP_TEST_EXPORT]$(API-URL-TEST)'

3.       Npm install – to install the npm packages.
4.       Npm custom – run build
5.       Archive files
6.       Copy file
7.       Publish


CREATE KEY VAULT



Wednesday, November 23, 2011

open workbook

Workbooks.Open filename:="file.xls"
 Sheets(Worksheets.Count).Activate   ' selecting the last tab
'insert row after first line
  Rows("2").Select
 Selection.Insert Shift:=xlDown
ActiveSheet.Cells(2, 1).Value = "Sharieff"
ActiveSheet.Columns("A").AutoFit
  'If row 3 is blank then deleting it
        If LenB(ActiveSheet.Cells(3, 1)) = 0 Then
          ActiveSheet.Rows(3).EntireRow.Delete
          Rows("2").Select
        End If
  ActiveWorkbook.Close SaveChanges:=True
'Reading Textbox value
filename = Application.Worksheets("main").txtFile.Value

'Current Path
Path = Application.ThisWorkbook.Path

'Wild card
file = Path & "*juk*.xls"
file_return = Dir(file)
While file_return <> ""
        file_return = Dir()
wend

Thursday, November 10, 2011

Add New workbook

Dim new_xl As Object
Dim workbook_name As String
Set new_xl = Workbooks.Add
new_xl.Activate
workbook_name = ActiveWorkbook.Name
Sheets("sheet1").Select
Cells(1, 1) = "CURRENT STOCK REPORT OF ALL PRODUCTS"
‘copy data from xl file x.xls
For i = 1 To lastrow
  Cells(i + 2, 1) = Application.Workbooks("x.xls").Worksheets("Balanced").Cells(i, 1)
  Cells(i + 2, 2) = Application.Workbooks("x.xls").Worksheets("Balanced").Cells(i, 2)
  Cells(i + 2, 3) = Application.Workbooks("x.xls").Worksheets("Balanced").Cells(i, 3)
Next i
‘Formatting
Range("A1:F1").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    With Selection.Interior
        .ColorIndex = 10
        .Pattern = xlSolid
    End With
    Selection.Font.ColorIndex = 2
    Selection.Font.Bold = True
    Cells.Select
    Cells.EntireColumn.AutoFit
End Sub

Find First or Last Populated Row in a sheet

Option Explicit

Sub Test_xlFirstLastRows()
    
     '       Target Application:  MS Excel
     '       Demonstration:  display first and last non-blank rows in the active sheet
     '                       and one target sheet
    
    Dim SheetName As String
     '
     '           display sheet name and results from xlFirstRow and xlLastRow
     '           for the active sheet.  Since activesheet is assumed if procs are called
     '           without a passed arguement, use that method here
     '
    MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _
    "First non-blank row = " & xlFirstRow & vbCrLf & _
    "Last non-blank row = " & xlLastRow, vbInformation, _
    "Active Sheet Demonstration"
     '
     '           display sheet name and results from xlFirstRow and xlLastRow
     '           for "Sheet4".  Since this is not the active sheet, the sheet must
     '           be defined via the passed arguement.
     '
    SheetName = "Sheet4"
    MsgBox "Worksheet name = " & SheetName & vbCrLf & _
    "First non-blank row = " & xlFirstRow(SheetName) & vbCrLf & _
    "Last non-blank row = " & xlLastRow(SheetName), vbInformation, _
    "Passed Sheet Name Demonstration"
    
    
End Sub

Function xlFirstRow(Optional WorksheetName As String) As Long
    
     '    find the first populated row in a worksheet
    
    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
    With Worksheets(WorksheetName)
        On Error Resume Next
        xlFirstRow = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _
        xlWhole, xlByRows, xlNext).Row
        If Err <> 0 Then xlFirstRow = 0
    End With
    
End Function

Function xlLastRow(Optional WorksheetName As String) As Long
    
     '    find the last populated row in a worksheet
    
    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
    With Worksheets(WorksheetName)
        On Error Resume Next
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).Row
        If Err <> 0 Then xlLastRow = 0
    End With
    
End Function

Quickly Delete Blank Rows From A Long List

1.     First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .
2.     Now, select the cells in one column from the top of your list to the bottom.
3.     Make sure that all the blank cells in this selected range are the rows you want to delete.
4.     Press the F5 key on your keyboard (or select Edit, Goto).
5.     Click the Special button.
6.     Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
  1. Now choose Edit, Delete, select the Entire Row option and click OK.
VBA Code
Sub DeleteBlankRows2()
'Deletes the entire row within the selection if _
 some of the cells WITHIN THE SELECTION contain no data.
On Error Resume Next
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Sub DeleteBlankRows3()
'Deletes the entire row within the selection if _
the ENTIRE row contains no data.
Dim Rw As Range
If WorksheetFunction.CountA(Selection) = 0 Then
   MsgBox "No data found", vbOKOnly, "OzGrid.com"
   Exit Sub
End If
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    Selection.SpecialCells(xlCellTypeBlanks).Select
        For Each Rw In Selection.Rows
            If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
                Selection.EntireRow.Delete
            End If
        Next Rw
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub