Sunday, August 12, 2018

VBA Code to copy and paste the Content from excel to word

There are situations where you may have to copy and paste some content from excel to word. And save the newly created document into disk. The below VBA code or a macro will help you to do that. It is a very simple code, it is being executed from MS Excel. The MS Word object Library should included. This library has to functionality to interact with MS word object. 

To include MS word object library, go to Tools and then select References. Select the required object library as shown in the picture below.
 

To copy the VBA code follow the below steps

Open excel and make your table ready. This steps is as per your requirement. 

Press Ctrl + F11, this will open a Visual Basic editor. 

Click on insert and then module. 

Module1 will appear if do not have any  module. Paste the below code into this module 

Go back to excel and your macro will appear under macros list. Select and click on Run to execute the macro. 

Watch this video for more details explanation





Sub CreateWord()
    
    Dim w
    Dim d
    Dim filename As String
    'filename = "D:\\krishna\\youtube vidoes upload\\Marks Card " & Format(Date, "ddmmyy") & "_" & Second(Now())
    filename = "D:\\krishna\\youtube vidoes upload\\Marks Card " & Sheets("marks_sheet").Range("G7:G7") & "_" & Sheets("marks_sheet").Range("G8:G8")
    
    Sheets("marks_sheet").Range("a4:k25").Copy
    
    Set d = CreateObject("Word.Application")
    'If w Is Nothing Then
    Set w = d.Documents.Add
     
    d.Selection.Paste
    d.Visible = True
    'd.Selection.Paste ("new content")
    Application.CutCopyMode = False
    'ActiveDocument.Sections.Paste ("adsf")
    d.ActiveDocument.SaveAs filename
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Application.CutCopyMode = False
    Set w = Nothing
    Set d = Nothing

End Sub

Saturday, July 14, 2018

Cleaning up private docker registry

The solution to cleaning the unused docker registry layers is not straightforward, there is nothing readily available from docker. As more and more docker layers are pushed and tagged, it may be possible that there are image layers which may not be required. These unnecessary layers will consume a lot of storage space. This page describes the way to delete unused layers without disturbing the registry.
I am going to explain how to clean up unused layers from docker registry using HTTP API V2
Docker HTTP API V2
The current version of docker provides an option to interact with the images in the remote private registry using HTTP API version 2.
Few useful Digest APIs
List all the repositories available in the private registry
CATALOG
$ curl reg-server:5000/v2/_catalog

Output
------
{"repositories":["alpine","1_ubuntu_16.04","centos","centos6-build-test","centos6-build-qa","centos6-build-build","centos6-jenkins-agent","jenkins","squid-deb-proxy","ubuntu","ubuntu-build","ubuntu-build-agent","ubuntu_16.04"]}
Getting a list of layers used, and other metadata of a repository (image)
GET DETAILS
$ curl -k -v --silent -H "Accept: application/vnd.docker.distribution.manifest.v2+json" -X GET http://reg-server:5000/v2/ubuntu-jenkins-agent/manifests/latestrl reg-server:5000/v2/_catalog
OUTPUT
Output
------
* Trying 10.0.93.36...
* Connected to reg-server (12.0.33.1) port 5000 (#0)
> GET /v2/ubuntu-jenkins-agent/manifests/latest HTTP/1.1
> Host: reg-server:5000
> User-Agent: curl/7.47.0
> Accept: application/vnd.docker.distribution.manifest.v2+json
> 
< HTTP/1.1 200 OK
< Content-Length: 5540
< Content-Type: application/vnd.docker.distribution.manifest.v2+json
< Docker-Content-Digest: sha256:d00e05048b4ef3d7e175d233a306f64175f1c716c755224984099c7e8cf0948
< Docker-Distribution-Api-Version: registry/2.0
< Etag: "sha256:d00e05048b4ef3d7e175d233a306f64175f1c716c7552245e14099c7e8cf0948"
< X-Content-Type-Options: nosniff
< Date: Fri, 06 Apr 2018 08:39:42 GMT
< 
{
"schemaVersion": 2,
"mediaType": "application/vnd.docker.distribution.manifest.v2+json",
"config": {
"mediaType": "application/vnd.docker.container.image.v1+json",
"size": 17980,
"digest": "sha256:6c4e6280d347be4762dd77a20845ec69c4c1da3424195523765dfaeeecbffa22"
},
"layers": [
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 67103213,
"digest": "sha256:5d890c53be21ea2d7c417960dfdb8edf87f623bfd016751261fac26943a0b188"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 72628,
"digest": "sha256:f775b856e1997836995617cf691ea4ffb0b1ef967ac73db661666ba3a216d432"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 363,
"digest": "sha256:552c4f407d99f5ff4e96e79430bae55c4ff1154824dab3945ef4bb0482c826d5"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 676,
"digest": "sha256:fda304b96f8a99052eacb6ce515f26d8ff10fc78cbb6a9f09e996faaadabdaaa"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 162,
"digest": "sha256:2b033adb904af1b663e78cf33f513fc2f98730b9c0dcab4a3ad4cd85eb825880"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 69424905,
"digest": "sha256:3b93b65608a04bbd902b73d76e61f52a28b2a0b0faca95b8303dbe9a3397a688"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 76466711,
"digest": "sha256:8ccd40bb9dd1bb2dfbc16a0ab661b817ce9b6af0617ae679cfabaa80a74414c5"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 1066,
"digest": "sha256:0cf16c8ac4188b500ccbdf405c1288c298c4a50e40f0aafe485cd2d58f81ee8f"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 4606,
"digest": "sha256:5670062ffd23e300b2fa32af6d9763211372ae919801c1ee98d1827aec24b57c"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 1020,
"digest": "sha256:ce7a2ec46384b5e5cf94f6bdded1c8a46c1614b4e5216101e706a5a47dd1a8b5"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 38402,
"digest": "sha256:73dd7e155ea00edcb02adc94f271aa07924faac30b4413fba823cbb7be51541c"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 175,
"digest": "sha256:46094570601896ddccb7446c48fd64e7089fb3fb826b747e6db4c971da4f2d9a"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 28176,
"digest": "sha256:2a4b48722514ef189776665fa00e98b24296117db359b491c83d995c53d4a3c1"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 47021,
"digest": "sha256:55c8e005550e7be9ea7f990696b6b863a270f7a43a059b8e52af9e84a745403d"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 415368,
"digest": "sha256:9cbcf4bfa6017c078eb8e2f20da1593e91cb0b5d271538c480c2a847fd0453ea"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 98274,
"digest": "sha256:4e6638ed8398ca234d28dad09047ffba24fa96961497d8975ea20fe89738fe7e"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 1133119,
"digest": "sha256:cffca1cea7728d2f65b29956c5f4ca46e3130e7c5eca2a8e74ceee7760f75227"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 465660,
"digest": "sha256:586d6232925d2948926e1c0697657ddeb8daf63840f50d14e75a5d80a140f53f"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 114104,
"digest": "sha256:329692f820b7a8f115763b238025b0021eeebfa83f9ba5ba94d09e4d30ab0443"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 11356,
"digest": "sha256:8b948376919e9027aed5fb110f827e29b5d5b89faa249dd5e96d9f322f16671d"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 233,
"digest": "sha256:280ed15840b101f31d36195774dbc64a0756b10445ac86a7d56c9323b254d3ab"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 177,
"digest": "sha256:8b3430dea5aa62d32f1afa524062c8842b69f4e4c6c206c506096d61b86cf9aa"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 5397987,
"digest": "sha256:940f81402bbb4f7cd2fc2e27ad60243ee352594d2d347d4cc7d061543a645579"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 265,
"digest": "sha256:f22d1b75d0776f7b50c189b5f902411a16f1e558bb182eaa50d87970a783c3ed"
},
{
"mediaType": "application/vnd.docker.image.rootfs.diff.tar.gzip",
"size": 573,
"digest": "sha256:9d201349e9cab134e816a063bcbf18d1a23ff854a953492b9d9aa4165cf059ec"
}
]
* Connection #0 to host reg-server left intact
Deleting an image tag using the Docker-Content-Digest
Using this API, any given tag of an image can be deleted remotely. This will delete the layers associated with this tag. But one has to identify the tags that should be deleted. After deleting a tag remotely, one has to run the garbage-collector to take the depletion effect. Please note that the DELETE api requires a digest of an image tag. The digest can be obtained using a tag as bellow (for an image which is tagged).
The tag name in the below snippet is latest. It will work with any other named tag
DELETE
$ curl -k -v --silent -H "Accept: application/vnd.docker.distribution.manifest.v2+json" -X GET http://reg-server:5000/v2/ubuntu-jenkins-agent/manifests/latest 2>&1 | grep Docker-Content-Digest | awk '{print ($3)}'
To get the digest of an untagged image, go to the storage of the docker registry inside the registry container. If the registry container has configured with volume, a digest can be obtained from this location as well.
Suppose if 966fcf31b8a2 is a container id of a private registry and /var/lib/registry is the registry storage inside the container
DELETE
$ sudo docker exec -it 966fcf31b8a2 sh
$ cd /var/lib/registry/docker/registry/v2/repositories/alpine/_manifests/revisions/sha256

## below listed sha256 are the manifest whihc reference the image tag This is called a digest. These digests are used while deleting an image tag.
## there are 3 unamed tags here and the latest is always tagged as :latest
$ ls
xr-xr-x 2 root root 18 Apr 4 06:02 ef04ea6e2324b2e1f2b1a25a56defc92d24f6b364e14ddd081241426af82aa2d
xr-xr-x 2 root root 18 Apr 4 10:06 b978ab300d84b859181fbf8c579315e709d22bb47e25b448952e6dfdc79be1f5
xr-xr-x 2 root root 6 Apr 4 10:10 c62d369018e25c79c651cdebc10d380e585acbda33340476d9f85d34c4a37b0d

To delete a tag using a Digest (which you got in the previous step)
Note:
  1. stop the running container and start again with additional ENV variable -e REGISTRY_STORAGE_DELETE_ENABLED=true. The run command looks like
    docker run -d -p 5000:5000 -v /home/sanjeeva/registry_vol:/var/lib/registry -e REGISTRY_STORAGE_DELETE_ENABLED=true --name registry registry:2
  2. By editing the config file inside the registry container.
    vi /etc/docker/registry/config.yml
    Under 
    storage: add these 2 lines
    delete:
    enabled: true
    Run the below command
    $ sudo docker restart >
DELETE
$ curl -k -v --silent -X DELETE http://localhost:5000/v2/alpine/manifests/sha256:934c25b1f1c266e31ee3693890b08f67cf0b05c162561edc779150c0ece7d872
DELETE OUTPUT
* Trying 127.0.0.1...
* Connected to localhost (127.0.0.1) port 5000 (#0)
> DELETE /v2/alpine/manifests/sha256:934c25b1f1c266e31ee3693890b08f67cf0b05c162561edc779150c0ece7d872 HTTP/1.1
> Host: localhost:5000
> User-Agent: curl/7.47.0
> Accept: */*
> 
< HTTP/1.1 202 Accepted
< Docker-Distribution-Api-Version: registry/2.0
< X-Content-Type-Options: nosniff
< Date: Fri, 30 Mar 2018 10:58:18 GMT
< Content-Length: 0
< Content-Type: text/plain; charset=utf-8
< 
* Connection #0 to host localhost left intact
Garbage-collection
After deleting a tag of an image using API, run the garbage collection inside the container.
DELETE
## dry run with -d
$ /bin/registry garbage-collect -d /etc/docker/registry/config.yml
## actual run without -d
$ /bin/registry garbage-collect /etc/docker/registry/config.yml
Summary of steps to clean up a registry
  1. Enabling a layer deletion in docker registry (there are 2 ways to do this 1.b is the preferred method)
    1. by stopping and deleting the registry container
      1. Stop and delete the registry container
    2.  
      1. This may delete all the images in the registry unless it was started with a volume with -v option
      2. Start the registry container with
      3. REGISTRY_STORAGE_DELETE_ENABLED=true as
      4. docker run -d -p 5000:5000 -v /home/sanjeeva/registry_vol:/var/lib/registry -e REGISTRY_STORAGE_DELETE_ENABLED=true --name registry registry:2
    3. by restarting the container after editing the config file
      1. $ vi /etc/docker/registry/config.yml
      2. Under storage: add these 2 lines
        delete:
        enabled: true
      3. Run the below command
      4. $ sudo docker restart
  2. inside the registry container, for each repository
    1. List all the manifests except the latest tag.
  3. Run the API V2 command to delete the non-latest manifests
    1. $ curl -k -v --silent -X DELETE http://reg-server:5000/v2/alpine/manifests/sha256:249c714c688541c83ca2c9b2a8c30dd77b5c45c836e6c69632815ee3614ccbd2
  4. Run the garbage-collector inside the registry container
    1. It will delete the image layers which are no more referenced by any tag(manifest) except the latest tag
    2. We may see a few folders left undeleted.


VBA Code to copy and paste the Content from excel to word

There are situations where you may have to copy and paste some content from excel to word. And save the newly created document into disk. Th...