Excel VBA Example – DDL Generator

DownloadDDLGenerator

(Rename ddlgenerator.xls to ddlgenerator.xlsm)

please rename to file from ddlgenerator.xlsx to ddlgenerator.xlsm

 

Function: It would generate Oracle SQL files for specified table manipulation.

 

Image 011

Image 002

Four files would be generated

Image 001Image 014

Image 003

In the working path of the excel, you could find two sql file. One is the add column ddl file while another is drop column ddl file. The file name is DDL_AddColumn_<table name>.sql,  DDL_DropColumn_<table name>.sql, DDL_CreateTable_<table name>.sql and DDL_DropTable_<table name>.sql

Source Code

 

Sub genDDL()

 For i = 1 To Worksheets.Count
 If Worksheets.Item(i).Name = "TableAddColumn" Then
 Dim currentWorksheet As Worksheet
 Set currentWorksheet = Worksheets.Item(i)
 
 ' Table Name
 tableName = currentWorksheet.Cells(1, 2)
 
 ' #1 as Add Column DDL file
 addColumnDDLFile = ActiveWorkbook.Path + "\DDL_AddColumn_" + tableName + ".sql"
 Open addColumnDDLFile For Output As #1
 
 ' #2 as Drop Column DDL file
 dropColumnDDLFile = ActiveWorkbook.Path + "\DDL_DropColumn_" + tableName + ".sql"
 Open dropColumnDDLFile For Output As #2
 
 ' #3 as Create Table DDL file
 createTableDDLFile = ActiveWorkbook.Path + "\DDL_CreateTable_" + tableName + ".sql"
 Open createTableDDLFile For Output As #3
 
 ' #4 as drop table DDL file
 dropTableDDLfile = ActiveWorkbook.Path + "\DDL_DropTable_" + tableName + ".sql"
 Open dropTableDDLfile For Output As #4
 
 Print #1, "ALTER TABLE " + tableName + " ADD ("
 Print #2, "ALTER TABLE " + tableName + " DROP ("
 Print #3, "Create Table " + tableName + " ("
 Print #4, "Drop Table " + tableName; ";"
 
 ' Reading columnValue until not empty
 i = 3 ' row number
 Do While currentWorksheet.Cells(i, 1) <> ""
 
 ' For add column statement
 preparedPartialAddColumnSQL = currentWorksheet.Cells(i, 1) + " " _
 + currentWorksheet.Cells(i, 2) + "(" _
 + currentWorksheet.Cells(i, 3).Text
 
 ' If it specify the unit, add unit other do not add it
 If (currentWorksheet.Cells(i, 4) <> "") Then
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + " " + currentWorksheet.Cells(i, 4)
 End If
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + ")"
 
 ' If it marked "Y" for not null then add not null to the prepared partial sql statement
 If (currentWorksheet.Cells(i, 5) = "Y") Then
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + " NOT NULL"
 End If
 
 ' If text line have text, add "," at the end of line
 If currentWorksheet.Cells(i + 1, 1) <> "" Then
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + ","
 End If
 
 ' For drop column statement
 preparedPartialDropColumnSQL = currentWorksheet.Cells(i, 1)
 If currentWorksheet.Cells(i + 1, 1) <> "" Then
 preparedPartialDropColumnSQL = preparedPartialDropColumnSQL + ","
 End If
 
 Print #1, " " + preparedPartialAddColumnSQL
 Print #2, " " + preparedPartialDropColumnSQL
 Print #3, " " + preparedPartialAddColumnSQL
 i = i + 1
 columnValue = currentWorksheet.Cells(3, 1)
 Loop
 
 Print #1, ");"
 Print #2, ");"
 Print #3, ");"
 
 ' Close output file
 Close #1
 Close #2
 Close #3
 Close #4
 
 End If
 Next i

 
End Sub


Feasible Aspects

  • Index
  • Tablespace
  • DB Connection
  • Constraints
  • Primary Key
  • Foreign Key

 

Reference: Create Table SQL Statement Information

Image 001
Proposed New Excel Template (For provide more function)

 

Excel VBA

Overview

Excel VBA is programming language for excel.

Modules

It is the place where VBA code is stored and typed in the VBA Editor.

Basic Excel Object Model

Excel Object Model

Important Excel Object:

  • Microsoft.Office.Interop.Excel.Application
  • Microsoft.Office.Interop.Excel.Workbook
  • Microsoft.Office.Interop.Excel.Worksheet
  • Microsoft.Office.Interop.Excel.Range

 

Reference

 

Example of Excel VBA

It create a file which contain the tab name and first row of each tab.

Sub generated()

' Filename for store out
outputfile = ActiveWorkbook.Path + '\output.txt'
Open outputfile For Output As #1

' No. of Worksheets
WS_Count = ActiveWorkbook.Worksheets.Count

'Loop the Worksheet(Tab)
For i = 1 To WS_Count

ActiveWorkbook.Worksheets(i).Select

J = 1
Do Until Trim(Cells(1, J).Value) = ''

' declare variable table_name
table_name = ''
If Right(ActiveWorkbook.Worksheets(i).Name, 6) = '-NODEL' Then
table_name = Left(ActiveWorkbook.Worksheets(i).Name, Len(ActiveWorkbook.Worksheets(i).Name) - 6)
Else
table_name = ActiveWorkbook.Worksheets(i).Name
End If

Print #1, table_name + ' ' + Cells(1, J).Value
J = J + 1
Loop

Next

' Pop up when finished
MsgBox 'Finished'

' Close and Flush the output
Close #1

End Sub

The below is a function, f. It create a file, output.txt and write “Testing to it". Then Close the file.

sub f()
outputfile = ActiveWorkbook.Path + "\output.txt"
Open outputfile For Output As #1
print "Testing"
close #1
end sub

 

wscript and cscript

Overview of WSH

Windows Script Host (WSH) is a Windows administration tool. it makes objects and services available for the script and provides a set of guidelines within which the script is executed.

wscript

Provides access to root object for the Windows Script Host object model.

cscript

A command-line version of the Windows Script Host.

 

WSH Object Model 

The Windows Script Host Object Model Hierarchy

From: https://msdn.microsoft.com/en-us/library/a74hyyw0(v=vs.84).aspx

 

Difference between wscript and cscript

WSCRIPT.EXE and CSCRIPT.EXE are almost exactly identical, except that one is flagged as a windows application and the other is flagged as a console application.

This also affects some behaviors, such as the WScript.Echo command. In a CSCRIPT.EXE this writes a line to the console window. In WSCRIPT.EXE it shows a messagebox.

From: http://stackoverflow.com/questions/8678441/difference-between-wscript-and-cscript

 

Path of Window 7 :

C:\Windows\System32\cscript.exe

C:\Windows\System32\wscript.exe

 

Example of vbs runnable in wscript/cscript 

<code>

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("<Excel File Path>")

set ws1 = objWorkbook.worksheets
for i=1 to ws1.count
Wscript.echo ws1(i).name
next

</code>