Rakesh Patel: July 2020

Monday, July 27, 2020

Create QR-Code with VBA

Function to add QR-Code as image in your file.

Function Insert_QR(codetext As String)
	Dim URL As String, MyCell As Range

	Set MyCell = Application.Caller
	URL = "https://chart.googleapis.com/chart?chs=125x125&cht=qr&chl=" & codetext
	On Error Resume Next
	  ActiveSheet.Pictures("My_QR_" & MyCell.Address(False, False)).Delete 'delete if there is prevoius one
	On Error GoTo 0
	ActiveSheet.Pictures.Insert(URL).Select
	With Selection.ShapeRange(1)
	 .PictureFormat.CropLeft = 10
	 .PictureFormat.CropRight = 10
	 .PictureFormat.CropTop = 10
	 .PictureFormat.CropBottom = 10
	 .Name = "My_QR_" & MyCell.Address(False, False)
	 .Left = MyCell.Left + 25
	 .Top = MyCell.Top + 5
	End With
	Insert_QR = "" ' or some text to be displayed behind code
End Function

Excel To PDF with VBA

Sub ConvertExcelToPDF()

'Export Single Sheets

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="TrainingDeo", OpenAfterPublish:=True

'Export Multiple Sheets

Sheets(Array("RemovingDuplicates", "HideUnhide", "ExportToPDF")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="TrainingDemo2", OpenAfterPublish:=True

Sheets("ExportToPDF").Select

Range("A1").Select

End Sub

Reference Code at YouTube

Friday, July 24, 2020

SQL Query to find all tables in a Database containing specified column name

Following query will help to find field name available in any of the table in a database.

SELECT * from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%ColumnName%'
order by TABLE_NAME

SQL Server:

SELECT Table_Name, Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YOUR_DATABASE'
AND COLUMN_NAME LIKE '%YOUR_COLUMN%'

Oracle:

SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE column_name LIKE '%YOUR_COLUMN_NAME%'

AND OWNER IN ('YOUR_SCHEMA_NAME');